skip to content

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.