PgBouncer and Prisma
hi! if my db has a 500 connection limit and i'm using pgbouncer in between, with prisma, what should the connection_limit be set to in my db string? i see these strange spikes where queries suddenly start taking much, much, longer and the database is not struggling nor is the application code.
my hunch is that prisma connections are somehow backing up / not being freed up correctly and it's leading to a backed up queue.
any guidance here would be greatly appreciated.
p.s. this has been an issue for us for a long, long, long time and i just added pgbouncer to the mix which seems to help a bit, but still happens about once an hour.
12 Replies
You've gone full bot mode! Let me fetch that AI answer for you ASAP.
would love some help here 🙏
the docs are limited
does pgbouncer=true handle this somehow? im on pgbouncer 1.22 so it doesn't seem needed
Hey 👋
Can you run this SQL query and see which applications are occupying connections?
Do you see any idle connections in output?
yes - i generally have 40 - 80 here (just cleared idle connections before i saw this message)
@Nurul (Prisma) the issue is that the prisma seems to be letting idle connections stack up in the queue, I have to manually restart pgbouncer and clear idle connections in postgres to fix it. this happens every few hours
here are my pgbouncer settings
and with prisma i have
?connection_limit=200&pool_timeout=5
we're strongly considering a move off of Prisma at this stage (100k users, social site) because seemingly at random db requests stack up in the queue and take a long time to resolve. it does not correspond to any memory / CPU spike on postgres or application code, at this stage i'm quite confident it's something funky with how prisma handles poolingBased on the knowledge sources provided, I can offer some insights and guidance regarding your question about PgBouncer, Prisma, and connection limits.
1. Connection Limit Setting:
When using PgBouncer with Prisma, you should set the
connection_limit in your database connection string to a value that is appropriate for your application's needs, but significantly lower than the database's 500 connection limit. The exact number depends on your specific use case, but a common practice is to set it to a fraction of the database's limit.
2. PgBouncer Configuration:
Make sure PgBouncer is configured correctly. As mentioned in this GitHub issue comment, you should set PgBouncer to transaction mode:
This is recommended in the Prisma documentation for configuring PgBouncer.
3. Prisma and PgBouncer Compatibility:
Ensure you're using the pgbouncer=true parameter in your connection string when using PgBouncer. This is crucial for Prisma to work correctly with PgBouncer as discussed here.
4. Connection Pooling Behavior:
The strange spikes you're experiencing where queries suddenly take much longer could be related to how Prisma manages connections. As noted in this GitHub issue, some users have experienced similar issues with connections not being freed up correctly, leading to a backed-up queue.
5. Monitoring and Diagnostics:
To better understand what's happening, you might want to enable Prisma Metrics. This can give you insights into connection usage and help identify if connections are indeed not being released properly as mentioned here.
6. Potential Workarounds:
If the issue persists, you might consider:
- Increasing the pool timeout value beyond 10 seconds
- Increasing the number of connections you give your applicat
ions
- Increasing the pool size by increasing the connection_limit value
These suggestions were provided in this GitHub issue comment.
It's worth noting that the combination of Prisma, PgBouncer, and specific deployment environments (like serverless) can sometimes lead to complex connection management scenarios. If the problem persists after trying these suggestions, you might need to reach out to Prisma support or consider opening a new issue on their GitHub repository with detailed reproduction steps and your specific configuration.Thank you for providing additional context.
Do you have multiple instances of application server running?
Did you try expliciltly disconnecting connections via $disconnect?
I would recommend you to enable metrics to get a better idea on how many open connections are occupied by Prisma.
Also, can you confirm that you have a single instance of
PrismaClient in your codebase?Metrics (Preview) | Prisma Documentation
Diagnose application performance with insights into Prisma Client database activity.
Connection management | Prisma Documentation
This page explains how database connections are handled with Prisma Client and how to manually connect and disconnect your database.
can you advise on the connection_limit when connecting to pgbouncer?
setting up metrics now, quick question, if we run Prisma on 3-5 different servers will this return metrics across all or just the application it's being called on
and yes we use a prisma singleton
we get ~1,000 requests per minute, i find it strange that despite this volume prisma is only opening 4 connections (connection_limit=200)
If you are running your app on 3-5 different servers, I assume you each app instance would have its own PrismaClient instance. Each PrismaClient instance manages it's own pool. So, the connections would be split across each PrismaClient instance.