PgBouncer pool_mode choice and basic sizing

Command

; /etc/pgbouncer/pgbouncer.ini
[databases]
appdb = host=<primary_host> port=5432 dbname=appdb pool_mode=transaction
reportdb = host=<primary_host> port=5432 dbname=appdb pool_mode=session

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 20
reserve_pool_size = 5
max_prepared_statements = 200
query_wait_timeout = 120

What it does

Session mode binds a client to one server connection for the lifetime of its connection; transaction mode assigns a server connection only for the duration of a transaction and serves thousands of clients with dozens of PostgreSQL connections; statement mode returns the connection after every query and disallows multi-statement transactions. pool_mode can also be overridden per database.

When to use it

Transaction mode for OLTP/microservice applications that open many short-lived connections; session mode for work that depends on session state (reporting, migrations, services using LISTEN).

Caution

In transaction mode, session state is not preserved between transactions: SET (except SET LOCAL), session-level advisory locks, LISTEN, WITH HOLD cursors, temp tables across transactions and SQL-level PREPARE are unreliable. Protocol-level prepared statements are supported only with PgBouncer 1.21+ and max_prepared_statements > 0 (default 200 since 1.24, 0 before). default_pool_size times the number of (database, user) pairs must not exceed PostgreSQL max_connections (minus the superuser_reserved_connections margin).