skip to content

Client, Pooling & Failover

How clients reach a PostgreSQL cluster and keep reaching it: the psql client, PgBouncer connection pooling and Patroni-managed failover. Interviewers probe pool modes and split-brain safety.

on this pageshow

explore

questions

18

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 psql, which backslash meta-commands list and describe a database's objects, and what do the + and S modifiers and \x change?

level: juniorimportance: must knowfreq 72%

basics

~20 s

The \d family: \dt lists tables, \d name describes one relation, \l databases, \dn schemas, \du roles, \df functions. A + suffix adds detail such as size and comments, S includes system objects, and \x prints rows as field/value lines.

open as a page

In Patroni, how do ttl, loop_wait and retry_timeout bound the leader key, and what does the primary do when renewal fails?

level: middleimportance: must knowfreq 58%

basics

~20 s

Patroni's primary holds a leader key in the DCS that expires after ttl (30 s) and renews it every loop_wait (10 s), retrying for retry_timeout (10 s). If renewal fails it demotes PostgreSQL, timed to finish before the key expires.

open as a page

With Patroni's patronictl, when do you run switchover instead of failover, and what does each command require and risk?

level: middleimportance: must knowfreq 55%

basics

~20 s

patronictl switchover is for a healthy cluster: it cleanly demotes the current leader and promotes a chosen or best replica. patronictl failover is for an unhealthy one: it needs --candidate, works without a leader, and can lose data.

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

When psql runs a migration file with -f and a statement in the middle fails, what happens by default, and how do ON_ERROR_STOP and --single-transaction change it?

level: middleimportance: must knowfreq 60%

basics

~20 s

By default psql prints the error, runs the rest of the file and exits 0. ON_ERROR_STOP=1 stops at the first error with exit status 3; -1 wraps the file in BEGIN/COMMIT, sending ROLLBACK instead when ON_ERROR_STOP catches a failure.

open as a page

How does a load balancer such as HAProxy use Patroni's REST API health endpoints to send writes to the primary and reads to replicas?

level: middleimportance: should knowfreq 46%

basics

~20 s

HAProxy health-checks each node's Patroni REST port instead of PostgreSQL: GET /primary returns 200 only on the leader, GET /replica only on a running replica without noloadbalance, so each pool sends traffic only to nodes answering 200.

open as a page

In psql, how does the \copy meta-command differ from the server-side COPY command, and when do you need \copy instead?

level: middleimportance: should knowfreq 56%

basics

~20 s

psql's \copy runs COPY ... FROM STDIN or TO STDOUT, and psql reads or writes the file on the client host with the client user's permissions. Server-side COPY uses the server's filesystem and needs superuser or pg_read_server_files / pg_write_server_files.

open as a page

During an incident, how do psql's \watch, \timing and \conninfo help you, and what exactly does each one show or measure?

level: middleimportance: should knowfreq 42%

basics

~20 s

\watch reruns the current or last query every 2 seconds until cancelled or a count or row floor stops it. \timing prints each statement's elapsed time measured by psql, network included. \conninfo shows the database, host, user and backend you are on.

open as a page

In a Patroni cluster, what happens when the primary loses its connection to etcd, and how do failsafe_mode and the watchdog change the split-brain risk?

level: seniorimportance: should knowfreq 38%

basics

~20 s

When Patroni's primary cannot reach etcd it assumes a partition and demotes PostgreSQL, so an etcd outage costs write availability. failsafe_mode lets it stay primary only if every known member confirms via POST /failsafe; the watchdog resets a node whose Patroni stalls.

open as a page

In Patroni 4, how do maximum_lag_on_failover, synchronous_mode (on or quorum) and synchronous_mode_strict change what an automatic failover can lose and when writes stall?

level: seniorimportance: should knowfreq 42%

basics

~20 s

Asynchronously, a replica over maximum_lag_on_failover bytes (default 1 MiB) behind cannot promote, bounding loss without removing it. synchronous_mode on or quorum lets only replicas holding every acknowledged commit promote; synchronous_mode_strict blocks writes rather than run unreplicated.

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 a psql deployment script, how do you pass parameters safely with -v and :'var', and generate DDL from a catalog query with \gexec?

level: seniorimportance: should knowfreq 30%

basics

~20 s

psql substitutes variables set with -v or \set before sending: :'name' as an escaped literal, :"name" as a quoted identifier. \gexec executes each cell of a query's result as its own statement, so build those statements with format() and %I.

open as a page

For a payment gateway's PostgreSQL on Patroni across two data centres, would you stretch one Patroni cluster with a third-site etcd member or run a standby cluster, and why?

level: principalimportance: should knowfreq 26%

basics

~20 s

With a third site for one etcd member, stretch one Patroni cluster and replicate synchronously across sites for automatic, loss-free failover. With only two sites, run a standby cluster in the second and promote it manually, accepting slower recovery.

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

In psql 18, what do \parse, \bind_named and the \startpipeline ... \endpipeline commands add, and when would you reach for them?

level: seniorimportance: nice to knowfreq 14%

basics

~20 s

psql normally uses the simple query protocol. psql 18's \parse creates a named protocol-level prepared statement, \bind_named executes it with parameters and \close_prepared closes it; \startpipeline ... \endpipeline queue queries without waiting for each result, as drivers do.

open as a page