Prisma•4w ago

Latency to/from US-east <-> US-east and Denmark <-> Paris

Really struggling with long latency for my queries - are these numbers what I can expect from your hosted Postgres solution ? (Thx. for the ORM btw - really awesome). I am currently assessing your hosted solution and on paper it looks great, but I am really struggling with large latencies. I am situated in Denmark and am doing tests against a database in Paris, but experiencing same long latency when using a database in US east with a Vercel hosted solution in US east as well. The average ping round-trip time from here to France is around 30ms (screenshot attached). The query I test with is a simple select from a table with 1 row. Using @prisma/ppg_tunnel and pgAdmin4 with EXPLAIN ANALYZE, I can see that the query takes sub-milliseconds to execute (which is expected), but the query takes around 200ms for repeated queries. There must be some overhead in the pgg_tunnel also, because I see a bit lower numbers when using the ORM: I have captured timing from "Optimize" using the Prisma ORM (screenshot attached). I run the query 10 times. Hosting is again local, connecting to the db in Paris. Average query is 122ms. for this simple query. Using "Accelerate" speeds things up a bit, but I have many unique queries that will not be boosted by that. As I wrote, I see the same numbers when measuring via "Optimize", running on Vercel where both hosting and db is US East. Are these the kind of numbers I should expect ? Less than 10 simple queries pr. sec. is not performant. Hoping for a suggestion, otherwise I need to go with a different solution. Best, Carsten

7 Replies

Prisma AI Help•4w ago

You're in no rush, so we'll let a dev step in. Enjoy your coffee, or drop into #ask-ai if you get antsy for a second opinion!

Nurul•4w ago

Hi @chessydk 👋 Thank you for sharing detailed scenario on what you are observing. I would like to get a few additional details and validate my understanding before I share it with our team internally. 1. Can you share with me your email id or GitHub handle with which you signed up, so that we can take a look at the metrics of your project to ensure there's no issues which might be causing this additional latency. 2. *"but the query takes around 200ms for repeated queries." * How much latency do you see if you do not use the ppg_tunnel and instead use the Prisma Postgres connection string of Paris region. ppg_tunnel indeed has an additional overhead and is in Early Access at the moment. Am I correct in my understanding that if you use Prisma Postgres connection string of Paris region, you observe 122ms average duration? 3. Are you using an Early Access version of database or a Production ready GA version? Can you try testing with a new database? (the newly provisioned databases would be of GA version) 4. *"I see the same numbers when measuring via "Optimize", running on Vercel where both hosting and db is US East." * If I understand correctly, when you are testing with a Prisma Postgres database in US East region and when you test it with Prisma Postgres database in Paris region, you observe the same numbers?

chessydkOP•4w ago

Hi Nurul! 1) chessydk / [email protected] 2) Hmm - how do I optain the connection string of Paris ? I have only seen the database_url (which I then use with pgg_tunnel) 3) No, the databases are freshly created after I got the news around GA versions. 4) What I mean is that I get similar latency numbers when running from Vercel (US / Washington D.C) against a Prisma db in US-east as when I host it locally in Denmark vs. Paris. Actually I think I was trying to "cross" run also with similar numbers also. The pure network latency (ping) does not seem to be that different doing so.

Nurul•4w ago

Thank you for providing the details. 1. I see you have two projects, "Trading journal next" and "Trade Journal Next local". Both of them has received some Prisma Postgres traffic. I did not find any obvious errors or issues in either of projects. 2. I meant using the DATABASE_URL directly instead of using a local connection string (running via ppg_tunnel). I did an experiment similar to what you tried. I selected the EU region to get a Prisma Postgres connection string and did the same findUnique query. I tested this from India. I saw similar results as you, where most of the time the query was between 100-150ms. I tested this with ppg_tunnel running.

Nurul•4w ago

Can you check if you try running the same query with DATABASE_URL, do you still see an average duration of 122ms? If it helps this was my script to test the duration with Prisma Postgres DATABASE_URL

import { PrismaClient } from "@prisma/client";
import { withAccelerate } from "@prisma/extension-accelerate";
import { withOptimize } from "@prisma/extension-optimize";
import dotenv from "dotenv";

dotenv.config();
const apiKey: string = process.env.OPTIMIZE_API_KEY ?? "";

const prisma = new PrismaClient()
  .$extends(withOptimize({ apiKey: apiKey }))
  .$extends(withAccelerate());

// A `main` function so that we can use async/await
async function main() {
  for (let i = 0; i < 100; i++) {
    const fetchUser = await prisma.user.findUnique({
      where: {
        id: 1,
      },
    });

    console.log(`Fetched user: ${JSON.stringify(fetchUser)}`);
  }
}

main()
  .then(async () => {
    await prisma.$disconnect();
  })
  .catch(async (e) => {
    console.error(e);
    await prisma.$disconnect();
    process.exit(1);
  });

import { PrismaClient } from "@prisma/client";
import { withAccelerate } from "@prisma/extension-accelerate";
import { withOptimize } from "@prisma/extension-optimize";
import dotenv from "dotenv";

dotenv.config();
const apiKey: string = process.env.OPTIMIZE_API_KEY ?? "";

const prisma = new PrismaClient()
  .$extends(withOptimize({ apiKey: apiKey }))
  .$extends(withAccelerate());

// A `main` function so that we can use async/await
async function main() {
  for (let i = 0; i < 100; i++) {
    const fetchUser = await prisma.user.findUnique({
      where: {
        id: 1,
      },
    });

    console.log(`Fetched user: ${JSON.stringify(fetchUser)}`);
  }
}

main()
  .then(async () => {
    await prisma.$disconnect();
  })
  .catch(async (e) => {
    console.error(e);
    await prisma.$disconnect();
    process.exit(1);
  });

DATABASE_URL was this:

DATABASE_URL="prisma+postgres://accelerate.prisma-data.net/?api_key=api_key"

DATABASE_URL="prisma+postgres://accelerate.prisma-data.net/?api_key=api_key"

chessydkOP•4w ago

Hi - and thx. for your effort. Actually the screenshot from Prisma "Optimize" was from running it kind of like what you did. I have pushed my test-code to a public Vercel site now, so you can actually try it. Its here: https://www.trading-journal-next.com/dbtest It makes 10 calls without accelerate and 10 with (and remember, I don't expect I can make a lot of use of accelerate as I will have lots of unique queries). The Vercel function is in US east (Washington D.C.) and the db is also in US east, so they are as close as they almost can be. It is faster than the previous measurements, but still pretty high to me (avg. 84ms) I have attaced a prisma "Optimize" screenshot of it and around 50% of the 10 queries are flagged "yellow" (latency between 50-500ms), which to me also signals that the prisma Optimze team deems that as "slow". I can't see there is anything to do about it, but to me it

Trading Journal Next

Created by Carsten Hess (@chessydk)

chessydkOP•4w ago

seems like high latency. But I don't know what to expect. Thx. for your help. Best, Carsten

Gaming

Programming

Latency to/from US-east <-> US-east and Denmark <-> Paris

Did you find this page helpful?