My dev blog where I dive deep into TypeScript, Postgres, Data science, Infrastructure, Ethereum, and more...

Prisma with pgBouncer benchmark

3rd Sep 2022

Everyone loves Prisma. It is a library that makes developer's lives easier. An ORM you actually want to use.

Postgres connection pooling can be tricky. Especially in serverless environments.

It’s easy to start blaming different parts of the stack without really understanding the issues.

I decided to do some benchmarks to understand more about prisma, postgres, pgBouncer and how different configurations work together.

 

Is the Prisma team deliberately making it hard to work with pgBouncer and AWS RDS just to push people over to their Prisma Data Proxy?

Only one way to find out, let’s test it!

Creating a benchmark repo

I cloned the official Prisma Nextjs starter and stripped it down to make it super simple.

It basically consists of

  • A Prisma schema with a User object.
  • A seed data script that generates 100 000 users
  • An API route that fetches the 95000 user. Since name does not have an index this should be a good way to make the query a bit heavy.

Benchmarking with k6

K6 is a load-testing tool that lets you hammer an API endpoint with thousands of users.

In our case we want to make as many requests to /api/fetchRowPrisma as possible.

The variables we can tweak and test are:

  • Prisma connection pool size
  • pgBouncer vs no pgBouncer
    • pgBouncer pool size
    • pgBouncer transaction vs session mode.
    • prisma &pgbouncer=true option
  • node-postgres instead of Prisma

Running the tests

 

Connection limit in parenthesis

  • Prisma (1)
    • Successful requests: 1281
    • Avg duration: 5220ms
  • Prisma (20).
    • Successful requests: 9072
    • Avg duration: 592ms
  • Node-postgres (20)
    • Successful requests: 9234
    • Avg duration: 582ms
  • Prisma (200)
    • Failed with error Error querying the database: db error: FATAL: sorry, too many clients already
    • Successful requests: 452
  • Prisma (200) + pgBouncer (20) (transaction)
    • gave prepared statement \"s135\" does not exist errors
  • Prisma (200) &pgbouncer=true + pgBouncer (20) (transaction)
    • Successful requests: 5844
    • Avg duration: 929ms
  • Prisma (200) + pgBouncer (20) (session)
    • Failed a lot
  • Node-postgres (200) + pgBouncer (20) (transaction)
    • Successful requests: 8602
    • Avg duration: 625ms
 

Repo

Check out the repo here: https://github.com/larskarbo/prisma-pool-test


Tools