Databases
Connection pooling: multiple-choice review
Crux Multiple-choice synthesis across the pooling unit: process model, PgBouncer modes, sizing math, exhaustion vs idle-in-transaction, and pooler selection.
Your altitude — climbing toward senior
ZeroJuniorMiddleSenior
You are at senior altitude — in orbitSix questions that cut across the whole unit. Each mirrors a decision you make at 02:14 with a pager going off — not a definition to recite, but a tradeoff to weigh when the pool is empty and cl_waiting is climbing.
Goal
Confirm you can connect the Postgres process model, PgBouncer pooling modes, sizing math, the hold-time root cause of exhaustion, and pooler selection — the synthesis the seven lessons built toward.
Quiz
Completed
A team runs 40 application workers, each with a client pool of max 20, against Postgres with max_connections = 100. They direct-connect (no PgBouncer) and see 'FATAL: sorry, too many clients already'. What is the real fix?
Heads-up Each backend is a ~10 MB OS process; 800 active backends pre-allocate hundreds of MB of shared memory and push the box past its throughput peak. Raising the cap moves the symptom and degrades throughput non-linearly.
Heads-up Starving each worker to 2 connections cripples in-worker concurrency; the right per-worker size comes from the concurrency formula, and the cluster-wide cap is solved by a pooler, not by undersizing every worker.
Heads-up That is a Go heap knob with nothing to do with Postgres backend counts. The constraint here is max_connections, which a server-side pooler exists to bypass.
Quiz
Completed
An app on PgBouncer session mode switches to transaction mode for the multiplexing win. A `SET search_path = 'app, public'` issued once at connect time silently stops taking effect. Why, and what is the correct fix?
Heads-up PgBouncer does not cache or interpret SET; it just routes each transaction to whichever backend is free. The connect-time SET is invisible to backends that never executed it.
Heads-up SET is not rejected — it succeeds on whatever backend ran it. The problem is that the next transaction is on a different backend, so the effect does not persist.
Heads-up Casing is irrelevant here; the root cause is the per-transaction backend leasing model breaking connect-time session state.
Quiz
Completed
A 16-core Postgres box (NVMe, working set in RAM) is behind PgBouncer. An engineer sets default_pool_size = 200 to 'match peak concurrency' and p99 gets worse. What is the throughput-optimal pool_size and why?
Heads-up More backends past (cores × 2) reduce throughput: each snapshot acquisition is O(active_backends) and context-switch plus shared-memory contention rise super-linearly. The p99 regression is exactly the symptom of oversizing.
Heads-up That is not a sizing formula — max_connections is a hard cap unrelated to the CPU-driven throughput optimum. pool_size is derived from cores and storage parallelism, not from the cap.
Heads-up Pure ×1 undersizes for mixed OLTP where backends spend time on I/O and lock waits; the ×2 multiplier plus burst headroom is the standard starting point. ×1 is only right for purely CPU-bound query mixes.
Quiz
Completed
At 02:14 PgBouncer reports pool = 24, cl_waiting = 180, and the app returns 503s. pg_stat_activity shows 19 backends in state 'idle in transaction' with max xact age 8 minutes. The pool was sized correctly that morning. What is happening?
Heads-up Raising pool_size buys more backends held by the same stuck transactions — more proc-array contention, same hold time. The connections are held too long, not too few; this masks the cause.
Heads-up Postgres is working correctly; the backends are idle-in-transaction waiting on the application, not failing to plan. The database is waiting on the app, not the reverse.
Heads-up TCP exhaustion is rare at these scales and would show as connection-level errors, not backends sitting idle-in-transaction with old xact_start. The signal points squarely at held transactions.
Quiz
Completed
A team on PgBouncer 1.20 disabled driver prepared statements to avoid 'prepared statement does not exist' errors in transaction mode, losing ~20% throughput. They want both transaction-mode multiplexing AND prepared statements in 2026. What unlocks it?
Heads-up There is no such pool mode. The three modes are session, transaction, and statement; prepared-statement support is enabled by max_prepared_statements within transaction mode.
Heads-up server_reset_query runs a cleanup SQL command between client leases (e.g. DISCARD ALL); it has nothing to do with permitting prepared statements.
Heads-up Session mode does preserve prepared statements but throws away all multiplexing — the exact win they are trying to keep. PgBouncer 1.21+ gives both at once.
Quiz
Completed
A multi-tenant B2B SaaS runs 10,000 tenant databases, ~50 concurrent users each, with serverless functions that cold-start under load. PgBouncer on one node pegs a single CPU core and cold starts cause connection storms. Which architecture fits?
Heads-up 10,000 single-threaded PgBouncer processes is an operational nightmare and still leaves the serverless storm problem unsolved. A scale-out pooler is designed exactly for this multi-tenant case.
Heads-up PgBouncer is single-threaded — half a million inbound clients through one event loop saturates one core long before that. The inbound count, not the config value, is the limit.
Heads-up 10,000 tenants × 50 users is up to 500,000 potential connections; each cold start forks a ~10 MB backend and storms exhaust max_connections. A colocated pooler must absorb the burst.
Recap
The through-line across the unit is one chain: Postgres forks a heavyweight process per connection, so backend count must stay near (cores × 2) — which a pooler delivers by multiplexing thousands of cheap client connections in transaction mode. Transaction mode costs you connect-time session state (SET, LISTEN, SQL PREPARE, session advisory locks), each with an exact safe substitute, and PgBouncer 1.21+ finally makes prepared statements compatible. When the pool empties, the cause is almost always hold-time — a slow query or an external call inside a transaction — not size; idle_in_transaction_session_timeout is the free safety net. Reach past PgBouncer only when you have measured its single-thread ceiling or face serverless/multi-tenant scale.