prisma: Connection to Postgres database with pgbouncer is significant slower on Digital Ocean

We expect the problem to go away when support for prepared statements in PgBouncer is actually released and rolled out. You will probably just be able to stop using pgbouncer=true (which we will update via our docs then).

Until then, we are doing what we can - release 5.4.0 for example removed certain queries triggered by enums that were contributing to this. They are not done anymore, which - if you are using many enums - might lead to clear improvements here. The fundamental problem still exists though unfortunately.

We too are experiencing massive overhead when using Prisma in PGBouncer mode. In my scenario we have our node application running in GKE, the path our application takes to communicate with its database is the following: Application -> PG Bouncer -> Cloud SQL Proxy -> Managed Postgres Instance.

I have ran different testing scenarios to rule the different moving parts out that might be the cause and will try to explain them. Hopefully this will give enough information to solve the slowness.

Versions of the moving pieces:

prisma                  : 4.12.0
@prisma/client          : 4.12.0
Current platform        : darwin
Query Engine (Node-API) : libquery-engine 659ef412370fa3b41cd7bf6e94587c1dfb7f67e7 (at node_modules/prisma/node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Migration Engine        : migration-engine-cli 659ef412370fa3b41cd7bf6e94587c1dfb7f67e7 (at node_modules/prisma/node_modules/@prisma/engines/migration-engine-darwin)
Format Wasm             : @prisma/prisma-fmt-wasm 4.12.0-67.659ef412370fa3b41cd7bf6e94587c1dfb7f67e7
Default Engines Hash    : 659ef412370fa3b41cd7bf6e94587c1dfb7f67e7
Studio                  : 0.483.0
Preview Features        : tracing

Postges: 14.5 PG Bouncer: 1.18.0

Prisma Schema

generator client {
  provider        = "prisma-client-js"
  output          = "../src/persistence/generated/client"
  binaryTargets   = ["native", "debian-openssl-1.1.x"]
  previewFeatures = ["tracing"]
}

generator dbml {
  provider = "prisma-dbml-generator"
}

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

model Inventory {
  id           String           @id
  slug         String
  gtin         String
  location     String
  qoh          Int
  type         InventoryType
  createdDate  DateTime      @default(now()) @map("created_date")
  modifiedDate DateTime      @updatedAt @map("modified_date")

  @@unique([slug, gtin, location, type])
  @@index([slug])
  @@index([gtin])
  @@index([type])
  @@index([location])
  @@map("inventory")
}

enum InventoryType {
  brand
  retailer
}

DAO

Below is the bit of code that handles the saving of data to Postgres

    async saveNewInventory(newInventory: Prisma.InventoryCreateManyInput[], jobId?: string) {
        try {
            await prisma.$transaction(
                async (prisma) => {
                    logger.info({ jobId }, 'about to delete inventory ...');
                    await prisma.inventory.deleteMany({ where: { slug: newInventory[0].slug } });

                    logger.info({ jobId }, 'recreating it ...');
                    await prisma.inventory.createMany({ data: newInventory });
                },
                {
                    maxWait: 120000,
                    timeout: 120000,
                },
            );
        } catch (error) {
            logger.error({ jobId, error }, 'Error: inventory rolled back');
            throw new InternalServerError('Error saving new inventory');
        }
    }

Inserting 400K items with pgbouncer=true in the connection string and talking directly to postgres takes 58 minutes. Inserting 400K items with pgbouncer=true going through pgbouncer to write to postgres takes 54 minutes. Inserting 400K items with pgbouncer=true removed and talking directly to postgres takes 90 seconds.

From my understanding enabling pgbouncer=true wraps all calls into a transaction along with issuing DEALLOCATE ALL, but it comes with severe overhead issues.

Our infrastructure has OpenTelemetry enabled, looking at the first test above the slowness seems to be in the insert statement.

As you can see most of the time is spent in prisma:engine:itx_query_builder, by turning off pgbouncer in the connection string the same 400K items take less than 120 seconds. I would love to get some insights into what might be causing this, I don’t know if by turning on pgbouncer and starting prisma.$transaction, everything is being double wrapped in a transaction call, or if that is even possible in postgres.

Same issue here, but it becomes much more evident when using createMany.

For the given (simplified) schema, it takes about 17 minutes (!) to save 8000 records, whereas it takes 2 seconds without using pgBouncer.

In both cases, I’m running this locally and connecting to a Postgres database in DigitalOcean.

Any updates on this?

Simplified schema

model BulkEmailMessage {
  id                      String      @db.Uuid @id
  to                      Subscriber  @relation(fields: [toId], references: [id])
  toId                    String      @db.VarChar(24)

  // One-to-many 
  bulkEmail              BulkEmail   @relation(fields: [bulkEmailId], references: [id])
  bulkEmailId            String      @db.VarChar(24) 
  events                  Json        @db.JsonB

  created                 DateTime    @default(now())
  updated                 DateTime    @updatedAt 
}


model BulkEmail {
  id                      String      @db.VarChar(24) @id
  name                    String      @unique

  emails                  BulkEmailMessage[]
  subject                 String      
  from                    String      
  html                    String      

  // One-to-many 
  emailList               EmailList   @relation(fields: [emailListId], references: [id])
  emailListId             String      @db.VarChar(24)

  created                 DateTime    @default(now())
  updated                 DateTime    @updatedAt 

  @@unique([id, name])
}

Write 8000 records

const bulkEmailMessageData = Array(8000)
  .fill(0)
  .map(() => ({
    id: randomUUID(),
    toId: 'c31prgr345tsq7po126z7rgx',
    bulkEmailId: 'uzg1rc7itt91yv7gw5saahx6',
    events: []
}))


await prisma.bulkEmailMessage.createMany({
  data: bulkEmailMessageData
})

@amit1911 once pgbouncer makes a release with support for prepared statements (hopefully soon) prisma should work perfectly fine with pgbouncer

We don’t know yet. Someone should open an issue for figuring that out and share information if they tried it out.

Update: Exists at https://github.com/prisma/prisma/issues/21531

@amit1911 once pgbouncer makes a release with support for prepared statements (hopefully soon) prisma should work perfectly fine with pgbouncer

It was released yesterday I assume.

@dkamenov-fitted is your pgbouncer configured in transaction mode?

if I’m not mistaken when this lands pgbouncer/pgbouncer#845 we might be able to drop the pgbouncer=true and work normally

Yes, pgbouncer is configured to be in transaction mode as per this link

However I don’t think this is a PGBouncer issue, one of the above tests was enabling pgbouncer in the connection string and making a request directly to the database without going through pg bouncer, this saw significant increase in how long the underlying query with 400K records took.

@dkamenov-fitted is your pgbouncer configured in transaction mode?

if I’m not mistaken when this lands https://github.com/pgbouncer/pgbouncer/pull/845 we might be able to drop the pgbouncer=true and work normally

Has there been any development on this? I am running into the same issue with pgbouncer and supabase. When using the Data Proxy service offered by Prisma the query speed is comparable to a direct connection.