Django persistent connections with PgBouncer?

We have database performance issues and I am looking into adding PgBouncer to handle connections more efficiently and hopefully solve the issue.

However, after configuring it, I am not sure how to tell whether PgBouncer is handling connections as intended (if at all) and in a healthy and effective way.

I think the key is deciding whether Djang's CONN_MAX_AGE setting should be set to 0 or to None. This answer says it should be None for persistent connections. But wouldn't this defeat the purpose of PgBouncer's connection pooling? Wouldn't we want to set it to 0 and release connections back to the pool instead?

I set PgBouncer's verbosity level to 3, let me paste my log when CONN_MAX_AGE is None and when it is 0, hopefully it tells you something relevant that I myself can't see:

CONN_MAX_AGE = None

2025-02-27 12:15:02.832 UTC [3336] NOISE safe_send(23, 577) = 577
2025-02-27 12:15:02.832 UTC [3336] NOISE sbuf_process_pending: end
2025-02-27 12:15:02.832 UTC [3336] NOISE resync(20): done=577, parse=577, recv=577
2025-02-27 12:15:02.832 UTC [3336] NOISE S-0x1a8a698: dbname/dbuser@127.0.0.1:5432 release_server: new state=12
2025-02-27 12:15:02.832 UTC [3336] DEBUG S-0x1a8a698: dbname/dbuser@127.0.0.1:5432 reuse_on_release: replication 0
2025-02-27 12:15:02.836 UTC [3336] NOISE resync(23): done=0, parse=0, recv=0
2025-02-27 12:15:02.836 UTC [3336] NOISE safe_recv(23, 4096) = 1044
2025-02-27 12:15:02.836 UTC [3336] NOISE sbuf_process_pending: start
2025-02-27 12:15:02.836 UTC [3336] NOISE sbuf_process_pending: loop 1
2025-02-27 12:15:02.836 UTC [3336] NOISE C-0x1a80a40: dbname/dbuser@127.0.0.1:59190 read pkt='Q' len=1044
2025-02-27 12:15:02.836 UTC [3336] NOISE C-0x1a80a40: dbname/dbuser@127.0.0.1:59190 find_server: client had no linked server yet
2025-02-27 12:15:02.836 UTC [3336] NOISE C-0x1a80a40: dbname/dbuser@127.0.0.1:59190 linking client to S-0x1a8a698
2025-02-27 12:15:02.836 UTC [3336] NOISE C-0x1a80a40: dbname/dbuser@127.0.0.1:59190 add_outstanding_request: added Q, still outstanding 1
2025-02-27 12:15:02.836 UTC [3336] NOISE sbuf_process_pending: loop 2
2025-02-27 12:15:02.836 UTC [3336] NOISE sbuf_process_pending: done looping
2025-02-27 12:15:02.836 UTC [3336] NOISE sbuf_send_pending_iobuf
2025-02-27 12:15:02.836 UTC [3336] NOISE tls_write: req=1044 out=1044
2025-02-27 12:15:02.836 UTC [3336] NOISE sbuf_process_pending: end
2025-02-27 12:15:02.836 UTC [3336] NOISE resync(23): done=1044, parse=1044, recv=1044
2025-02-27 12:15:02.837 UTC [3336] NOISE resync(20): done=0, parse=0, recv=0
2025-02-27 12:15:02.837 UTC [3336] NOISE tls_read: req=4096 out=577
2025-02-27 12:15:02.837 UTC [3336] NOISE sbuf_process_pending: start
2025-02-27 12:15:02.837 UTC [3336] NOISE sbuf_process_pending: loop 1
2025-02-27 12:15:02.837 UTC [3336] NOISE S-0x1a8a698: dbname/dbuser@127.0.0.1:5432 read pkt='T', len=419
2025-02-27 12:15:02.837 UTC [3336] NOISE sbuf_process_pending: loop 2
2025-02-27 12:15:02.837 UTC [3336] NOISE S-0x1a8a698: dbname/dbuser@127.0.0.1:5432 read pkt='D', len=138
2025-02-27 12:15:02.837 UTC [3336] NOISE sbuf_process_pending: loop 3
2025-02-27 12:15:02.837 UTC [3336] NOISE S-0x1a8a698: dbname/dbuser@127.0.0.1:5432 read pkt='C', len=14
2025-02-27 12:15:02.837 UTC [3336] NOISE sbuf_process_pending: loop 4
2025-02-27 12:15:02.837 UTC [3336] NOISE S-0x1a8a698: dbname/dbuser@127.0.0.1:5432 read pkt='Z', len=6
2025-02-27 12:15:02.837 UTC [3336] NOISE S-0x1a8a698: dbname/dbuser@127.0.0.1:5432 pop_outstanding_request: popped Q, still outstanding 0, skip 0
2025-02-27 12:15:02.837 UTC [3336] DEBUG C-0x1a80a40: dbname/dbuser@127.0.0.1:59190 query time: 1283 us
2025-02-27 12:15:02.837 UTC [3336] DEBUG C-0x1a80a40: dbname/dbuser@127.0.0.1:59190 transaction time: 1283 us
2025-02-27 12:15:02.837 UTC [3336] NOISE sbuf_process_pending: loop 5
2025-02-27 12:15:02.837 UTC [3336] NOISE sbuf_process_pending: done looping
2025-02-27 12:15:02.837 UTC [3336] NOISE sbuf_send_pending_iobuf
2025-02-27 12:15:02.837 UTC [3336] NOISE safe_send(23, 577) = 577
2025-02-27 12:15:02.837 UTC [3336] NOISE sbuf_process_pending: end
2025-02-27 12:15:02.837 UTC [3336] NOISE resync(20): done=577, parse=577, recv=577

CONN_MAX_AGE = 0

2025-02-27 12:24:45.527 UTC [3336] NOISE resync(20): done=20, parse=20, recv=20
2025-02-27 12:24:45.527 UTC [3336] NOISE resync(24): done=0, parse=0, recv=0
2025-02-27 12:24:45.527 UTC [3336] NOISE safe_recv(24, 4096) = 12
2025-02-27 12:24:45.527 UTC [3336] NOISE sbuf_process_pending: start
2025-02-27 12:24:45.527 UTC [3336] NOISE sbuf_process_pending: loop 1
2025-02-27 12:24:45.527 UTC [3336] NOISE C-0x1a80fd0: dbname/dbuser@127.0.0.1:34242 read pkt='Q' len=12
2025-02-27 12:24:45.527 UTC [3336] NOISE C-0x1a80fd0: dbname/dbuser@127.0.0.1:34242 add_outstanding_request: added Q, still outstanding 1
2025-02-27 12:24:45.527 UTC [3336] NOISE sbuf_process_pending: loop 2
2025-02-27 12:24:45.527 UTC [3336] NOISE sbuf_process_pending: done looping
2025-02-27 12:24:45.527 UTC [3336] NOISE sbuf_send_pending_iobuf
2025-02-27 12:24:45.527 UTC [3336] NOISE tls_write: req=12 out=12
2025-02-27 12:24:45.527 UTC [3336] NOISE sbuf_process_pending: end
2025-02-27 12:24:45.527 UTC [3336] NOISE resync(24): done=12, parse=12, recv=12
2025-02-27 12:24:45.527 UTC [3336] NOISE resync(20): done=0, parse=0, recv=0
2025-02-27 12:24:45.527 UTC [3336] NOISE tls_read: req=4096 out=18
2025-02-27 12:24:45.527 UTC [3336] NOISE sbuf_process_pending: start
2025-02-27 12:24:45.527 UTC [3336] NOISE sbuf_process_pending: loop 1
2025-02-27 12:24:45.527 UTC [3336] NOISE S-0x1a8a698: dbname/dbuser@127.0.0.1:5432 read pkt='C', len=12
2025-02-27 12:24:45.528 UTC [3336] NOISE sbuf_process_pending: loop 2
2025-02-27 12:24:45.528 UTC [3336] NOISE S-0x1a8a698: dbname/dbuser@127.0.0.1:5432 read pkt='Z', len=6
2025-02-27 12:24:45.528 UTC [3336] NOISE S-0x1a8a698: dbname/dbuser@127.0.0.1:5432 pop_outstanding_request: popped Q, still outstanding 0, skip 0
2025-02-27 12:24:45.528 UTC [3336] DEBUG C-0x1a80fd0: dbname/dbuser@127.0.0.1:34242 query time: 826 us
2025-02-27 12:24:45.528 UTC [3336] DEBUG C-0x1a80fd0: dbname/dbuser@127.0.0.1:34242 transaction time: 1948 us
2025-02-27 12:24:45.528 UTC [3336] NOISE sbuf_process_pending: loop 3
2025-02-27 12:24:45.528 UTC [3336] NOISE sbuf_process_pending: done looping
2025-02-27 12:24:45.528 UTC [3336] NOISE sbuf_send_pending_iobuf
2025-02-27 12:24:45.528 UTC [3336] NOISE safe_send(24, 18) = 18
2025-02-27 12:24:45.528 UTC [3336] NOISE sbuf_process_pending: end
2025-02-27 12:24:45.528 UTC [3336] NOISE resync(20): done=18, parse=18, recv=18
2025-02-27 12:24:45.528 UTC [3336] NOISE S-0x1a8a698: dbname/dbuser@127.0.0.1:5432 release_server: new state=12
2025-02-27 12:24:45.528 UTC [3336] DEBUG S-0x1a8a698: dbname/dbuser@127.0.0.1:5432 reuse_on_release: replication 0
2025-02-27 12:24:45.528 UTC [3336] NOISE resync(24): done=0, parse=0, recv=0
2025-02-27 12:24:45.528 UTC [3336] NOISE safe_recv(24, 4096) = 5
2025-02-27 12:24:45.528 UTC [3336] NOISE sbuf_process_pending: start
2025-02-27 12:24:45.528 UTC [3336] NOISE sbuf_process_pending: loop 1
2025-02-27 12:24:45.528 UTC [3336] NOISE C-0x1a80fd0: dbname/dbuser@127.0.0.1:34242 read pkt='X' len=5
2025-02-27 12:24:45.528 UTC [3336] LOG C-0x1a80fd0: dbname/dbuser@127.0.0.1:34242 closing because: client close request (age=0s)
2025-02-27 12:24:45.528 UTC [3336] NOISE safe_close(24) =

At first glance, I see no difference in the log, but there's much being logged too.

At least I have cleared my doubts on whether PgBouncer was being used at all, since I see some mentions to queries and transactions in the log when I test this on staging. All it took was changing the port to PgBouncer's.

If relevant at all, these are my PgBouncer settings:

[databases]
template1 = host=localhost port=5432 dbname=dbname

[pgbouncer]
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
logfile = /etc/pgbouncer/pgbouncer.log
pidfile = /etc/pgbouncer/pgbouncer.pid
pool_mode = transaction
max_client_conn = 250
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 2

The numbers feel a bit arbitrary, I set default_pool_size to 250 because in the mentioned answer they say it should be below Postgres's max connections, which in my case is 300.

Then they also set max_client_conn to 1000 because they have a Gunicorn setup with eventlet, which is also my case. I am running it with 17 workers, but eventlet is single-threaded unless I am mistaken, so wouldn't max connections be 17 as well? How can they get so high? I am surely missing something.

To summarize:

  • Should I set CONN_MAX_AGE to 0 or to None?

  • Do my PgBouncer logs show healthy behavior?

  • What values should I use for PgBouncer default_pool_size and max_client_conn, if I am running 17 eventlet Gunicorn workers?

Edit: I just realized each eventlet worker handles up to 1000 connections. Does this mean potentially 17000 concurrent database connections, if it weren't for Postgres's max_connections?

Back to Top