Prisma with pgBouncer benchmark
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!
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
namedoes not have an index this should be a good way to make the query a bit heavy.
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
node-postgresinstead of Prisma
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)
prepared statement \"s135\" does not existerrors
- 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
Check out the repo here: https://github.com/larskarbo/prisma-pool-test