skip to content

PgBouncer

PgBouncer multiplexes many clients onto few PostgreSQL backends in session, transaction or statement mode. Interviewers probe what breaks under transaction pooling: session state, SQL PREPARE, LISTEN.

on this pageshow

explore

questions

6

In PgBouncer, what do the session, transaction and statement values of pool_mode each do, and which one is the default?

level: juniorimportance: must knowfreq 76%

answer

  1. when the server goes back
  2. disconnect vs end of transaction vs query
  3. one mode refuses BEGIN blocks
  4. the default is the polite one

basics

~10 s

PgBouncer's pool_mode sets when a server connection returns to the pool: session (the default) on client disconnect, transaction at the end of each transaction, statement after each query, which refuses multi-statement transactions.

solid answer

~40 s

`pool_mode` decides when PgBouncer hands a PostgreSQL server connection back to its pool. In `session` mode, the default, a client keeps one server connection until it disconnects, so it behaves like a direct connection, and `server_reset_query` (`DISCARD ALL` by default) cleans the connection before the next client gets it. In `transaction` mode the server goes back when the transaction ends, so a few dozen server connections can serve many mostly idle clients; an autocommit statement is its own transaction. `statement` mode releases after every query and drops a connection that opens a transaction block. The mode is set globally in `[pgbouncer]`, per database in `[databases]` and per user in `[users]`, and `SHOW POOLS` shows the mode each pool uses.

code

ini · 10 lines
ini
[databases]
ticketing = host=10.0.0.5 dbname=ticketing pool_mode=transaction
ticketing_session = host=10.0.0.5 dbname=ticketing pool_mode=session

[users]
migrator = pool_mode=session

[pgbouncer]
listen_port = 6432
pool_mode = session

go deeper

for a junior

Name the three values and the release moment of each: client disconnect, end of transaction, end of query. Say clearly that session is the default.

for a middle

Explain where the mode can be set and which wins: [users] over [databases] over the global value. Mention that server_reset_query only runs in session mode.

for a senior

Show how you would split one database into a transaction-pooled name for the application and a session-pooled name for migrations and listeners, and how you verify it with SHOW POOLS.

for a principal

Frame the mode as a contract with every client team: transaction pooling buys connection density only if all code keeps its state inside single transactions.

## What `pool_mode` decides PgBouncer is a lightweight, single-process connection pooler that speaks the PostgreSQL wire protocol. Applications connect to it (port **6432** by default) exactly as they would connect to PostgreSQL. PgBouncer keeps its own **server connections** to the real database and lends them to **client connections**. One **pool** exists for each (database, user) pair. `pool_mode` answers a single question: **at what moment does a borrowed server connection go back to its pool**, ready for another client? Everything else about a mode follows from that moment. ## The three modes | Mode | Server connection released | What the client can rely on | Typical fit | |---|---|---|---| | `session` (**default**) | when the client disconnects | full session semantics, as on a direct connection | long-lived application servers, migrations, `psql`, `LISTEN` consumers | | `transaction` | when the transaction ends (`COMMIT`, `ROLLBACK`, or the end of an autocommit statement) | only state that lives inside one transaction | many short-lived or mostly idle clients | | `statement` | when each query finishes | single autocommit statements; transaction blocks are refused | rare autocommit-only workloads | - **Session pooling** is the most polite mode. The gain is **reuse**: when a client disconnects, a warm server connection stays behind, so the next client skips PostgreSQL's per-connection process start-up and authentication. Before reuse PgBouncer runs `server_reset_query`, which defaults to `DISCARD ALL`, so the next client does not inherit temporary tables, prepared statements, `LISTEN` registrations or `SET` values. - **Transaction pooling** is **multiplexing**. A client holds a server connection only while it is inside a transaction and holds nothing between transactions. That is how a ticketing site's fleet of short-lived serverless functions can share a few dozen server connections during an on-sale spike. The price is that the client's next transaction may land on a different PostgreSQL backend, so session-level state does not follow it. `server_reset_query` is **not** run in this mode. - **Statement pooling** is the most aggressive mode. The server goes back after every query. If a query leaves the server inside a transaction block, PgBouncer drops the connection with the message `transaction blocks not allowed in statement pooling mode`. ## Where the mode is set PgBouncer resolves the mode for each pool from the most specific setting to the least: 1. `pool_mode` on the user's line in the `[users]` section; 2. `pool_mode` in the database's entry in the `[databases]` section; 3. the global `pool_mode` in `[pgbouncer]`, which defaults to `session`. ```ini [databases] ticketing = host=10.0.0.5 dbname=ticketing pool_mode=transaction ticketing_session = host=10.0.0.5 dbname=ticketing pool_mode=session [users] migrator = pool_mode=session [pgbouncer] listen_port = 6432 pool_mode = session ``` Exposing the same PostgreSQL database under two PgBouncer database names is the usual way to give the function fleet transaction pooling while migrations, `psql` sessions and notification listeners keep session pooling. A few more details are worth knowing: - **Replication connections** that pass through PgBouncer are always session-pooled, whatever the pool's mode says. - `pool_mode` is **not** among the settings that need a process restart, so a `RELOAD` on the admin console (or `SIGHUP`) picks it up. - On the admin console, the virtual database named `pgbouncer`, `SHOW POOLS` has a `pool_mode` column that shows what each pool really uses. That is the fastest way to catch a per-user override nobody remembered. ## What each mode means for the application Session mode needs nothing from the application. Transaction mode needs the application to keep every piece of session state inside one transaction. These are the features that do not survive it: - session-level `SET` of parameters PgBouncer does not track; - SQL-level `PREPARE` / `EXECUTE`; - `LISTEN`; - session-level advisory locks; - temporary tables and `WITH HOLD` cursors kept across transactions. Statement mode additionally forbids `BEGIN ... COMMIT` blocks. ## How to choose 1. Start from what each client does with its session, not from how many clients there are. 2. If clients are short-lived and keep their state inside single transactions, use `transaction` for them; this is where PgBouncer's big multiplexing wins come from. 3. Keep `session` for anything that needs session features, ideally on a separate PgBouncer database name rather than flipping the whole instance. 4. Treat `statement` as a special case for autocommit-only traffic. Why pooling helps at all, and how big a pool should be in general, are pooling-theory questions. The PgBouncer-specific answer here is the release moment, the `session` default, and the user-over-database-over-global precedence.

  • Why might you list the same PostgreSQL database twice in PgBouncer's [databases] section?
    So each name can carry its own `pool_mode`. The application connects to a transaction-pooled name for multiplexing, while migrations, `psql` and `LISTEN` consumers connect to a session-pooled name and keep full session semantics. A per-user `pool_mode` in `[users]` does the same for one role.
  • What does server_reset_query_always change in PgBouncer, and why is it a workaround rather than a fix?
    It runs `server_reset_query` (`DISCARD ALL` by default) in every pooling mode, not only in session mode. Under transaction pooling, clients then lose all session state after every transaction. The breakage becomes deterministic instead of random state leaking between clients, but session features still do not work.

A car-share desk: session mode lends you a car for your whole stay, transaction mode for each trip, and statement mode for each single street, which is why it refuses multi-stop journeys.

  • Transaction pooling is PgBouncer's default, since that is why people install it.
  • Session mode gives no benefit over connecting straight to PostgreSQL.
  • Statement mode is the safest mode because it releases servers fastest.
  • In transaction mode PgBouncer runs DISCARD ALL after every transaction.
  • pool_mode can only be set once, globally, in the [pgbouncer] section.
open as a page

In PgBouncer, how do default_pool_size and max_client_conn differ, and how do you read SHOW POOLS when clients start queueing?

level: middleimportance: must knowfreq 61%

basics

~10 s

max_client_conn (default 100) caps client connections to PgBouncer; default_pool_size (default 20) caps server connections per database and user pair. In SHOW POOLS, rising cl_waiting and maxwait with no sv_idle mean every server is busy.

open as a page

During a planned PostgreSQL switchover behind PgBouncer 1.26, how do PAUSE, RELOAD and RESUME keep clients connected, and how do you restart PgBouncer itself without -R?

level: seniorimportance: should knowfreq 40%

basics

~20 s

PAUSE waits for every server connection to be released, then disconnects them while clients stay connected and queue; RELOAD repoints the database; RESUME continues. PgBouncer itself restarts by rolling several so_reuseport processes, since 1.26 removed -R.

open as a page

With PgBouncer in transaction mode, why do driver prepared statements work while SQL PREPARE and EXECUTE fail, and what does max_prepared_statements control?

level: seniorimportance: should knowfreq 50%

basics

~10 s

PgBouncer 1.21+ tracks protocol-level named prepared statements, renames them and re-prepares them on whichever server a client gets; SQL PREPARE is passed through untracked. max_prepared_statements, default 200 since 1.24, sizes the per-server cache.

open as a page

With PgBouncer 1.26 in transaction mode in front of PostgreSQL 18, which session state survives between transactions and which leaks or silently disappears?

level: seniorimportance: should knowfreq 57%

basics

~20 s

Only parameters PostgreSQL reports to the client are tracked and re-applied; with PgBouncer 1.26 on PostgreSQL 18 that includes search_path. Other SET values, SQL PREPARE, LISTEN, session advisory locks and temporary tables stay behind on one backend.

open as a page

In PgBouncer, how do auth_user and auth_query let clients log in without listing every user in auth_file, and how do you set them up safely?

level: middleimportance: nice to knowfreq 30%

basics

~10 s

For users missing from auth_file, PgBouncer connects as auth_user and runs auth_query to fetch the user's password hash from PostgreSQL. Safely: a non-superuser calling a SECURITY DEFINER lookup function with a pinned search_path.

open as a page