Skip to content
Tyrone Software
Database

PgBouncer transaction pooling breaks more than you expect

Abstract illustration for the article PgBouncer transaction pooling breaks more than you expect

The database hits max_connections under load, so someone puts PgBouncer in front of it. Latency improves, and a week later strange errors appear that only occur in production.

Why pooling exists

Every PostgreSQL connection is a separate server process with its own memory. Hundreds of application workers, each holding a connection, waste memory and slow down the server long before the CPU is busy. A pooler keeps a small number of real connections and lends them out.

PgBouncer has three modes. Session pooling gives a client a server connection for its whole connection. It is safe, and the saving is small. Transaction pooling lends one only for the duration of a transaction, which multiplexes far better and is what most people mean when they set it up. Statement pooling is stricter and rarely used.

What breaks in transaction mode

Two consecutive transactions from the same client may run on different server connections, so anything stored in the session is not reliable:

Table of PostgreSQL features and whether they work under PgBouncer transaction pooling
The failures are often intermittent, which is why they reach production.
  • Session-level SET. SET search_path, SET statement_timeout or SET TIME ZONE outside a transaction lands on one connection and not on the next one you get. Use SET LOCAL inside a transaction, or set defaults per role or database.
  • Session-level advisory locks (pg_advisory_lock), which stay with the connection. Use the transaction-level pg_advisory_xact_lock variant.
  • LISTEN / NOTIFY, since notifications go to a specific connection. Listeners need a direct connection or session pooling.
  • Temporary tables and cursors declared WITH HOLD.
  • Prepared statements. Named prepared statements belong to a server connection. Older PgBouncer versions could not route them correctly in transaction mode, which produces errors like "prepared statement does not exist". Recent versions added support through a max_prepared_statements setting, so check the version you run and its documentation, or disable server-side prepares in the driver.

Django specifics

With transaction pooling, set DISABLE_SERVER_SIDE_CURSORS = True in the database settings, because server-side cursors used by .iterator() live on one connection. Persistent connections (CONN_MAX_AGE) are fine and reduce connection churn between the app and PgBouncer, but remember that the pooler, not the app, now controls the real connections.

Sizing

Set the pool size from what the database can do, not from the client count. A common starting range is a small multiple of the number of CPU cores on the database server, and then measure. The sum of all pools (for every database and user pair) must stay below max_connections with room left for admin connections. If clients wait in the pool queue, more clients cannot help, and slow queries are the actual cause.

Test in the mode you will run

Development against a direct connection hides every problem above. Put PgBouncer in the staging path in the same mode as production, and run the test suite through it.

Watching the pooler itself, not only the database

PgBouncer exposes its own state through a special administrative database you connect to like any other, typically named pgbouncer, where commands such as SHOW POOLS, SHOW STATS and SHOW CLIENTS report what is actually happening inside it:

psql -h localhost -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"

SHOW POOLS reports, per database and user pair, how many clients are waiting for a server connection (cl_waiting) against how many server connections are active or idle. A pool with clients waiting is the direct evidence that the pool is undersized for the current load, which is a more reliable signal than inferring it from application-side latency alone, and it is the number to watch while tuning the sizing guidance already covered.

server_reset_query is what runs on a server connection before PgBouncer hands it to the next client in transaction mode, normally DISCARD ALL, which clears session state (prepared statements, temporary tables, session-level SET values) left behind by the previous client. It is part of why session state does not reliably survive between transactions from the same application connection: PgBouncer is actively resetting it, by design, so the safety of a stateless transaction-scoped pattern is not an accident of timing but an enforced property of the reset query.

Keep reading

Technologies

  • Python
  • PHP
  • Laravel
  • Django
  • PostgreSQL
  • MySQL
  • HTML5
  • CSS3
  • Bootstrap