/blog

6 min read

What I check before turning on connection pooling

Transaction pooling breaks a few things quietly. A checklist to run before you put PgBouncer in front of an app.

A pooler in transaction mode hands each transaction a server connection and takes it back at COMMIT. Anything that expects the connection to remember something between transactions will break, often without an error.

The checklist

  • Session settings: SET search_path or SET timezone outside a transaction won't stick.
  • Prepared statements: older drivers prepare once per connection and then fail with prepared statement "s1" does not exist.
  • Advisory locks taken with pg_advisory_lock belong to a connection you no longer own.
  • LISTEN / NOTIFY needs a dedicated connection that skips the pool.

A minimal config to start from

[pgbouncer]
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 20
server_reset_query =
max_prepared_statements = 100

Load test with the pooler in place before launch day. The bugs it exposes are much cheaper to find on a Tuesday afternoon.

$ subscribe

Want the next one?

Leave your email and new posts from Stack & Heap come to your inbox.

$ subscribe --email

Get new posts by email

Leave your address and new posts from Stack & Heap come to your inbox.

Rather write back? hello@stackandheap.example