Django 5.1 now supports connection pooling that's built in to psycopg. Okay, cool. How does this compare with using pgbouncer? Is it better, worse, are they complementary? Is it better than just setting CONN_MAX_AGE? I'm very confused.
#pgbouncer vs built in connection pooling
spice herald
prime thorn
I am also confused about this and I use none of those options because I run everything on the same host so I thought I don't need to. Am I doing it wrong?
spice herald
it depends how it's set up I think. I can give some of my knowledge:
CONN_MAX_AGE keeps a connection alive that can be re-used. Often it seems to break, especially if you have multiple web workers and other conenctions to the database and the db will eventually say you have too many connections. Without CONN_MAX_AGE (or rather, set to the default of 0), Django opens a new connection for every request, and closes it at the end, which is very inefficient. This could also be inefficient even on the same host, but probably a lot less so.
Using a connection pooler such as pgbouncer allows you to maintain many connections to the pooler, then the pooler will manage a (usually smaller) number of connections to the server to make sure you don't run out of connections, but use all of those connections as efficiently as possible, and basically manages all this stuff for you by having something in the middle.
What I'm confused about really, is this:
What is the difference between the built in pooling in Django 5.1 / psycopg and how is it different from using pgbouncer? One difference I see is that you can't use CONN_MAX_AGE with the builtin pooler, I assume because it's managing the connections nicely for you. But really not sure which is better.
stuck wind
A big difference, perhaps obviously, is going to be that the psycopg pooler is on the application side. So if you have multiple app instances or other services connecting to the same db, something like pgbouncer is the better choice.
I have to learn more about how psycopg2 pooling works, but my gut tells me that PgBouncer is better. If turning on connection pooling in Django is as simple as a setting than I would just start there.
stuck wind
I am also confused about this and I use none of those options because I run ever...
If you have everything on the same host, you're not going to get ~~a lot ~~ the same amount of benefits from connection pooling. I think you're fine. 🙂
stuck wind
Reading through the psycopg docs it looks like their pooling has the potential to be very powerful. It does seem to support transaction pooling like PgBouncer, where multiple client sessions can share the same pool or even have the same client use multiple pools. I'd have to dig through the Django implementation though.
stuck wind
There's also an in depth conversation with some maintainers. The general sentiment I get is that the psycopg pool is limited compared to PgBouncer. However it was added by the same maintainer later, maybe that signifies an updated understanding?