pgbouncer=true causes 3-4x slowdown in response

[elilabes]

I am looking to migrate an existing application away from Firebase to Digital Ocean, starting with the database. As part of this I have been creating a POC with Prisma to ensure that things work properly before making the change.

I have created a Postgres database using the Basic cluster configuration (Basic / 1 vCPU / 1 GB RAM / 10 GB SSD / Connection limit: 22).

I have a very basic test schema

generator client {
  provider = "prisma-client-js"
  binaryTargets = ["native", "debian-openssl-1.1.x"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Blogs {
  id   String @id @default(uuid())
  name String @db.VarChar(255)

  @@map("blogs")
}

and the database has 4 test rows in it.

I have a simple QGL resolver using type-graphql

@Query(() => Boolean)
  public async speedTest(): Promise<boolean> {
    const data = await prisma.blogs.findMany();
    console.log(data);
    return true;
  }

This all worked as expected and when I run the server locally and execute my query the 4 results are logged back to me in between 150-200ms, which is expected for how far away I am from Singapore (the region I selected).

As the server will be run inside of Firebase Cloud Functions for now, I needed to take into consideration the concerns of connections when function instances scale. Digital Ocean offers adding a PG Bouncer which I configured with:

• Pool mode: transaction
• Size: 8 (server can handle up to 22 connections)

I then updated the DATABASE_URL environment variable to connect to the connection pool and added ?pgbouncer=true to the connection string.

The problem I have ran into is that adding pgbouncer=true causes the response time to go from 150-200ms to 750-1000ms. If I remove the pgbouncer query param (but keep the connection string pointing at the connection pool) then the response time goes back down to 150-200ms.

Is this type of increase in response time expected when adding in external connection pooling? The weird part is that excluding pgbouncer=true gives the same response time.

Any help or answers appreciated 😄 🙌

Update:

I wanted to rule out a misconfiguration of Digital Ocean, so I setup a quick database using Supabase (free plan, same table structure and test data) with all their default configuration (including pooling). I got the same results as Digital Ocean where adding pgbouncer=true caused a 3-4x slowdown.

Update 2:

To ensure that it wasn't Firebases' local server that was being weird, I setup an Apollo Server node express app (so not serverless) and the same issue occurred.

[elilabes]

Okay - after enabling more logging and reading the pgbouncer notion doc, it looks like this slowdown is expected.

Even with a simple read query, it adds three extra queries to ensure pgbouncer compatibility. Each query is around 150-200ms, hence the 3-4x slow down.

Looks like the solution will be having to move away from Cloud Functions before migrating away from Firestore, as 750-1000ms per query is to slow.

[ludralph]

Hi there,

To keep our discussions organized and focused on the most relevant topics, we’re reviewing and tidying up our backlog. As part of this process, we’re closing discussions that have already been marked as answered but remain open.

If this discussion still requires further input or clarification, feel free to reopen it or start a new one with updated details. Your contributions are invaluable to the community, and we’re here to help!

For more details about our priorities and vision for the future of Prisma ORM, check out our latest blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and ongoing support of the Prisma community!