pgBouncer, PgCat & Connection Pooling for Analytics Workloads

pgbouncer is the connection pooler that quietly keeps every serious Postgres analytics deployment alive — and the single misconfigured component most senior engineers inherit on day one of an "our database is mysteriously slow" incident. Postgres opens a full operating-system process per client connection; once a many-tenant analytics workload pushes that count past a few hundred, the box runs out of RAM, the scheduler thrashes, and pg_stat_activity fills with idle in transaction rows long before any actual query work begins. A postgres connection pool collapses thousands of short-lived client connections onto a small bounded set of backends, and the engineering trade-off lives in which pool mode you pick and how you tune it — not in whether you need a pooler at all.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "explain the prepared-statement gotcha with transaction pooling" or "when do you reach for pgcat instead of pgBouncer?" or "what does your pg_bouncer config look like for a 5000-client analytics workload backed by a single primary plus three read replicas?" It walks through why postgres max_connections is already the practical ceiling, the three pool modes (session pooling, transaction, statement pooling) and which Postgres features each one breaks, the canonical pgBouncer config with default_pool_size, max_client_conn, reserve_pool_size, and auth_user indirection, the PgCat sharding + read-replica routing model with built-in Prometheus, and the production patterns — connection pool sizing formula, saturation monitoring, prepared-statement-safe mode — that senior engineers ship into every analytics deployment. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the ETL practice library →, and sharpen the tuning axis with the optimization practice library →.

---

On this page

• Why Postgres needs a connection pooler
• Pool modes — session / transaction / statement
• pgBouncer config + sizing
• PgCat — the Rust-native modern alternative
• Production patterns — sizing, observability, gotchas
• Cheat sheet — connection pooling recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why Postgres needs a connection pooler

Every Postgres backend is an OS process — max_connections = 500 is the practical ceiling, not the recommended setting

The one-sentence invariant: Postgres forks a dedicated operating-system process for every client connection, so the cost of an idle connection is not zero — it is roughly 10 MB of resident RAM, a slot in the global ProcArray, and a contender in every cache-coherency scan. Other databases (MySQL, SQL Server, Oracle) use a thread-per-connection or coroutine-per-connection model where idle connections are dirt cheap. Postgres does not. The architectural choice is great for isolation and crash safety (a misbehaving backend cannot corrupt another backend's memory) and terrible for many-tenant workloads where each client opens a connection on startup, holds it for the lifetime of the process, and uses it for maybe 1% of that time.

The four axes interviewers actually probe.

• Pool mode. Session, transaction, or statement — each one trades parallelism against feature compatibility. Transaction pooling is the default for analytics, but it breaks prepared statements, SET LOCAL, LISTEN, and advisory locks. Knowing which Postgres feature your app uses is the first step to picking the right mode.
• Mode-feature compatibility. A pooler is not transparent. The pg_bouncer config will silently downgrade behaviour the moment your application reaches for a feature the chosen mode does not support. The senior signal in an interview is naming which features break in which mode without being prompted.
• Auth. pgBouncer accepts md5, scram-sha-256, cert, and auth_user indirection (where pgBouncer queries a separate user table to authenticate clients). Auth mistakes are the single most common production bug — usually a forgotten auth_query or a stale userlist.txt file after a password rotation.
• Observability. A connection pool you cannot see saturating is a connection pool that will saturate without warning. Both pgBouncer (SHOW POOLS, SHOW STATS, SHOW SERVERS) and PgCat (built-in Prometheus) expose pool metrics; the senior answer wires both into an alert pipeline.

Why max_connections is already the ceiling, not a knob to crank.

• RAM. Each backend holds roughly 10–25 MB of resident memory before any query runs (binary, shared library mappings, per-process catalog cache, work_mem allocations). A 64 GB box with max_connections = 5000 can spend 50–125 GB on idle backends — impossible.
• ProcArray scans. Many Postgres internal operations (vacuum, snapshot generation, lock-graph traversal) scan the ProcArray, which is O(max_connections). At 5000 connections every snapshot takes measurably longer; at 10000 the scan becomes a bottleneck on its own.
• Context switches. The Linux scheduler scales reasonably to a few thousand runnable processes, but the cost of switching between thousands of mostly-idle backends adds latency to every active query. Tail latency degrades long before any single query starves.
• Cache pollution. Each backend has its own catalog cache and relcache. Thousands of backends defeat the cache coherence story — the same pg_class row is loaded into every backend's private cache, and the resulting cache invalidations on DDL become expensive.
• The practical ceiling. For most production Postgres deployments, max_connections between 200 and 500 is the sweet spot — enough for the pooler plus a few admin connections, not so many that the box wastes RAM on idle backends. Beyond 500 the marginal cost of each additional slot grows non-linearly.

What a pooler actually does.

• Client side. The pooler exposes a Postgres-protocol listener on (say) port 6432. Your application connects to the pooler exactly as it would to Postgres — same wire protocol, same auth.
• Server side. The pooler maintains a bounded pool of long-lived backend connections to the actual Postgres on port 5432. When a client sends a query, the pooler leases a backend, forwards the query, returns the rows to the client, and (depending on the pool mode) returns the backend to the pool.
• Multiplexing ratio. A well-tuned postgres connection pool typically runs at 50:1 to 100:1 — 5000 client connections multiplexed onto 50–100 backend connections. The 99% of clients that are idle at any moment never hold a backend.
• Latency cost. The pooler adds one network hop and a small amount of CPU per query. For analytics workloads where queries take 10ms+ this overhead is invisible; for OLTP workloads with sub-millisecond queries the pooler adds 5–15% latency.

What interviewers listen for.

• Do you say "Postgres forks an OS process per connection" in the first sentence when asked why a pooler is needed? — senior signal.
• Do you mention the ProcArray scan cost at high max_connections? — senior signal.
• Do you push back on "just raise max_connections" with the RAM + ProcArray + context-switch argument? — required answer.
• Do you describe pooling as "collapsing many idle clients onto few busy backends" rather than as a vague "performance improvement"? — required answer.

Worked example — why max_connections is the wrong knob

Detailed explanation. The textbook anti-pattern: an app team hits "too many connections" errors and the on-call DBA raises max_connections from 200 to 2000. The error goes away for a week. Then the Postgres box runs out of RAM, the OOM killer terminates the postmaster, and the entire cluster goes down at 3 AM. Walk an interviewer through what actually happened and what should have been done.

• The symptom. Application throws FATAL: sorry, too many clients already.
• The naive fix. ALTER SYSTEM SET max_connections = 2000; SELECT pg_reload_conf(); — actually requires a restart for this parameter, but the team eventually restarts at the next maintenance window.
• The real bug. Each backend now consumes ~15 MB resident, so 2000 backends consume ~30 GB before any query runs.
• The collapse. Under load, work_mem allocations push the backends to 50 MB each, total RAM use crosses physical RAM, the OOM killer fires.

Question. A 64 GB Postgres box has max_connections = 200. The app team wants to scale to 5000 concurrent application clients. They propose raising max_connections to 5000. Quantify the RAM cost, the ProcArray cost, and propose the correct architecture using pgBouncer.

Input.

Parameter	Value before	Proposed value	RAM per backend (idle)	RAM per backend (active query with work_mem=4MB)
max_connections	200	5000	15 MB	50 MB
Total box RAM	64 GB	64 GB	—	—
shared_buffers	16 GB	16 GB	—	—
Available for backends	48 GB	48 GB	—	—

Code.

-- Diagnose current per-backend RAM use
SELECT pid,
       state,
       backend_start,
       pg_size_pretty(pg_total_relation_size('pg_database')) AS catalog_size,
       application_name
FROM   pg_stat_activity
WHERE  state != 'idle'
ORDER  BY backend_start;

-- Quantify the cost of raising max_connections
WITH proposed AS (
  SELECT 5000 AS max_conn,
         15   AS idle_mb_per_backend,
         50   AS active_mb_per_backend,
         16   AS shared_buffers_gb,
         64   AS total_ram_gb
)
SELECT max_conn,
       (max_conn * idle_mb_per_backend / 1024.0)::numeric(10,1)   AS idle_ram_gb,
       (max_conn * active_mb_per_backend / 1024.0)::numeric(10,1) AS active_ram_gb,
       total_ram_gb - shared_buffers_gb                            AS budget_gb
FROM   proposed;

; The correct architecture — pgBouncer in front, max_connections low
; postgresql.conf
max_connections = 200          ; keep low; backends are expensive
shared_buffers  = 16GB
work_mem        = 4MB

; pgbouncer.ini
[databases]
analytics = host=db.internal port=5432 dbname=analytics

[pgbouncer]
pool_mode          = transaction
max_client_conn    = 5000      ; clients can open many connections cheaply
default_pool_size  = 50        ; only 50 backends per (db, user) pair
reserve_pool_size  = 10
reserve_pool_timeout = 5
listen_port        = 6432
auth_type          = scram-sha-256
auth_file          = /etc/pgbouncer/userlist.txt

Step-by-step explanation.

1. The diagnostic query against pg_stat_activity confirms the current backend count and lets you eyeball application_name to find the offenders. Connection storms almost always trace back to one or two services that misconfigure their driver-side pool.
2. The cost calculation: 5000 backends × 15 MB idle = 75 GB just to hold idle connections. The box has 48 GB available after shared_buffers. The proposal fails on RAM alone before any active query runs. Under active load (50 MB per backend) the cost balloons to 250 GB — 5× physical RAM.
3. The correct architecture keeps max_connections = 200 on Postgres and puts pgBouncer in front. pgBouncer accepts up to 5000 client connections (max_client_conn = 5000) but only opens 50 backend connections to Postgres (default_pool_size = 50). The multiplexing ratio is 100:1.
4. Because most clients are idle most of the time, 50 backends comfortably handle the active queries. If the active query count exceeds 50, the next clients wait briefly in pgBouncer's queue (or borrow from the reserve_pool_size = 10 overflow pool).
5. The Postgres box now uses 50 × 15 MB = 750 MB on backends instead of 75 GB. The same hardware now supports 100× the application clients without running out of RAM.

Output.

Architecture	max_connections	Active client cap	RAM on backends (idle)	RAM on backends (active)	Outcome
Direct connect, max_conn=200	200	200	3 GB	10 GB	Fails at 5000 clients
Direct connect, max_conn=5000	5000	5000	75 GB	250 GB	OOM at 3 AM
pgBouncer transaction pool	200 (50 in use)	5000	750 MB	2.5 GB	Stable; 100:1 ratio

Rule of thumb. Never raise max_connections above ~500 on a Postgres box. Instead, put pgBouncer (or PgCat) in front and let the pooler absorb the client-side connection count. The pooler is the answer; tuning max_connections is at best a workaround and at worst the cause of a 3 AM OOM.

Worked example — connection storm during a deploy

Detailed explanation. Another classic: a Kubernetes deployment of an analytics microservice scales from 10 pods to 200 in response to a traffic spike. Each pod opens a driver-side pool of 20 connections on startup. The new pod-side pool count is 200 × 20 = 4000 connections — all attempting to open simultaneously against a Postgres with max_connections = 500. The first 500 succeed; the rest get FATAL: sorry, too many clients already and the new pods crash-loop.

• The connection storm. All 200 pods finish startup within seconds of each other and race for connections.
• The fanout. 200 pods × 20 connections each = 4000 attempts in flight.
• The Postgres limit. max_connections = 500 — the first 500 succeed and the rest fail immediately.
• The cascade. The crashed pods are rescheduled, retry the connection storm, and the cycle continues until rate-limited.

Question. Design the architecture that survives a 20× pod scale-up event without overwhelming Postgres. Quantify the connection counts at each layer.

Input.

Layer	Before deploy	After deploy
Pods	10	200
Per-pod driver pool size	20	20
Total client-side pool	200	4000
Postgres max_connections	500	500

Code.

# Kubernetes deployment — apps connect to pgBouncer service, NOT direct to Postgres
apiVersion: v1
kind: Service
metadata:
  name: pgbouncer
spec:
  selector:
    app: pgbouncer
  ports:
    - port: 6432
      targetPort: 6432
  type: ClusterIP

---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: analytics-app
spec:
  replicas: 200
  template:
    spec:
      containers:
        - name: app
          env:
            - name: DATABASE_URL
              value: postgres://app:pw@pgbouncer:6432/analytics

; pgbouncer.ini — sized for 200 pods × 20 driver conns
[pgbouncer]
pool_mode          = transaction
max_client_conn    = 8000      ; headroom: 200 pods × 20 conns × 2× safety
default_pool_size  = 80        ; backend cap to Postgres
reserve_pool_size  = 20
reserve_pool_timeout = 3
server_idle_timeout = 600

Step-by-step explanation.

1. Without pgBouncer, the 200 pods open 4000 connections directly to Postgres. With max_connections = 500, 3500 attempts fail. The deploy is a guaranteed outage.
2. With pgBouncer in front, the 4000 client connections terminate at the pooler. pgBouncer accepts all 4000 simultaneously (max_client_conn = 8000 gives headroom).
3. pgBouncer then opens at most 80 backend connections to Postgres (default_pool_size = 80). The Postgres connection count stays comfortably below max_connections = 500, leaving budget for admin connections, replication slots, and a safety margin.
4. The clients that arrive while the 80 backends are busy wait in pgBouncer's internal queue. Because most queries are short, the queue drains quickly.
5. When the deploy stabilises and the per-pod traffic settles, the actual active query count is typically 30–50, well under 80. The pool runs at ~50% utilisation in steady state — the right design point.

Output.

Layer	Direct connect	With pgBouncer
Client pool size	4000	4000
Backend connections to Postgres	4000 (3500 fail)	80
Postgres max_connections usage	500/500 = 100%	80/500 = 16%
Deploy outcome	Outage	Stable

Rule of thumb. Always size your client-side driver pool against the pooler, not against Postgres. The driver pool is cheap; the Postgres pool is the expensive one. Put pgBouncer (or PgCat) in front before your second microservice ever ships.

Worked example — multi-tenant SaaS connection math

Detailed explanation. A multi-tenant SaaS runs 500 customer tenants in a shared Postgres. Each tenant has its own database role and database (tenant_001, tenant_002, ...). Each app server opens one driver-side connection per tenant on demand. The naive math: 50 app servers × 500 tenants × 1 driver conn = 25000 connections — clearly impossible.

• The constraint. Postgres pools are per (database, user) pair. 500 tenants × 50 app servers means 25000 distinct potential pools.
• The mitigation. pgBouncer pools by (db, user) and shares idle servers across tenants only if server_reset_query resets per-session state.
• The win. Most tenants are inactive most of the time. The active concurrent tenant count is closer to 20, and the per-tenant query rate is low.

Question. Design the pool configuration for a 500-tenant SaaS where the active concurrent tenant count is 20 at p99 and each active tenant runs at most 3 concurrent queries.

Input.

Parameter	Value
Total tenants	500
Active tenants at p99	20
Concurrent queries per active tenant	3
App servers	50

Code.

; pgbouncer.ini — multi-tenant pool sizing
[databases]
* = host=db.internal port=5432   ; wildcard match for all tenant DBs

[pgbouncer]
pool_mode          = transaction
max_client_conn    = 5000
default_pool_size  = 5           ; per (db, user) pair — 20 active × 5 = 100 backends
max_db_connections = 200         ; cap per database
max_user_connections = 1000      ; cap per user
server_idle_timeout = 60         ; reclaim idle backends from quiet tenants quickly
server_reset_query  = DISCARD ALL ; reset session state when returning to pool

Step-by-step explanation.

1. The naive pool sizing — default_pool_size = 5 per (db, user) — sets a per-tenant cap. Inactive tenants don't pre-allocate backends; the pool is created lazily on first query and reclaimed by server_idle_timeout when the tenant goes quiet.
2. At p99, 20 active tenants × 5 backends = 100 backends in use. With max_connections = 200 on Postgres, this leaves 100 slots of headroom for admin connections, sudden bursts, and the active vacuum / autovacuum workers.
3. server_reset_query = DISCARD ALL is critical for multi-tenant: when a backend is returned to the pool, the next checkout might be from a different client. DISCARD ALL resets prepared statements, temp tables, session GUCs, and unlisten — preventing tenant-A state from leaking into a tenant-B query.
4. The wildcard * = host=db.internal port=5432 in [databases] is the multi-tenant trick: a single pgBouncer instance handles all 500 tenant databases without an explicit entry per tenant. The downside is no per-tenant override.
5. With this config, the steady-state Postgres connection count is 50–100, well within max_connections = 200. The pgBouncer side sees 25000 logical client connections (50 app servers × 500 tenants) but only opens backends on demand.

Output.

Metric	Value
Distinct (db, user) pools managed by pgBouncer	up to 500
Active pools at p99	20
Backend connections to Postgres at p99	100
Postgres max_connections setting	200
Effective multi-tenant fanout	250:1 (25000 logical clients : 100 backends)

Rule of thumb. For multi-tenant workloads, size default_pool_size for the per-active-tenant concurrency, not for the per-tenant connection cap. server_reset_query = DISCARD ALL is mandatory; without it, session state leaks across tenants and you ship a confidentiality bug.

Senior interview question on max_connections and the pooling architecture

A senior interviewer often opens with: "You inherit a Postgres deployment with max_connections = 2000 and no connection pooler. The app team wants to scale 10x. Walk me through how you'd architect the fix, what numbers you'd target at each layer, and what failure modes you'd guard against."

Solution Using pgBouncer in front of Postgres with a sizing formula

; Step 1 — drop Postgres max_connections back to a sane number
; postgresql.conf
max_connections          = 300
shared_buffers           = 16GB
work_mem                 = 4MB
maintenance_work_mem     = 256MB
max_worker_processes     = 16

; Step 2 — pgBouncer in front, transaction pool mode
; pgbouncer.ini
[databases]
analytics = host=db.internal port=5432 dbname=analytics

[pgbouncer]
pool_mode             = transaction
max_client_conn       = 10000          ; 10x scale headroom on client side
default_pool_size     = 80             ; backend cap to Postgres
reserve_pool_size     = 20             ; emergency overflow
reserve_pool_timeout  = 5              ; seconds before reserve kicks in
server_idle_timeout   = 600            ; reclaim idle backends after 10 min
server_lifetime       = 3600           ; recycle backends every hour
auth_type             = scram-sha-256
auth_file             = /etc/pgbouncer/userlist.txt

; Step 3 — sizing formula:
;   default_pool_size = (active_clients × queries_per_client) / avg_query_concurrency
;   active_clients   = 1000 at p99
;   queries_per_client = 0.1 (10% of clients have an active query)
;   avg_query_concurrency  = 1
;   default_pool_size = 1000 × 0.1 / 1 = 100 → round to 80, leave headroom

Step-by-step trace.

Step	Before	After
Postgres max_connections	2000	300
RAM on idle backends (15 MB each)	30 GB	4.5 GB
Client connection cap	2000	10000 (via pgBouncer)
Backend connections in use at p99	unbounded	80 (capped)
ProcArray scan cost	2000 entries	300 entries
Connection error budget	0 (already saturated)	9920 spare client slots

After the rollout, Postgres metrics show the active backend count flat at 50–80, ProcArray scans drop from 1.5 ms to 0.3 ms (5× faster snapshot), and the application's connection-acquire p99 drops from 200 ms (waiting for Postgres) to 5 ms (waiting for pgBouncer). The 10x scale-up event becomes a non-event.

Output:

Metric	Before	After
max_connections	2000	300
Client cap	2000	10000
Backend conns at p99	1500+	80
Box RAM on backends	30 GB	4.5 GB
Postgres OOM risk	high	low

Why this works — concept by concept:

• Pooler in front, low max_connections — the architectural lever is moving the connection cap from Postgres to pgBouncer. Postgres stays at a sane 300; the pooler absorbs the 10x client growth on a cheap, thread-based runtime.
• Sizing formula — default_pool_size = (active_clients × queries_per_client) / avg_query_concurrency is the textbook starting point. Round down to leave headroom; the reserve_pool_size overflow handles transient spikes.
• Reserve pool — reserve_pool_size = 20 + reserve_pool_timeout = 5 is the safety valve. Normal load uses 80 backends; a 30-second spike borrows from the reserve up to 100, then queues. This isolates the spike from the steady state.
• Server idle + lifetime — server_idle_timeout = 600 reclaims backends from quiet periods; server_lifetime = 3600 recycles every hour to bound memory growth (Postgres backends leak a small amount of memory over very long sessions).
• Cost — pgBouncer adds one network hop (~0.1 ms) per query and roughly 5–15% CPU on the pooler box. For analytics workloads (query latency 10ms+) this is invisible. The avoided Postgres RAM cost (25 GB+) and the avoided ProcArray scan cost (1+ ms per snapshot) are O(max_connections) and dominate.

SQL
Topic — sql
SQL pool-tuning and connection-saturation problems

Practice →

Optimization Topic — optimization Optimization problems on Postgres tuning

Practice →

---

2. Pool modes — session / transaction / statement

Three pool modes, three different feature surfaces — picking the right mode is half the connection-pool interview

The mental model in one line: session pooling leases a backend for the lifetime of the client connection (most permissive, least efficient), transaction pooling leases a backend per transaction (the analytics default, breaks prepared statements + LISTEN + SET LOCAL), and statement pooling leases a backend per statement (most efficient, breaks any multi-statement transaction). Every other pgBouncer or PgCat interview question is a consequence of which mode you chose and which Postgres feature your app uses.

Session pooling — the most-permissive mode.

• Lease granularity. Backend is leased to the client for the lifetime of the client connection. When the client disconnects, the backend goes back to the pool.
• Feature compatibility. Anything Postgres supports works — prepared statements, session-level GUCs, LISTEN/NOTIFY, advisory locks, temp tables. The pool is a thin layer that only manages connection lifecycle.
• Multiplexing ratio. Roughly 1:1 — there's almost no point using session pooling unless you're protecting Postgres from connection-storm events (where the cost of the TCP handshake + auth is the issue, not the connection count itself).
• When to use. Apps that hold connections for long periods, apps that use session-level features (LISTEN, prepared statements in older drivers), or apps where every connection is genuinely independent.

Transaction pooling — the analytics default.

• Lease granularity. Backend is leased to the client for the duration of one transaction. BEGIN ... COMMIT is one lease; the next BEGIN may get a different backend.
• What breaks. Anything that lives at session scope: prepared statements (the backend may change between PREPARE and EXECUTE), SET (not SET LOCAL), session-level temp tables, LISTEN/NOTIFY (notifications go to whichever backend was leased when the LISTEN ran), session-level advisory locks (pg_advisory_lock).
• What works. Single-statement queries, multi-statement transactions inside a single BEGIN/COMMIT, SET LOCAL (scoped to the transaction), transaction-level temp tables, transaction-level advisory locks (pg_advisory_xact_lock).
• Multiplexing ratio. Typically 50:1 to 200:1 — the workhorse mode for analytics, where most queries are short read-only transactions.
• When to use. Web apps, analytics dashboards, any workload where the per-query state is bounded by the transaction.

Statement pooling — the most-efficient mode.

• Lease granularity. Backend is leased to the client for one statement. Multi-statement transactions are not allowed — BEGIN errors out.
• What breaks. Any multi-statement transaction. Implicit transactions (Postgres auto-wraps each statement in a transaction, but at statement pool granularity that's still one statement = one transaction = one lease).
• What works. Single-statement queries (autocommit mode).
• Multiplexing ratio. Typically 100:1 to 500:1 — the highest fan-out, used for very high-volume read-mostly workloads where transactional integrity is not required (or is handled by an outer mechanism).
• When to use. Pure-read analytics dashboards, dashboard backends running stateless SELECTs, anything that already avoids multi-statement transactions.

The PREPARE gotcha — why prepared statements break under transaction pooling.

• The issue. Prepared statements are session-scoped in Postgres. PREPARE stmt AS SELECT ... lives on the backend that executed the PREPARE. Under transaction pooling, the next EXECUTE stmt on the same client connection may be routed to a different backend that has never seen stmt.
• The symptom. ERROR: prepared statement "stmt" does not exist.
• The fix. Use track_prepared_statements = 1 in modern pgBouncer (4.x) which adds server-side prepared-statement tracking, OR move the prepared-statement use to session-pool mode, OR have the driver use unnamed (no-PREPARE) statements with parameters.

The LISTEN gotcha — why notifications break under transaction pooling.

• The issue. LISTEN channel registers a session-level listener. Under transaction pooling, that registration lives on the backend that ran the LISTEN; future NOTIFY events delivered to that backend never reach your client (which now holds a different backend on its next lease).
• The fix. Run any LISTEN consumer on a separate pgBouncer database configured with pool_mode = session. Don't mix LISTEN with the rest of your analytics traffic.

Common interview probes on pool modes.

• "Walk me through what happens to a prepared statement under transaction pooling" — required answer is the session-scope mismatch.
• "Your app uses LISTEN/NOTIFY. What pool mode do you pick?" — session pooling on a dedicated pgBouncer database.
• "What multiplexing ratio is realistic for transaction pooling?" — 50:1 to 200:1 depending on idle ratio.
• "Why is statement pooling rarely used?" — most ORMs auto-wrap multi-statement work in transactions; statement mode breaks that.

Worked example — same workload, three pool modes

Detailed explanation. A dashboards app sends a steady stream of single-statement read queries against an analytics Postgres. The team measures the multiplexing ratio (client connections : backend connections) achievable under each pool mode for the same load profile. The numbers tell the story.

• Load profile. 1000 client connections, each running one query every 5 seconds, each query taking 50 ms.
• Active fraction. 50 ms / 5000 ms = 1% of clients have an active query at any moment.
• Naive expectation. 1% × 1000 = 10 backends should suffice.

Question. Calculate the realistic default_pool_size for session, transaction, and statement pool modes against this workload, and show the resulting Postgres backend count.

Input.

Parameter	Value
Client connections	1000
Query rate per client	0.2 queries/sec (1 per 5 sec)
Query latency	50 ms
Total query rate	200 queries/sec
Active concurrent queries	200 × 0.05 = 10

Code.

; Mode A — session pooling
[pgbouncer]
pool_mode         = session
default_pool_size = 1000    ; 1:1 — backend held for client lifetime

; Mode B — transaction pooling
[pgbouncer]
pool_mode         = transaction
default_pool_size = 20      ; ~2x active concurrency for safety

; Mode C — statement pooling
[pgbouncer]
pool_mode         = statement
default_pool_size = 15      ; tighter — backend returns after every statement

-- Diagnose realised backend count under each mode
SELECT pool_mode, cl_active, sv_active, sv_idle
FROM   pgbouncer.pools
WHERE  database = 'analytics';

Step-by-step explanation.

1. Under session pooling, every client holds its own backend for as long as the client connection lives. 1000 clients = 1000 backends. The multiplexing ratio is 1:1 — there's no benefit beyond the slight saving on TCP handshakes. Postgres needs max_connections >= 1010.
2. Under transaction pooling, the backend is leased per BEGIN/COMMIT. With autocommit, every statement is a tiny transaction. At any moment, only 10 clients have an active query, so 10 backends would barely suffice; default_pool_size = 20 gives 2× headroom. Multiplexing ratio: 50:1.
3. Under statement pooling, the backend is leased per statement. With 10 active queries concurrent at any moment, 15 backends comfortably handle the load with safety margin. Multiplexing ratio: 67:1.
4. The cliff between session and transaction is enormous (1000 vs 20 backends — 50× saving). The cliff between transaction and statement is small (20 vs 15 — 25% saving). The win is moving from session to transaction; statement is incremental.
5. The catch is that statement mode forbids BEGIN/COMMIT. If any client opens an explicit transaction, pgBouncer rejects the BEGIN. The team must verify every code path uses single statements before flipping the switch.

Output.

Mode	default_pool_size	Backends used	Multiplexing ratio	Caveat
Session	1000	1000	1:1	No real benefit
Transaction	20	20	50:1	No session-level features
Statement	15	15	67:1	No multi-statement txns

Rule of thumb. For analytics workloads, default to transaction pooling with default_pool_size = 2 × active_concurrent_queries. Only escalate to statement pooling if you've verified the entire codebase uses autocommit and the extra 20–30% multiplexing matters at your scale.

Worked example — the prepared-statement gotcha

Detailed explanation. A Spring Boot app uses the Postgres JDBC driver with prepareThreshold = 5 — the driver auto-prepares any statement executed 5 or more times. The app sits behind pgBouncer in transaction pool mode. After deploy, errors start surfacing intermittently: ERROR: prepared statement "S_3" does not exist. The bug is the session-scope mismatch between Postgres prepared statements and transaction-level connection leases.

• The naming. The JDBC driver names auto-prepared statements S_1, S_2, S_3, .... Each backend has its own namespace.
• The lease. Under transaction pooling, EXECUTE runs on whichever backend pgBouncer leases for that transaction.
• The mismatch. S_3 lives on backend A (where PREPARE ran); the next EXECUTE may land on backend B (no such name).

Question. Diagnose the error and propose three fixes — driver config, pgBouncer config (modern), and pool-mode change. Recommend the right one.

Input.

Component	Setting
App	Spring Boot + JDBC driver
Driver param	prepareThreshold=5
Pool mode	transaction
pgBouncer version	1.21 (modern)
Postgres version	16
Symptom	ERROR: prepared statement "S_3" does not exist (intermittent)

Code.

; Fix 1 — driver-level workaround: disable auto-prepare
; jdbc:postgresql://pgbouncer:6432/analytics?prepareThreshold=0
; Pros: works on any pgBouncer version
; Cons: loses prepared-statement plan caching → ~10–15% slower queries

; Fix 2 — pgBouncer 1.21+ server-side prepared-statement tracking
; pgbouncer.ini
[pgbouncer]
pool_mode                  = transaction
max_prepared_statements    = 100      ; track up to 100 unique prepared stmts
; pgBouncer now intercepts PREPARE/EXECUTE/DEALLOCATE and re-prepares
; on the assigned backend if needed.

; Fix 3 — move app to session pool
[databases]
analytics_with_prep = host=db port=5432 dbname=analytics pool_mode=session
; Use a separate database name for the subset of clients that need prep stmts

-- Confirm pgBouncer's prepared-statement tracking is active
SHOW STATS_TOTALS;
SHOW PREPARED_STATEMENTS;   -- pgbouncer 1.21+

Step-by-step explanation.

1. The diagnosis: under transaction pooling, the JDBC driver prepares S_3 on backend A, then on the next transaction pgBouncer leases backend B, which has never seen S_3. The driver sends EXECUTE S_3 ... → Postgres → prepared statement does not exist. The intermittent nature reflects the random backend assignment.
2. Fix 1 disables driver-side prepared statements. The driver sends parameterised queries as unnamed statements (Bind + Execute in the extended protocol with no Parse of a named statement). Always works, but loses plan caching — measure the 10–15% per-query overhead before shipping.
3. Fix 2 (the modern answer) uses pgBouncer 1.21's server-side prepared-statement tracking. pgBouncer remembers the PREPARE on backend A; when EXECUTE goes to backend B, pgBouncer transparently re-PREPAREs on B and forwards. The driver is unchanged; the per-statement plan-cache benefit is preserved.
4. Fix 3 moves prep-stmt-heavy clients to a dedicated database name with pool_mode=session on a per-db basis. Heavyweight but compatible with older pgBouncer.
5. The recommendation: Fix 2 if you can upgrade pgBouncer; Fix 1 if you're on an older version and the 10–15% latency cost is acceptable; Fix 3 only for the small subset of clients that genuinely need session-level features.

Output.

Fix	pgBouncer version	Driver change	Latency cost	Recommendation
1: prepareThreshold=0	any	yes	+10–15%	OK on older versions
2: track_prepared_statements	1.21+	no	~0%	Best for modern stacks
3: session pool db	any	partial	none, but no multiplexing	Niche

Rule of thumb. If you're on pgBouncer 1.21+, turn on max_prepared_statements and keep transaction pool mode. The performance and operational story is unambiguously the best of the three.

Worked example — LISTEN/NOTIFY under transaction pool

Detailed explanation. A background-job system uses Postgres LISTEN/NOTIFY to wake workers when a new job is enqueued. The team puts pgBouncer in front and switches to transaction pool mode. Workers stop receiving NOTIFY events. The bug is the same session-scope mismatch — LISTEN registers a listener on one backend; NOTIFY events sent to that backend never reach the worker, which holds a different backend on its next checkout.

• The pattern. Workers run LISTEN job_queue once at startup, then block on a Postgres protocol-level notification message.
• The broken behaviour under transaction pooling. The LISTEN registers on backend A. The worker's connection then sits idle (no active transaction), so the next NOTIFY from the producer goes to whichever backend happens to be leased to it. The worker hears nothing.
• The fix. LISTEN/NOTIFY needs session-level affinity. Move the workers to a dedicated database with pool_mode = session.

Question. Architect the fix so the LISTEN-using workers and the rest of the analytics traffic both work — without disabling pgBouncer for the analytics traffic.

Input.

Component	Setting
Pool mode (main analytics)	transaction
Pool mode (LISTEN workers)	needs session
Postgres role for workers	bg_worker
Postgres database name	analytics

Code.

; pgbouncer.ini — two virtual databases, one Postgres
[databases]
analytics         = host=db port=5432 dbname=analytics            pool_mode=transaction
analytics_notify  = host=db port=5432 dbname=analytics            pool_mode=session

[pgbouncer]
default_pool_size = 50
max_client_conn   = 5000

; Analytics traffic connects to "analytics"
; LISTEN workers connect to "analytics_notify"
; Same underlying Postgres database; different pgBouncer pool modes.

-- Worker startup, against pgbouncer://.../analytics_notify
LISTEN job_queue;

-- Producer, against pgbouncer://.../analytics
NOTIFY job_queue, 'job_id=42';

Step-by-step explanation.

1. The trick: pgBouncer treats [databases] entries as virtual database names. Two entries can point at the same physical Postgres database with different pool modes. The application just connects to a different name.
2. The LISTEN workers connect to analytics_notify (pool_mode = session). Each worker holds a backend for the lifetime of its connection. The backend keeps the LISTEN job_queue registration alive.
3. The producers connect to analytics (pool_mode = transaction). Their NOTIFY job_queue, 'job_id=42' command is forwarded by pgBouncer to whichever backend is leased; that backend asynchronously broadcasts the notification to all backends that LISTEN on job_queue.
4. The worker's listening backend receives the notification and delivers it to the client. The worker wakes up, fetches the job, processes it, and goes back to LISTENing.
5. The session pool for the workers is small (one backend per worker), so the multiplexing benefit is on the analytics-traffic side, not the worker side. Most teams have 10s of workers and 1000s of analytics clients — splitting the two is essentially free.

Output.

Path	Pool mode	Backends used	Behaviour
Analytics clients → analytics	transaction	50 (pooled)	Multiplexed
Workers → analytics_notify	session	1 per worker	LISTEN works

Rule of thumb. Whenever you have a small set of clients that need session-level Postgres features (LISTEN, advisory locks, session GUCs), give them their own pgBouncer virtual database with pool_mode = session. Don't relax the pool mode for the whole deployment.

Senior interview question on pool-mode selection

A senior interviewer might ask: "An analytics app uses prepared statements via the JDBC driver, runs a few multi-statement transactions per request, and has a small admin tool that uses LISTEN/NOTIFY. Walk me through which pool mode you'd pick for which path and the config that makes them coexist."

Solution Using per-database pool modes + modern prepared-statement tracking

; pgbouncer.ini — three virtual databases, three pool modes, one Postgres
[databases]
analytics        = host=db port=5432 dbname=analytics  pool_mode=transaction
analytics_notify = host=db port=5432 dbname=analytics  pool_mode=session
analytics_stmt   = host=db port=5432 dbname=analytics  pool_mode=statement

[pgbouncer]
pool_mode               = transaction       ; default for [databases] without explicit override
default_pool_size       = 80
reserve_pool_size       = 20
max_client_conn         = 8000
max_prepared_statements = 200              ; pgBouncer-managed prep-stmt cache
auth_type               = scram-sha-256
auth_file               = /etc/pgbouncer/userlist.txt
server_reset_query      = DISCARD ALL
server_idle_timeout     = 600
server_lifetime         = 3600

; Client routing:
;   analytics-app           → pgbouncer:6432/analytics         (transaction, prep stmts via pgbouncer cache)
;   admin-listen-tool       → pgbouncer:6432/analytics_notify  (session, LISTEN works)
;   dashboard-readonly-svc  → pgbouncer:6432/analytics_stmt    (statement, max multiplexing)

Step-by-step trace.

Client	Database	Pool mode	What works	What doesn't
analytics-app (JDBC + prep stmts + multi-stmt txns)	analytics	transaction	multi-stmt txns; prep stmts via pgBouncer cache	LISTEN, session GUCs
admin-listen-tool	analytics_notify	session	LISTEN, advisory locks, session GUCs	(no multiplexing)
dashboard-readonly-svc	analytics_stmt	statement	single-stmt SELECTs; max multiplexing	multi-stmt txns

After the rollout, the analytics-app sees the prep-stmt error rate drop to zero (pgBouncer caches them), the admin-listen tool keeps working without a separate Postgres bypass, and the dashboard backend achieves the highest multiplexing ratio. The configuration is the canonical "split-by-pool-mode" pattern.

Output:

Surface	Result
analytics-app prep-stmt errors	0/min
admin-listen-tool LISTEN events	delivered, p99 < 100ms
dashboard-readonly-svc backends	30 → handles 3000 client conns
Total Postgres backends in use at p99	~100
Postgres max_connections	300 (plenty of headroom)

Why this works — concept by concept:

• Per-database pool modes — pgBouncer's [databases] section accepts a per-entry pool_mode override. Different client paths can use different modes against the same underlying Postgres database. The application just connects to a different name.
• Modern prepared-statement tracking — max_prepared_statements = 200 enables pgBouncer to intercept PREPARE / EXECUTE / DEALLOCATE and re-prepare transparently when a backend swap happens. Keeps transaction mode + prep stmts working.
• server_reset_query — DISCARD ALL runs on every backend return to the pool, scrubbing prepared statements, temp tables, GUCs, listen registrations. Critical for transaction and statement modes; pointless in session mode.
• server_lifetime — caps the maximum age of a backend at 1 hour. Some Postgres backends leak small amounts of memory over very long lifetimes; recycling bounds the cost.
• Cost — three pool modes, one pgBouncer instance, one Postgres. Operational overhead is O(1) — config-only. The pool-mode split is the cheapest way to support the union of all Postgres features behind a single pooler.

SQL
Topic — sql
SQL transaction and pooling edge cases

Practice →

Optimization Topic — optimization Optimization problems on pool mode and multiplexing

Practice →

---

3. pgBouncer config + sizing

Five knobs decide the pool — default_pool_size, max_client_conn, reserve_pool_size, server_idle_timeout, and the auth flow

The mental model in one line: a production pg_bouncer config boils down to (1) the per-pool backend cap, (2) the global client cap, (3) the reserve overflow, (4) the lifecycle timers, and (5) the auth flow — get those five right and the rest is observability. The pgbouncer.ini file is small; every line earns its place.

The five knobs.

• default_pool_size. The cap on backend connections per (database, user) pool. The most important knob — set it via the sizing formula, not by guessing.
• max_client_conn. The global cap on client connections to pgBouncer. Set this to your worst-case fan-out (driver pool size × number of app servers × safety factor).
• reserve_pool_size + reserve_pool_timeout. Overflow pool that activates only when the primary pool is exhausted and a client has waited reserve_pool_timeout seconds. Absorbs transient spikes without sizing the steady-state pool too large.
• server_idle_timeout + server_lifetime. Lifecycle timers. Idle backends are reclaimed after server_idle_timeout; every backend is recycled after server_lifetime regardless. Bounds idle resource use and per-connection memory growth.
• auth_type + auth_file + auth_user + auth_query. The auth flow — either a static userlist.txt or an indirect lookup via auth_user running auth_query against the real Postgres user table.

The auth flow — four modes.

• trust. No password check (dev only).
• md5 / scram-sha-256. Static userlist.txt with "username" "hash" lines. Simple but rotation is operationally painful.
• auth_user indirect lookup. pgBouncer authenticates as a privileged auth_user, runs auth_query (e.g. SELECT usename, passwd FROM pg_shadow WHERE usename = $1) against Postgres to fetch the hash, then validates the client. Eliminates the static file; rotation is automatic.
• cert. TLS client certificate; the CN of the cert is the username. Used in zero-trust environments.

The lifecycle timers — what each one prevents.

• server_idle_timeout (default 600 s). Reclaims backends after 10 minutes idle. Prevents idle backends from holding RAM on the Postgres box forever.
• server_lifetime (default 3600 s). Hard recycle. Even active backends are gracefully retired after 1 hour. Bounds the memory leak surface of long-running backends.
• query_timeout (default 0 = disabled). Cancels a query that takes longer than the limit. Optional; usually delegated to statement_timeout on the Postgres side.
• client_idle_timeout (default 0). Disconnects clients that hold the connection without sending traffic. Catches misbehaving clients.

The observability commands.

• SHOW POOLS. One row per (db, user) pool — columns: cl_active, cl_waiting, sv_active, sv_idle, sv_used, sv_tested, sv_login, maxwait. The single most useful command in a pool-saturation incident.
• SHOW STATS. Per-database query/transaction/wait totals — total_xact_count, total_query_count, total_received, total_sent, total_xact_time, total_query_time, total_wait_time. Use to find which db/user pair is busiest.
• SHOW SERVERS. One row per backend connection — pid, address, state, lifetime stats. Use to find stuck or misbehaving backends.
• SHOW CLIENTS. One row per client connection — pid, state, last query time. Use to find misbehaving clients.

The sizing formula in detail.

• Inputs. active_clients (clients with an in-flight query at p99), avg_query_concurrency (typically 1 — one query per active client at a time).
• Output. default_pool_size = ceil(active_clients × avg_query_concurrency / target_utilisation).
• Target utilisation. Aim for 60–70% — leaves headroom for spikes without over-provisioning.
• Worked example. 1000 client connections, 10% active fraction → 100 active clients. default_pool_size = ceil(100 × 1 / 0.7) = 144. Round up to 150 for round-numbers convenience.

Common interview probes on pgBouncer config.

• "What's a sane default_pool_size for an analytics workload of 5000 clients?" — derive it from the active fraction; don't guess.
• "What does reserve_pool_size do?" — overflow pool that activates only after reserve_pool_timeout wait.
• "Why use auth_user instead of userlist.txt?" — indirect lookup eliminates password-rotation pain.
• "When does server_lifetime matter?" — long-running Postgres backends leak small amounts of memory; recycle every hour.

Worked example — sizing a pool for 5000 clients on 100 backends

Detailed explanation. A textbook sizing problem. The team has 50 app servers, each with a driver-side pool of 100 connections, so the worst-case client count to pgBouncer is 5000. The Postgres box can comfortably run 100 backends. Pick the default_pool_size, max_client_conn, and reserve_pool_size. Show the multiplexing arithmetic.

• Worst-case client count. 50 × 100 = 5000 client connections (mostly idle).
• Active fraction at p99. 2% — 100 clients have an in-flight query.
• Backend budget. 100 — Postgres can handle this without RAM pressure.

Question. Produce a complete pgbouncer.ini for this workload, with explicit reasoning for each value.

Input.

Parameter	Value
App servers	50
Driver pool per server	100
Worst-case client conns	5000
Active fraction at p99	2%
Active clients at p99	100
Postgres backend budget	100

Code.

; /etc/pgbouncer/pgbouncer.ini
[databases]
analytics = host=db.internal port=5432 dbname=analytics

[pgbouncer]
; Listener
listen_addr           = 0.0.0.0
listen_port           = 6432

; Auth
auth_type             = scram-sha-256
auth_file             = /etc/pgbouncer/userlist.txt

; Pool sizing — derived from the formula above
pool_mode             = transaction
default_pool_size     = 80              ; ceil(100 active × 1 / 0.7) ≈ 144, but Postgres budget caps us at 80
reserve_pool_size     = 20              ; +20 emergency = 100 total ≤ Postgres budget
reserve_pool_timeout  = 5               ; seconds before reserve kicks in

; Client cap
max_client_conn       = 5500            ; 5000 worst case + 10% safety
max_db_connections    = 100             ; hard cap per-db = Postgres budget
max_user_connections  = 100             ; hard cap per-user

; Lifecycle
server_idle_timeout   = 600
server_lifetime       = 3600
server_login_retry    = 5

; Resets
server_reset_query    = DISCARD ALL
ignore_startup_parameters = extra_float_digits

; Logging
log_connections       = 0               ; off at scale; rely on aggregate stats
log_disconnections    = 0
log_pooler_errors     = 1

Step-by-step explanation.

1. default_pool_size = 80 is below the formula's 144 because the Postgres-side budget is 100, not the formula. The pool size you can actually allocate is min(formula, budget). Always reconcile the formula against the Postgres max_connections budget.
2. reserve_pool_size = 20 brings the total to 100 — the full Postgres budget. The reserve only activates when steady-state is exhausted and a client has waited 5 seconds, so it's reserved for spikes, not for steady-state use.
3. max_client_conn = 5500 accepts the worst-case 5000 plus 10% safety. Set this conservatively; pgBouncer per-client RAM is tiny (~2 KB) so over-provisioning is cheap.
4. max_db_connections = 100 and max_user_connections = 100 are belt-and-braces caps. Even if pool sizes are misconfigured, no more than 100 backends can be opened.
5. server_reset_query = DISCARD ALL is non-negotiable for transaction pool mode — it scrubs prepared statements, temp tables, GUCs, and listeners every time a backend returns to the pool. ignore_startup_parameters = extra_float_digits silences a benign warning emitted by some JDBC versions.

Output.

Knob	Value	Why
default_pool_size	80	Formula = 144; Postgres budget = 100; reserve takes 20
reserve_pool_size	20	Overflow for transient spikes
max_client_conn	5500	Worst case + 10% safety
max_db_connections	100	Hard cap = Postgres budget
pool_mode	transaction	Analytics default

Rule of thumb. Reconcile the sizing formula against the Postgres backend budget. The formula tells you the demand; the Postgres budget tells you the supply. The smaller of the two wins; use reserve_pool_size for elasticity above the steady-state target.

Worked example — auth_user indirect lookup

Detailed explanation. A team rotates Postgres passwords every 30 days for SOC 2 compliance. With static userlist.txt, each rotation requires updating the file on every pgBouncer instance, restarting pgBouncer, and timing the rollout to avoid auth failures. The auth_user indirect lookup eliminates the static file — pgBouncer queries Postgres on every login to fetch the hash, so rotation is just ALTER USER ... PASSWORD ....

• The static-file pain. N pgBouncer instances × every password rotation = N file updates + N restarts.
• The indirect-lookup win. Single privileged auth_user connects to Postgres, queries pg_shadow (or a custom view) for the client's hash, validates against the client's submitted password.
• The catch. The auth_user itself needs a static credential — usually the only entry in userlist.txt.

Question. Configure pgBouncer with auth_user indirect lookup. Show the Postgres-side setup (the pgbouncer role and the auth function) and the pgBouncer side.

Input.

Component	Value
Postgres role for indirect lookup	pgbouncer (privileged for SELECT on pg_shadow)
Postgres version	16
pgBouncer auth_type	scram-sha-256

Code.

-- Postgres side — create the indirect-lookup role and the auth function
CREATE ROLE pgbouncer WITH LOGIN PASSWORD 'replace-with-strong-secret';

-- Function that returns (usename, passwd) for a given username
CREATE OR REPLACE FUNCTION public.pgbouncer_get_auth(p_usename TEXT)
RETURNS TABLE(usename TEXT, passwd TEXT)
AS $$
  SELECT usename::TEXT, passwd::TEXT
  FROM   pg_shadow
  WHERE  usename = p_usename
$$ LANGUAGE SQL SECURITY DEFINER;

-- Lock down the function — only the pgbouncer role can call it
REVOKE ALL ON FUNCTION public.pgbouncer_get_auth(TEXT) FROM PUBLIC;
GRANT  EXECUTE ON FUNCTION public.pgbouncer_get_auth(TEXT) TO pgbouncer;

; pgbouncer side — minimal userlist.txt has only the pgbouncer role
; /etc/pgbouncer/userlist.txt
"pgbouncer" "SCRAM-SHA-256$4096:..."   ; hashed password for the pgbouncer role only

; pgbouncer.ini
[pgbouncer]
auth_type   = scram-sha-256
auth_file   = /etc/pgbouncer/userlist.txt
auth_user   = pgbouncer
auth_query  = SELECT usename, passwd FROM public.pgbouncer_get_auth($1)

Step-by-step explanation.

1. The Postgres side creates a privileged role pgbouncer and a SECURITY DEFINER function that returns (usename, passwd) from pg_shadow. The function is the only path pgBouncer uses to fetch hashes; granting EXECUTE only to the pgbouncer role minimises the attack surface.
2. The userlist.txt on the pgBouncer side now has one entry — the pgbouncer role itself. Every other user's password is fetched dynamically from Postgres at login time. Password rotation is an ALTER USER ... PASSWORD ... on the Postgres side; no pgBouncer file change required.
3. The auth_query is the SQL pgBouncer runs against Postgres (as the auth_user) to fetch the hash. The $1 placeholder is replaced with the client's submitted username.
4. When a client connects and presents (username, password), pgBouncer (a) opens a connection to Postgres as pgbouncer, (b) runs SELECT usename, passwd FROM public.pgbouncer_get_auth($1) with $1 = username, (c) compares the returned hash to the submitted password via scram-sha-256.
5. The result: password rotation is automated on the Postgres side; the only persistent secret on the pgBouncer host is the pgbouncer role's own credential, which rotates rarely.

Output.

Step	Static userlist.txt	auth_user indirect
Rotate one user's password	edit file on N pgBouncer hosts	ALTER USER on Postgres only
Add a new user	edit file on N pgBouncer hosts + restart	CREATE USER on Postgres only
Secret count on pgBouncer host	N (every user)	1 (the pgbouncer role itself)
Attack surface	full userlist file	one SQL function

Rule of thumb. For any production deployment with more than 5 users, switch to auth_user indirect lookup. The static-file model only makes sense for the very smallest deployments where password rotation isn't a requirement.

Worked example — observability with SHOW POOLS

Detailed explanation. A senior on-call DBA notices the analytics dashboard is slow. The first command in any pool-saturation incident is SHOW POOLS — it shows whether clients are waiting for backends, whether the reserve pool is in use, and how long the longest-waiting client has been blocked. Walk through reading the output of SHOW POOLS and the alert thresholds that catch saturation early.

• The columns. cl_active = clients with an active query; cl_waiting = clients waiting for a backend; sv_active = backends serving a query; sv_idle = backends in the pool but idle; maxwait = seconds the longest-waiting client has been waiting.
• The healthy snapshot. cl_waiting = 0, maxwait = 0, sv_active + sv_idle ≈ default_pool_size. Pool is healthy.
• The saturated snapshot. cl_waiting > 0, maxwait > 1, sv_active = default_pool_size, sv_idle = 0. Pool is saturated; clients are queuing.

Question. Build a SQL alert query that runs SHOW POOLS (via the pgbouncer admin database) and alerts when maxwait > 5 for any pool. Define the runbook the on-call follows when the alert fires.

Input.

Setup	Value
pgBouncer admin port	6432, database = pgbouncer
Alert threshold	maxwait > 5 seconds
Probe interval	30 seconds

Code.

-- Connect to the pgbouncer admin database:
--   psql -h pgbouncer -p 6432 -U admin pgbouncer

-- Query the pool state
SHOW POOLS;

-- Example output (transformed to plain SQL for clarity):
-- database  | user    | cl_active | cl_waiting | sv_active | sv_idle | maxwait
-- analytics | app_ro  |       180 |         42 |        80 |       0 |       8
-- analytics | app_rw  |        12 |          0 |         8 |       2 |       0
-- pgbouncer | pgbouncer|       1  |          0 |         0 |       0 |       0

# Prometheus-compatible alert runner
import psycopg2, time
from prometheus_client import start_http_server, Gauge

g_maxwait    = Gauge('pgbouncer_pool_maxwait_seconds',  'maxwait per pool', ['db','user'])
g_cl_waiting = Gauge('pgbouncer_pool_cl_waiting',       'clients waiting',  ['db','user'])
g_sv_active  = Gauge('pgbouncer_pool_sv_active',        'backends in use',  ['db','user'])

def scrape():
    conn = psycopg2.connect("postgres://admin@pgbouncer:6432/pgbouncer")
    conn.autocommit = True
    cur = conn.cursor()
    cur.execute("SHOW POOLS;")
    for row in cur.fetchall():
        db, user, cl_active, cl_waiting, sv_active, sv_idle, *_ , maxwait = row
        g_maxwait.labels(db, user).set(maxwait)
        g_cl_waiting.labels(db, user).set(cl_waiting)
        g_sv_active.labels(db, user).set(sv_active)
    conn.close()

if __name__ == "__main__":
    start_http_server(9127)
    while True:
        scrape()
        time.sleep(30)

Step-by-step explanation.

1. The scrape connects to the pgBouncer admin port (6432) and the special pgbouncer database — this is where the SHOW POOLS, SHOW STATS, and SHOW SERVERS admin commands live.
2. The Python loop runs every 30 seconds, exporting three gauges per pool to Prometheus. The on-call alert is pgbouncer_pool_maxwait_seconds > 5 for: 2m — sustained queue depth across two scrapes.
3. The reading of SHOW POOLS: pool (analytics, app_ro) has 180 active clients, 42 waiting, 80 backends all busy, and the longest waiter has been blocked for 8 seconds. This pool is saturated; default_pool_size = 80 is insufficient under this load.
4. The pool (analytics, app_rw) has 12 active, 0 waiting, 8 of 10 backends busy. Healthy.
5. The runbook when the alert fires: (a) check SHOW STATS to identify the slow query type; (b) check Postgres pg_stat_activity for idle in transaction — leaked transactions are the most common cause; (c) consider temporarily raising default_pool_size; (d) longer-term, profile the slow queries to reduce their hold time.

Output.

Pool	cl_waiting	maxwait	Healthy?
analytics / app_ro	42	8 s	SATURATED — page on-call
analytics / app_rw	0	0 s	OK
pgbouncer / admin	0	0 s	OK

Rule of thumb. SHOW POOLS is the first command in every pool incident. Alert on maxwait > 5 for 2 minutes (sustained queueing) — not on instantaneous spikes (which are noise). The runbook always starts with "find the slow query," not "raise the pool size."

Senior interview question on pgBouncer config

A senior interviewer might ask: "You're given a 64 GB Postgres box and a workload of 8000 client connections from 80 app servers. Walk me through the complete pgBouncer config, every value, and how you'd choose between increasing default_pool_size or adding a second pgBouncer instance behind a load balancer."

Solution Using a complete pgbouncer.ini with sizing + HA topology

; /etc/pgbouncer/pgbouncer.ini — single instance, full-feature
[databases]
analytics = host=db.internal port=5432 dbname=analytics

[pgbouncer]
; Network
listen_addr            = 0.0.0.0
listen_port            = 6432
unix_socket_dir        = /var/run/pgbouncer

; Auth — auth_user indirect lookup
auth_type              = scram-sha-256
auth_file              = /etc/pgbouncer/userlist.txt
auth_user              = pgbouncer
auth_query             = SELECT usename, passwd FROM public.pgbouncer_get_auth($1)

; Pool sizing
pool_mode              = transaction
default_pool_size      = 120
reserve_pool_size      = 30
reserve_pool_timeout   = 5
max_client_conn        = 9000
max_db_connections     = 150
max_user_connections   = 150
max_prepared_statements = 200

; Lifecycle
server_idle_timeout    = 600
server_lifetime        = 3600
server_login_retry     = 5
server_reset_query     = DISCARD ALL
query_timeout          = 0
client_idle_timeout    = 0
ignore_startup_parameters = extra_float_digits

; Logging + admin
log_pooler_errors      = 1
admin_users            = pgbouncer_admin
stats_users            = pgbouncer_stats

HA topology — when to scale out instead of up
=============================================

Single instance: capable up to ~50000 client conns and ~10000 qps per box
                 limited by single-process event loop (pgBouncer is single-threaded)

Scale-up first: tune default_pool_size; reserve_pool_size; lifetimes
Scale-out:      add a 2nd pgBouncer behind L4/L7 LB (HAProxy or AWS NLB)
                each instance has its own pool → pool size halves per instance
                Postgres-side max_connections must accommodate the SUM
                Or shard by app-server hash to keep backend pools partitioned

Step-by-step trace.

Question	Answer	Reasoning
What's default_pool_size?	120	8000 × 2% active / 0.7 utilisation ≈ 230; capped by 150 Postgres budget; 30 reserved
What's max_client_conn?	9000	8000 worst case + 12% safety
Auth model?	auth_user indirect	80 app servers × N users; static file too painful
pool_mode?	transaction	Analytics default; modern prep-stmt tracking on
Scale up or out?	up first, then out	Single pgBouncer handles 50000 conns; only scale out at ~10000 qps

The single-instance config above carries the workload comfortably. The HA pattern (two pgBouncer instances behind an L4 LB) becomes worthwhile only once a single instance saturates CPU on the event loop (typically around 8000–12000 qps depending on packet size).

Output:

Layer	Capacity
Single pgBouncer	up to 50000 client conns, ~10000 qps
Two-instance pool	doubles qps; halves per-instance pool size
Postgres max_connections	sum across all pgBouncer instances + admin headroom

Why this works — concept by concept:

• Single-knob calculation — default_pool_size derives from (active_clients × concurrency) / target_utilisation, capped by the Postgres backend budget. The other knobs follow.
• Reserve pool elasticity — reserve_pool_size = 30 + reserve_pool_timeout = 5 gives the system room to absorb a 25% spike without sizing the steady-state pool for the worst case.
• Auth_user indirection — 80 app servers means a static file is unmanageable; auth_user makes rotation an ALTER USER.
• Single-threaded event loop — pgBouncer's runtime is single-process, single-thread, libevent-based. You scale out, not up, after one instance saturates. The HA topology halves the per-instance pool capacity, so the Postgres max_connections must accommodate the sum of all instances' pool sizes.
• Cost — config-only; the operational cost is O(1) in instance count. The HA story costs an L4 LB + a Postgres-side max_connections bump proportional to instance count.

SQL
Topic — sql
SQL pool-sizing and config problems

Practice →

ETL Topic — etl ETL problems on Postgres throughput tuning

Practice →

---

4. PgCat — the Rust-native modern alternative

pgcat is what pgBouncer would look like if you rewrote it for 2025 — multi-shard, read-replica-aware, Prometheus-native

The mental model in one line: PgCat is a Rust-implemented Postgres-protocol pooler that adds multi-shard routing, read/write splitting, replica load balancing, and first-class Prometheus metrics on top of the same pool-mode primitives pgBouncer pioneered. It is not a drop-in replacement for pgBouncer in every workload — but for analytics deployments with primary + read replicas, sharded Postgres clusters, or modern observability requirements, it is increasingly the default.

What PgCat adds over pgBouncer.

• Multi-shard routing. Define multiple Postgres shards in the config; PgCat routes each query to the shard determined by a sharding key extracted from the query parameters or comments. Native support for pg_shard_id style routing.
• Read/write splitting. Mark Postgres instances as primary or replica. Read-only queries (auto-detected or explicit) are load-balanced across replicas; writes go to the primary. Routing is per-statement, not per-connection.
• Replica load balancing. Multiple read replicas are pooled as a group; PgCat picks the replica with the fewest in-flight queries (least-loaded) or by round-robin.
• Failure handling. Health checks on replicas; failed replicas are taken out of rotation automatically and re-added on recovery. The primary failover is harder — PgCat reads the topology from config (or via Consul / etcd in advanced setups).
• Prometheus metrics built-in. /metrics endpoint exposes per-pool and per-server gauges (active connections, queued clients, query latency histograms). No sidecar exporter required.
• Modern auth. SCRAM-SHA-256 and TLS by default; client certs optional.
• Memory safety. Written in Rust, no buffer-overflow class of bugs that periodically affect pgBouncer.

What PgCat doesn't (yet) have.

• The pgBouncer ecosystem. 15 years of operational lore, blog posts, hardened production wisdom. PgCat is younger.
• Some pgBouncer admin commands. PgCat's admin interface is closer in spirit but not identical.
• Single-process simplicity for the smallest deployments. For a 100-client app on one Postgres, pgBouncer is still the lower-friction answer.

The sharding model.

• Static shards. Each shard is a (host, port, database) tuple. The number of shards is fixed in config.
• Sharding key. Either extracted from the query via a regex on a comment (/* shard:42 */ SELECT ...), from a SQL function call (SET pgcat.shard_id = 42), or from a parameter binding.
• Routing. PgCat hashes the sharding key (or uses the raw integer) and maps to a shard.
• Cross-shard queries. Not supported transparently; cross-shard work must be done at the app layer or via a query rewriter.

The read/write split.

• Auto-detection. PgCat inspects the SQL and routes by the first verb — SELECT to replicas, INSERT/UPDATE/DELETE to primary. The detection has caveats (CTEs, function calls) and can be disabled per-query with a comment.
• Explicit override. /* role:primary */ or /* role:replica */ in the SQL forces the routing.
• Replica lag awareness. Not yet built-in; the app must handle eventual-consistency reads (read-your-writes against the primary, eventually-consistent reads against replicas).

The Prometheus integration.

• Endpoint. http://pgcat:9930/metrics (configurable).
• Key metrics. pgcat_servers_active, pgcat_servers_idle, pgcat_clients_waiting, pgcat_query_duration_seconds_bucket, pgcat_shard_load, pgcat_replica_health.
• No sidecar. Compared to pgBouncer (which needs prometheus-pgbouncer-exporter or a custom SHOW POOLS scraper), PgCat exposes metrics natively.

Common interview probes on PgCat.

• "When do you pick PgCat over pgBouncer?" — multi-shard, read replicas, native Prometheus, or you need Rust memory safety.
• "How does PgCat handle a failed replica?" — health check fails → replica drops out of rotation → recovers → re-added.
• "What's the sharding key model?" — extracted from comment, SET, or parameter; hashed to a shard.
• "Does PgCat replace pgBouncer everywhere?" — no, the simplest single-Postgres deployments are still simpler with pgBouncer.

Worked example — PgCat config for primary + 3 read replicas

Detailed explanation. An analytics deployment has one Postgres primary and three read replicas. The app sends a mix of writes (~5%) and reads (~95%). The goal is to route writes to the primary and load-balance reads across the three replicas, while keeping connection-pool semantics identical to pgBouncer's transaction mode.

• Topology. One primary (db-primary.internal:5432) plus three replicas (db-r1.internal:5432, db-r2.internal:5432, db-r3.internal:5432).
• Workload split. 95% SELECT / 5% INSERT-UPDATE-DELETE.
• Goal. Reads load-balanced across the three replicas; writes always to the primary.

Question. Produce a complete pgcat.toml for this topology with transaction-pool semantics, 80 backends total to the primary, and 80 backends across the three replicas (load-balanced).

Input.

Component	Value
Primary	db-primary.internal:5432
Replicas	db-r1, db-r2, db-r3 (each :5432)
Read fraction	95%
Write fraction	5%
Total backend budget per Postgres	80

Code.

# /etc/pgcat/pgcat.toml
[general]
host                = "0.0.0.0"
port                = 6432
admin_username      = "pgcat_admin"
admin_password      = "replace-with-strong-secret"

# Prometheus metrics
prometheus_exporter_port = 9930

[pools.analytics]
pool_mode         = "transaction"
default_role      = "any"               # auto-detect from SQL verb
query_parser_enabled = true             # parse SQL to extract role
primary_reads_enabled = true            # allow primary to serve reads if all replicas down
sharding_function = "pg_bigint_hash"    # not used here; one shard

[pools.analytics.users.0]
username          = "app_ro"
password          = "..."
pool_size         = 80
statement_timeout = 30000

[pools.analytics.shards.0]
# Primary
[[pools.analytics.shards.0.servers]]
host              = "db-primary.internal"
port              = 5432
role              = "primary"

# Replicas — load balanced
[[pools.analytics.shards.0.servers]]
host              = "db-r1.internal"
port              = 5432
role              = "replica"

[[pools.analytics.shards.0.servers]]
host              = "db-r2.internal"
port              = 5432
role              = "replica"

[[pools.analytics.shards.0.servers]]
host              = "db-r3.internal"
port              = 5432
role              = "replica"

-- Application — no code change required for basic read/write split
SELECT * FROM events WHERE ts > now() - INTERVAL '1 hour';
-- ↑ auto-routed to a replica (least-loaded across r1, r2, r3)

INSERT INTO events (ts, payload) VALUES (now(), '...');
-- ↑ auto-routed to primary

-- Force read from primary (e.g. for read-your-writes)
/* role:primary */ SELECT * FROM events WHERE id = 42;

Step-by-step explanation.

1. The TOML defines one pool (analytics) with one shard (0) and four servers in that shard: the primary and three replicas. Each server has a role (primary or replica).
2. query_parser_enabled = true makes PgCat inspect every SQL statement and route by the verb — SELECTs go to a replica, mutations go to the primary. This works for the vast majority of analytics workloads.
3. When a SELECT arrives, PgCat picks the least-loaded of the three replicas (least in-flight queries). Round-robin is an alternative load-balancing mode set via config.
4. The primary_reads_enabled = true flag is a safety net: if all replicas are unhealthy, reads fall through to the primary rather than failing. The downside is the primary momentarily handles 100% of the traffic; the upside is no outage.
5. Forcing a read to the primary uses the SQL comment /* role:primary */. The driver-side change is one comment per query; no infrastructure swap is needed.

Output.

Query type	Routed to	Backend cost
SELECT (default)	one of r1/r2/r3 (least loaded)	1 backend on the chosen replica
/* role:primary */ SELECT	primary	1 backend on primary
INSERT / UPDATE / DELETE	primary	1 backend on primary
BEGIN ... COMMIT (multi-stmt)	primary (transaction sticks to one server)	1 backend on primary

Rule of thumb. PgCat's auto-detection works for 95% of analytics workloads out of the box. For the 5% that need explicit routing (read-your-writes, materialised-view refreshes), the SQL comment override is the lowest-friction escape hatch.

Worked example — sharded routing across 4 Postgres instances

Detailed explanation. A larger analytics deployment shards events by tenant_id across 4 Postgres clusters. The app already knows the shard for each tenant (computed as tenant_id % 4) and includes it in a SQL comment on every query. PgCat extracts the shard ID from the comment and routes accordingly. This is the multi-shard story that pgBouncer cannot do natively.

• Sharding scheme. shard_id = tenant_id % 4. Each shard is a separate Postgres cluster (primary + replicas).
• Routing. The app annotates every query with /* shard:N */ .... PgCat parses the comment.
• Backend budget. 50 backends per shard.

Question. Produce a pgcat.toml with four shards (each a primary + 1 replica) and demonstrate the sharding routing via SQL comments.

Input.

Shard	Primary	Replica	Tenants
0	db-s0-p:5432	db-s0-r:5432	tenant_id % 4 == 0
1	db-s1-p:5432	db-s1-r:5432	tenant_id % 4 == 1
2	db-s2-p:5432	db-s2-r:5432	tenant_id % 4 == 2
3	db-s3-p:5432	db-s3-r:5432	tenant_id % 4 == 3

Code.

# /etc/pgcat/pgcat.toml — 4-shard config
[general]
host = "0.0.0.0"
port = 6432
prometheus_exporter_port = 9930

[pools.events]
pool_mode = "transaction"
sharding_function = "pg_bigint_hash"     # not used; we route by explicit comment
query_router_enabled = true
default_role = "any"
shard_id_regex = '/\* shard:(\d+) \*/'   # extract from SQL comment

[pools.events.users.0]
username  = "app"
password  = "..."
pool_size = 50

# Shard 0
[pools.events.shards.0]
[[pools.events.shards.0.servers]]
host = "db-s0-p.internal"
port = 5432
role = "primary"
[[pools.events.shards.0.servers]]
host = "db-s0-r.internal"
port = 5432
role = "replica"

# Shards 1, 2, 3 — same shape, different hosts (omitted for brevity)
[pools.events.shards.1]
[[pools.events.shards.1.servers]]
host = "db-s1-p.internal"
port = 5432
role = "primary"
[[pools.events.shards.1.servers]]
host = "db-s1-r.internal"
port = 5432
role = "replica"

[pools.events.shards.2]
[[pools.events.shards.2.servers]]
host = "db-s2-p.internal"
port = 5432
role = "primary"
[[pools.events.shards.2.servers]]
host = "db-s2-r.internal"
port = 5432
role = "replica"

[pools.events.shards.3]
[[pools.events.shards.3.servers]]
host = "db-s3-p.internal"
port = 5432
role = "primary"
[[pools.events.shards.3.servers]]
host = "db-s3-r.internal"
port = 5432
role = "replica"

# App-side — annotate every query with the shard ID
def query_for_tenant(tenant_id: int, sql: str):
    shard = tenant_id % 4
    annotated = f"/* shard:{shard} */ {sql}"
    return cursor.execute(annotated)

# Example
query_for_tenant(42, "SELECT * FROM events WHERE tenant_id = 42 ORDER BY ts DESC LIMIT 100")
# Sent as: /* shard:2 */ SELECT * FROM events WHERE tenant_id = 42 ORDER BY ts DESC LIMIT 100
# PgCat extracts shard=2, routes to the shard-2 primary/replica pool

Step-by-step explanation.

1. The [pools.events] section defines four shards (shards.0 through shards.3). Each shard has its own primary + replica. PgCat treats them as separate pools internally.
2. shard_id_regex = '/\* shard:(\d+) \*/' is the comment-extractor pattern. Every query the app sends has a /* shard:N */ comment at the start; PgCat parses the digits and routes.
3. The app code computes shard = tenant_id % 4 and prefixes every query with the matching comment. The application logic stays simple — the routing decision is centralised at the pooler.
4. Within a shard, the existing read/write split applies: SELECTs go to the replica, mutations to the primary, with auto-detection unless overridden by /* role:primary */.
5. Cross-shard queries are not supported transparently — the app must issue one query per shard and union the results. This is the cost of horizontal sharding: simplicity at the pooler layer; complexity at the app layer for any query spanning shards.

Output.

Query	shard comment	Routed to
/* shard:0 */ SELECT ...	0	db-s0 replica
/* shard:2 */ INSERT ...	2	db-s2 primary
/* shard:3 / / role:primary */ SELECT ...	3	db-s3 primary (override)

Rule of thumb. Multi-shard routing is the killer PgCat feature. If your roadmap includes horizontal Postgres sharding, choose PgCat from day one — bolting it onto a pgBouncer deployment after the fact is significantly more work.

Worked example — Prometheus metrics + Grafana dashboard

Detailed explanation. Native Prometheus metrics are the headline operational win of PgCat over pgBouncer. A single scrape job pulls per-pool and per-server gauges; a Grafana dashboard wires them into pool-utilisation, query-latency, and replica-health panels. The on-call now sees pool saturation in the same dashboard as Postgres CPU, with no sidecar exporter to operate.

• Endpoint. http://pgcat-host:9930/metrics.
• Format. Prometheus exposition format — labelled gauges and histograms.
• Pull model. Prometheus scrapes every 15 seconds; metric retention is whatever the Prometheus deployment configured.

Question. Configure Prometheus to scrape PgCat, then write three PromQL queries: pool utilisation, p99 query latency, and replica health. Show the dashboard layout.

Input.

Component	Value
PgCat metrics port	9930
Prometheus version	2.x
Scrape interval	15 s

Code.

# prometheus.yml — scrape config
scrape_configs:
  - job_name: pgcat
    scrape_interval: 15s
    static_configs:
      - targets:
          - pgcat-1.internal:9930
          - pgcat-2.internal:9930

# 1. Pool utilisation per pool — % of pool_size in use
sum by (pool) (pgcat_servers_active{pool="analytics"})
  /
sum by (pool) (pgcat_pool_size{pool="analytics"})

# 2. p99 query latency per pool
histogram_quantile(
  0.99,
  sum by (le, pool) (rate(pgcat_query_duration_seconds_bucket{pool="analytics"}[5m]))
)

# 3. Replica health — 0 if any replica is unhealthy
min by (pool, shard) (pgcat_server_healthy{role="replica"})

Grafana dashboard layout — "PgCat Analytics"
============================================
Row 1 — Pool utilisation
   panel: pool utilisation (PromQL #1)             threshold: warn > 0.7, crit > 0.9
   panel: clients waiting per pool                 threshold: warn > 10
   panel: maxwait per pool                         threshold: warn > 5 s
Row 2 — Query latency
   panel: p99 query latency (PromQL #2)            threshold: depends on SLO
   panel: query rate per pool
   panel: error rate per pool
Row 3 — Replica health
   panel: replica health (PromQL #3)               threshold: alert == 0
   panel: per-replica load
   panel: primary writes per second

Step-by-step explanation.

1. The Prometheus scrape config lists each PgCat host on port 9930. Two PgCat instances behind a load balancer means two scrape targets — labels distinguish them.
2. PromQL query 1 computes pool utilisation as active / pool_size. Above 70% is "approaching saturation"; above 90% is "page on-call." The query is per-pool because different pools (analytics_ro, analytics_rw) have independent budgets.
3. PromQL query 2 uses Prometheus's histogram_quantile on the bucketed query-latency histogram. The 5-minute window smooths the noise; the p99 surfaces tail latency that the average hides.
4. PromQL query 3 takes the minimum of per-replica health gauges; if any one replica is unhealthy (gauge = 0), the result is 0 and the alert fires. The min-aggregation is the standard pattern for "all members of a group must be healthy."
5. The Grafana dashboard groups these into three rows: pool, latency, replica. The on-call walks the rows top-to-bottom during an incident — pool saturation (capacity) → latency (per-query slowness) → replica health (topology).

Output.

PromQL query	Threshold	Page?
Pool utilisation	> 0.9 for 2m	yes
p99 latency	> SLO for 5m	yes
Replica health	== 0 for 1m	yes
Clients waiting	> 100 for 2m	yes

Rule of thumb. If you already run Prometheus + Grafana, PgCat's native metrics save weeks of exporter-glue work. The dashboard becomes the canonical operational view for the pool layer.

Senior interview question on PgCat vs pgBouncer

A senior interviewer might ask: "You're picking the pooler for a new analytics platform with one Postgres primary, three read replicas, and a roadmap for horizontal sharding next year. Walk me through whether you'd start with pgBouncer or PgCat, and what the migration story would be if you started with the wrong one."

Solution Using PgCat from day one for the sharded roadmap

Decision framework — pgBouncer vs PgCat in 2026
===============================================

1. Topology: primary + N replicas?
   yes → PgCat (native read/write split)
   no  → pgBouncer is simpler

2. Sharding roadmap?
   yes → PgCat (multi-shard native)
   no  → pgBouncer is simpler

3. Prometheus already in stack?
   yes → PgCat (native /metrics)
   no  → pgBouncer + exporter sidecar

4. Team size + ecosystem familiarity?
   small team, pgBouncer mature → pgBouncer
   modern team, OK with newer tool → PgCat

5. Memory safety paranoia?
   high (zero-CVE budget) → PgCat (Rust)
   standard               → pgBouncer (C, mature)

Step-by-step trace.

Question	Decision	Pushes to
Q1 — Topology	primary + 3 replicas	PgCat (native R/W split)
Q2 — Sharding	roadmap for horizontal sharding	PgCat (multi-shard)
Q3 — Prometheus	yes, existing stack	PgCat (native metrics)
Q4 — Team size	mid-size, OK with newer tools	PgCat
Q5 — Memory safety	standard, no special requirement	tie

Four of five push to PgCat; one is neutral. PgCat is the unambiguous start. The migration cost if the team started with pgBouncer would be roughly 3 months: replicate the auth model, swap the config layout (ini → toml), validate the read/write split, smoke-test the comment-based shard routing, and run both in parallel for a week before cutover.

Output:

Tool	When it wins
pgBouncer	Single-Postgres analytics, no replicas, mature team, no Prometheus
PgCat	Primary + replicas, sharding roadmap, Prometheus-native, modern team

Why this works — concept by concept:

• Match the tool to the topology — pgBouncer is a brilliant fit for one-Postgres deployments; PgCat is the right fit the moment you add replicas or shards. The architecture decision dwarfs config-level tuning.
• Native R/W split — PgCat parses SQL and routes by verb. pgBouncer requires app-side logic to pick a connection string per query, which becomes intractable as the number of replicas grows.
• Sharding is a one-way door — if your roadmap includes horizontal sharding, choosing pgBouncer locks you into a migration in 6–12 months. Starting on PgCat means no migration; the shard config is just a TOML edit.
• Prometheus parity — PgCat's native /metrics removes a whole sidecar process from the operational footprint. The savings in glue code accumulate over the lifetime of the deployment.
• Cost — config layout differs (ini vs toml) and tooling ecosystems differ; the migration cost between poolers is roughly 3 senior-engineer-months. Choose once, choose well.

SQL
Topic — sql
SQL read/write split and replica problems

Practice →

Optimization Topic — optimization Optimization problems on sharded routing

Practice →

---

5. Production patterns — sizing, observability, gotchas

The five production patterns every senior DBA ships — sizing formula, prep-stmt safe mode, LISTEN escape hatch, saturation monitor, recovery retry

The mental model in one line: a healthy postgres connection pool in production is the cumulative effect of the right sizing formula, a prepared-statement-safe configuration, an escape hatch for session-level features, a saturation monitor wired to the on-call pager, and graceful client-side retry with backoff on server_login_retry errors. None of these is exotic; missing any one of them is a production incident waiting to happen.

Pattern 1 — the sizing formula in full.

• The full equation. default_pool_size = ceil((app_clients × concurrency_per_client) / max_session_duration_factor). In practice: pool = (active_clients × queries_per_client) / target_utilisation.
• Plug-in defaults. Target utilisation = 0.6 to 0.7 (60–70% steady-state); concurrency per client = 1 unless the driver opens parallel queries on one connection.
• The cap from above. Postgres-side max_connections - admin_headroom. If the formula exceeds the cap, scale out (add a pgBouncer behind an LB) instead of cranking the pool size.
• The cap from below. reserve_pool_size is the burst capacity. The total default_pool_size + reserve_pool_size should equal the Postgres-side cap.

Pattern 2 — prepared-statement safe mode.

• For pgBouncer 1.21+. Set max_prepared_statements = 200 (or higher). pgBouncer transparently re-prepares statements on backend swaps.
• For older pgBouncer. Disable driver-side auto-prepare (prepareThreshold=0 for JDBC, prepare_threshold=None for psycopg, etc.). Accept the 10–15% per-query overhead.
• For PgCat. Modern versions support server-side prep-stmt tracking; check the version-specific config knob.
• Never. Use session-pool mode for the entire workload just to keep prepared statements alive; the loss of multiplexing is far worse than the prep-stmt fix.

Pattern 3 — LISTEN escape hatch.

• The principle. Any client that needs session-level Postgres features (LISTEN, advisory locks, session GUCs, session-level temp tables) goes through a separate pgBouncer/PgCat virtual database with pool_mode = session.
• Routing. Same physical Postgres, different [databases] entry, different pool mode. The application connects to a different name.
• Sizing. Session pools are 1:1 by definition; size them for the small number of clients that need them, not for the whole workload.

Pattern 4 — saturation monitor + on-call pager.

• Metric. pgbouncer_pool_maxwait_seconds > 5 for 2 minutes (pgBouncer) or pgcat_pool_maxwait_seconds > 5 for 2m (PgCat). Sustained queueing.
• Secondary metric. pgbouncer_pool_cl_waiting > 50 for 2 minutes — sustained queue depth.
• Runbook entry. (a) check SHOW STATS / Prometheus for slow query rate; (b) check Postgres pg_stat_activity for idle in transaction — leaked transactions are the most common root cause; (c) temporarily raise default_pool_size; (d) longer-term, profile the slow query.

Pattern 5 — client-side retry with backoff.

• The error to handle. FATAL: server_login_retry exceeded (pgBouncer cannot reach Postgres after retries) and the transient TCP-reset errors during pgBouncer reload.
• The driver pattern. Exponential backoff with jitter; cap the retry count; surface failures to the application layer rather than retrying forever.
• The pgBouncer side. server_login_retry = 5 (seconds) is reasonable; don't crank it higher — fast-fail beats infinite-hang.

Common interview probes on production patterns.

• "Walk me through the pool sizing formula." — required.
• "How do you detect a saturating pool before it stalls the cluster?" — SHOW POOLS + Prometheus + maxwait alert.
• "What does server_reset_query = DISCARD ALL actually reset?" — prepared statements, temp tables, listeners, GUCs.
• "How do you handle a Postgres failover behind pgBouncer?" — pgBouncer reload + a brief reconnect storm; client-side retry with backoff is mandatory.

Worked example — applying the sizing formula end-to-end

Detailed explanation. A senior DE is sizing the pool for a new analytics workload before launch. Inputs: 200 app servers, each running a driver-side pool of 50, peak concurrent users ~10k, average query latency 80ms, query rate per user 1 per 10 seconds. Run the formula, reconcile against the Postgres backend budget, choose the final pool size.

• Total client cap. 200 × 50 = 10000 client connections to pgBouncer.
• Active queries at p99. 10000 users × (0.08 s / 10 s) × 1 query × 2x peak factor = 160 active concurrent queries.
• Target utilisation. 0.65.
• Formula. default_pool_size = ceil(160 / 0.65) = 247.
• Postgres budget. max_connections = 400 - 50 admin headroom = 350 usable.
• Final. Pick default_pool_size = 240, reserve_pool_size = 60, max_client_conn = 11000. Total backend cap = 300 ≤ 350 budget.

Question. Produce the complete sized config and walk through what changes if peak users double.

Input.

Parameter	Value
App servers	200
Driver pool per server	50
Worst-case client cap	10000
Peak users at p99	10000
Query rate per user	0.1 qps
Avg query latency	80 ms
Active concurrent queries at peak	160
Postgres max_connections	400
Admin headroom	50

Code.

; pgbouncer.ini — sized via the formula
[pgbouncer]
pool_mode             = transaction
default_pool_size     = 240        ; ceil(160 active / 0.65 util) ≈ 247, rounded to 240
reserve_pool_size     = 60         ; burst capacity (240 + 60 = 300 ≤ 350 budget)
reserve_pool_timeout  = 5
max_client_conn       = 11000      ; 10000 worst case + 10% safety
max_db_connections    = 300        ; hard cap
max_user_connections  = 300        ; hard cap
max_prepared_statements = 300

; Scenario — peak users double to 20000:
;   active concurrent queries: 20000 × 0.1 × 0.08 × 2 = 320
;   default_pool_size = ceil(320 / 0.65) = 493
;   reserve_pool_size = 100
;   total = 593 > 350 budget → exceed single Postgres cap
;   → must either (a) shard the database or (b) add a read replica + PgCat for r/w split

# Quick capacity calculator
def size_pool(app_clients, qps_per_client, latency_s, peak_factor=2, util=0.65, headroom=50, max_conn=400):
    active = app_clients * qps_per_client * latency_s * peak_factor
    target = int(-(-active // util))         # ceil
    budget = max_conn - headroom
    pool   = min(target, int(budget * 0.8))
    reserve = budget - pool
    return pool, reserve, budget

# Today
print(size_pool(10000, 0.1, 0.08))   # (240, 110, 350)  → choose 240/60

# After 2x growth
print(size_pool(20000, 0.1, 0.08))   # (280, 70, 350)  → 350 cap blocks the formula → shard or add replicas

Step-by-step explanation.

1. Compute the active concurrent query count at p99: users × qps × latency × peak_factor = 10000 × 0.1 × 0.08 × 2 = 160. This is the steady-state number of backends actually executing work at the peak.
2. Apply the target utilisation: default_pool_size = ceil(active / 0.65) = 247. Round to a round number (240) and leave the remainder as reserve_pool_size.
3. Reconcile against the Postgres budget: max_connections - admin_headroom = 350. Total pool + reserve = 300 ≤ 350. The config fits.
4. The doubling scenario: 20000 users → 320 active → formula needs 493 backends. The Postgres budget is still 350. The formula exceeds the budget; further single-instance scaling is impossible. The team must either shard the data, add read replicas with PgCat r/w split, or vertically upgrade the Postgres box.
5. The Python calculator codifies the formula for repeatable sizing decisions. Plug in new numbers as the workload evolves and re-run; the output is the canonical sizing artefact.

Output.

Scenario	active	formula	budget	final pool	reserve	fits?
Today	160	247	350	240	60	yes
2x users	320	493	350	280	70	no — shard / replicate
4x users	640	985	350	280	70	no — major redesign

Rule of thumb. Apply the formula every quarter; the workload changes faster than the config. When the formula exceeds the Postgres budget, the answer is never "crank max_connections" — it's "shard, replicate, or scale the box."

Worked example — saturation alert + on-call runbook

Detailed explanation. The team ships a saturation alert that fires when maxwait > 5 seconds for 2 minutes. The on-call runbook walks the engineer from alert to root cause in five steps: check SHOW POOLS, check SHOW STATS for slow queries, check Postgres pg_stat_activity for idle-in-transaction, optionally raise the pool temporarily, file a follow-up issue. Walk through the entire flow against a synthetic saturation event.

• Trigger. maxwait > 5s for 2 minutes.
• Root cause (typical). One slow query or one leaked transaction holds a backend for minutes, starving the pool.
• Mitigation. Kill the offending backend, raise the pool temporarily, follow up to fix the query.

Question. Walk through the runbook for a synthetic incident where maxwait hits 8 seconds on the analytics/app_ro pool. Show every command and how it points to root cause.

Input.

Metric	Value
Alert	maxwait > 5s for 2m on analytics/app_ro
Time	2026-06-22 14:30 UTC
Current default_pool_size	80

Code.

-- Step 1 — confirm the pool state
-- psql -h pgbouncer -p 6432 -U admin pgbouncer
SHOW POOLS;
-- database  | user   | cl_active | cl_waiting | sv_active | sv_idle | maxwait
-- analytics | app_ro | 180       | 42         | 80        | 0       | 8

-- Step 2 — find the slowest query patterns
SHOW STATS;
-- database  | total_xact_count | total_query_count | total_query_time | total_wait_time
-- analytics | 12345678         | 12345678          | 9999999          | 9999999  ← spike

-- Step 3 — diagnose at Postgres level
-- psql -h db.internal -p 5432 -U dba analytics
SELECT pid,
       state,
       now() - query_start  AS query_age,
       now() - xact_start   AS xact_age,
       wait_event_type,
       wait_event,
       substring(query, 1, 100) AS query
FROM   pg_stat_activity
WHERE  state != 'idle'
ORDER  BY query_age DESC NULLS LAST
LIMIT  20;
-- pid | state               | query_age      | xact_age       | wait_event_type | query
-- 78  | idle in transaction | NULL           | 00:08:32       | Client          | NULL ← leak!
-- 89  | active              | 00:00:00.080   | 00:00:00.080   | (null)          | SELECT ...

-- Step 4 — kill the leaked transaction
SELECT pg_terminate_backend(78);

-- Step 5 — temporarily raise the pool while filing follow-up
-- pgbouncer admin db
RELOAD;   -- apply config edit raising default_pool_size from 80 to 100

Runbook — saturation alert "maxwait > 5s analytics/app_ro"
==========================================================

t+0s     Alert fires; PagerDuty pages on-call DBA
t+30s    On-call connects to pgBouncer admin (psql -h pgbouncer -p 6432 -U admin pgbouncer)
t+60s    Run SHOW POOLS — confirms 42 cl_waiting, maxwait 8s
t+90s    Run SHOW STATS — total_wait_time spiked in last minute
t+120s   Connect to Postgres, run pg_stat_activity query
t+150s   Identify leaked transaction (idle in transaction, xact_age > 8 min)
t+180s   pg_terminate_backend(pid)
t+200s   Verify SHOW POOLS — maxwait drops to 0
t+300s   File ticket against the team owning the offending app; root-cause analysis follow-up

Step-by-step explanation.

1. Step 1 — SHOW POOLS confirms the alert: 42 clients waiting, 80 backends in use, 0 idle, maxwait 8s. The pool is saturated.
2. Step 2 — SHOW STATS reveals which database/user pair is responsible. In this case, analytics/app_ro is the saturating pool. total_wait_time is the cumulative seconds clients have waited; a spike here is the leading indicator.
3. Step 3 — query pg_stat_activity on the Postgres side, looking for backends with state = 'idle in transaction' and a xact_age > 1 minute. These are leaked transactions — the most common cause of pool saturation in transaction-pool mode. The leaked transaction holds the backend; pgBouncer cannot reclaim it until the client COMMITs or ROLLBACKs.
4. Step 4 — pg_terminate_backend(pid) forcibly closes the backend. pgBouncer notices the disconnect, removes the entry from sv_active, and the next client in the queue gets the freed backend.
5. Step 5 — temporarily raise default_pool_size in pgbouncer.ini and RELOAD from the admin database (no restart needed). File a ticket against the team whose app leaked the transaction so the bug is fixed in code, not just papered over with more backends.

Output.

Step	Command	Time	Result
Confirm saturation	SHOW POOLS	t+60s	42 waiting, maxwait=8s
Identify pool	SHOW STATS	t+90s	analytics/app_ro
Find root cause	pg_stat_activity	t+150s	pid 78, idle-in-txn for 8 min
Mitigate	pg_terminate_backend(78)	t+180s	maxwait drops to 0
Resolve permanently	file bug ticket	t+300s	Team fixes leaked txn in code

Rule of thumb. Leaked idle in transaction is the #1 cause of pool saturation. Always check Postgres pg_stat_activity before raising the pool size. Adding backends to mask a transaction leak just delays the inevitable.

Worked example — graceful client retry on pgBouncer reload

Detailed explanation. Reloading pgBouncer (RELOAD; from the admin db) is mostly graceful — existing connections keep working — but a config change that affects auth or pool topology can cause transient TCP-level errors. Apps must implement client-side retry with exponential backoff so a one-second pgBouncer hiccup doesn't surface as a 500 to the user.

• The error class. psycopg2.OperationalError: connection has been closed, JDBC SQLException: Connection refused, or any transient TCP reset.
• The retry pattern. Exponential backoff with jitter; max 3–5 retries; total budget ~5 seconds.
• The pool-side knob. server_login_retry = 5 (seconds) controls how long pgBouncer waits before considering a Postgres backend unreachable.

Question. Implement the retry helper in Python (psycopg2) with exponential backoff + jitter and show how it integrates with a SQLAlchemy session.

Input.

Parameter	Value
Base backoff	0.1 s
Max backoff	2 s
Max attempts	5
Jitter	±25%

Code.

import random
import time
import logging
import psycopg2
from psycopg2 import OperationalError, InterfaceError

log = logging.getLogger("db.retry")

def execute_with_retry(conn_factory, query, params=None, attempts=5):
    """Run `query` against a connection from `conn_factory`, retrying transient errors.

    Retries OperationalError and InterfaceError (TCP-level / pgBouncer reload).
    Does not retry psycopg2.ProgrammingError (SQL syntax) or DataError (bad input).
    """
    for n in range(attempts):
        try:
            with conn_factory() as conn:
                with conn.cursor() as cur:
                    cur.execute(query, params)
                    if cur.description:
                        return cur.fetchall()
                    return None
        except (OperationalError, InterfaceError) as e:
            if n == attempts - 1:
                log.exception("db retry exhausted after %d attempts", attempts)
                raise
            base = min(0.1 * (2 ** n), 2.0)               # 0.1, 0.2, 0.4, 0.8, 1.6 capped at 2
            jitter = base * random.uniform(-0.25, 0.25)   # +/- 25%
            sleep_for = base + jitter
            log.warning("db transient error on attempt %d/%d, sleeping %.2fs: %s",
                        n + 1, attempts, sleep_for, e)
            time.sleep(sleep_for)

# Example use
def conn_factory():
    return psycopg2.connect("postgres://app@pgbouncer:6432/analytics")

rows = execute_with_retry(
    conn_factory,
    "SELECT id, name FROM events WHERE ts > %s ORDER BY ts DESC LIMIT 100",
    (datetime.utcnow() - timedelta(hours=1),),
)

Step-by-step explanation.

1. The helper retries only on OperationalError and InterfaceError — the transient classes that include TCP resets and connection-refused errors. SQL-level errors (ProgrammingError, DataError) are not retried because retrying a broken query is wasted work.
2. The exponential schedule is 0.1 × 2^n capped at 2 seconds. Five attempts cover roughly 0.1 + 0.2 + 0.4 + 0.8 + 1.6 = 3.1 seconds of retries — enough to absorb a pgBouncer reload, not long enough to mask a real outage.
3. The jitter (±25%) prevents the thundering-herd effect when many clients retry simultaneously after a pgBouncer reload. Without jitter, all retries would land in the same 200ms window and re-saturate the freshly-reloaded pool.
4. with conn_factory() opens a fresh connection for each attempt. This is important: a connection from the previous failed attempt may be in an inconsistent state; reuse risks a stale-transaction error.
5. The integration point is the SQLAlchemy session or the raw cursor call site. Wrapping every database call with execute_with_retry (or a SQLAlchemy event listener that does the same) makes the entire app resilient to brief pgBouncer hiccups.

Output.

Attempt	Sleep before	Outcome
1	0.0 s	Fails (TCP reset)
2	0.10 ± 25% s	Fails (still reloading)
3	0.20 ± 25% s	Succeeds (reload finished)

Rule of thumb. Every database client in a pgBouncer-fronted Postgres deployment needs exponential-backoff retry with jitter. The helper above is 30 lines of Python; not writing it is the cause of 90% of "intermittent 500s during deploys" incidents.

Senior interview question on production pooling patterns

A senior interviewer might ask: "You join a team running pgBouncer in transaction mode with default_pool_size = 100 and no monitoring. Walk me through the first week — what do you instrument, what do you measure, what do you change, and what would push you to migrate to PgCat?"

Solution Using a five-step production hardening plan

First-week hardening plan — pgBouncer in transaction mode
==========================================================

Day 1 — Observability
  - Stand up prometheus-pgbouncer-exporter sidecar
  - Scrape pgbouncer_pool_* metrics every 15s
  - Build Grafana dashboard: pool utilisation, cl_waiting, maxwait

Day 2 — Sizing audit
  - Apply the formula: default_pool_size = ceil(active × concurrency / 0.65)
  - Reconcile against Postgres max_connections - admin_headroom
  - Adjust default_pool_size; add reserve_pool_size = 0.25 × default_pool_size

Day 3 — Prep-stmt safe mode
  - Upgrade pgBouncer to 1.21+ (if older)
  - Set max_prepared_statements = 200
  - Verify with SHOW PREPARED_STATEMENTS

Day 4 — LISTEN escape hatch
  - Identify clients using LISTEN/NOTIFY, advisory locks, session GUCs
  - Create a separate [databases] entry with pool_mode = session
  - Migrate those clients to the new database name

Day 5 — Saturation alerts + runbook
  - Alert: maxwait > 5s for 2m
  - Alert: cl_waiting > 50 for 2m
  - Runbook: SHOW POOLS → SHOW STATS → pg_stat_activity → pg_terminate_backend
  - Document escalation path (DBA → backend owner)

Day 6 — Client retry
  - Audit every database client for exponential-backoff retry
  - Roll out the retry helper as a SQLAlchemy event hook
  - Smoke test by reloading pgBouncer mid-traffic

Day 7 — Decide on PgCat migration
  - If: primary + replicas, sharding roadmap, or Prometheus-native preferred → migrate
  - If: single Postgres, no roadmap → stay on pgBouncer

Step-by-step trace.

Day	Activity	Output
1	Prometheus + Grafana	Dashboard live, baseline pool metrics
2	Sizing audit	default_pool_size adjusted to formula
3	Prep-stmt safe mode	max_prepared_statements = 200
4	LISTEN escape hatch	analytics_notify db live
5	Saturation alerts	PagerDuty integration + runbook checked in
6	Client retry	Retry helper deployed in app stacks
7	PgCat decision	Roadmap-aware migration plan or stay

After day 7, the deployment has the five production patterns in place. The team can sleep at night; the on-call has a paved runbook; the next saturation event is a 5-minute incident, not a 4-hour war room.

Output:

Pattern	Before	After
Sizing formula applied	no	yes
Prep-stmt safe mode	no	yes
LISTEN escape hatch	no	yes
Saturation alerts	no	yes
Client retry	partial	full

Why this works — concept by concept:

• Five patterns, not one — production pool health is the cumulative effect of sizing + prep-stmt safety + LISTEN escape + saturation alerts + client retry. Missing any one is a future incident.
• Day-by-day rollout — sequencing the rollout (observability first, sizing second, etc.) means you measure the effect of each change before the next. Big-bang config changes are how outages happen.
• Saturation alert + runbook pair — the metric without the runbook is just noise; the runbook without the metric never gets exercised. Ship them together or neither.
• Client retry as a non-negotiable — pool reload, Postgres failover, transient network jitter — all of these surface as TCP errors. Retry with backoff is mandatory; "we'll add it later" never happens.
• Cost — five days of senior-DBA time for the rollout; the avoided cost of one 4-hour 3 AM incident pays for the entire week. O(events) per query at runtime; O(1) per quarter for re-tuning.

SQL
Topic — sql
SQL production pool-tuning problems

Practice →

Optimization
Topic — optimization
Optimization problems on pool saturation and retry

Practice →

---

Cheat sheet — connection pooling recipes

• When to use a connection pooler. Whenever you have more than ~50 client connections, run in cloud Postgres (where backend cost is your billable cost), or deploy a many-tenant analytics workload. Direct-connect-to-Postgres is appropriate only for the smallest single-app deployments.
• Pool mode default. Transaction pooling. Switch to session only for clients using LISTEN/NOTIFY, session GUCs, advisory locks, or driver-side prepared statements without pgBouncer 1.21+. Switch to statement only for high-volume single-statement workloads with no multi-statement transactions.
• pgBouncer transaction-pool config. pool_mode = transaction, default_pool_size = (active_clients × concurrency) / 0.65, reserve_pool_size = 0.25 × default_pool_size, max_client_conn = 1.1 × worst_case_clients, server_idle_timeout = 600, server_lifetime = 3600, server_reset_query = DISCARD ALL, max_prepared_statements = 200.
• PgCat sharding config. TOML format; one [pools.<name>] per logical database; multiple [pools.<name>.shards.<N>] for horizontal sharding; per-shard primary + replicas with role = "primary" | "replica"; query_router_enabled = true for auto read/write split; shard_id_regex for comment-based shard extraction.
• Pool sizing formula. default_pool_size = ceil((active_clients × queries_per_client) / target_utilisation), target 0.6–0.7, cap by max_connections - admin_headroom. Add reserve_pool_size = 0.25 × default_pool_size for burst. If the formula exceeds the Postgres budget, scale out (sharding or replicas) — never crank max_connections.
• Postgres max_connections ceiling. Keep at 200–500. Beyond 500, ProcArray scans and per-backend RAM dominate. The pooler absorbs client-side fan-out; max_connections is the supply side, not the demand side.
• Prep-stmt safe mode. pgBouncer 1.21+: max_prepared_statements = 200. PgCat: enable server-side prep-stmt tracking. Older pgBouncer: prepareThreshold=0 on the driver. Never switch the whole workload to session pooling just to keep prep stmts alive.
• LISTEN escape hatch. Separate [databases] entry with pool_mode = session. Different virtual database name, same physical Postgres. Workers connect to the session pool; analytics traffic uses the transaction pool. Size the session pool for the small worker count, not the whole workload.
• Auth_user indirect lookup. auth_type = scram-sha-256, auth_user = pgbouncer, auth_query = SELECT usename, passwd FROM public.pgbouncer_get_auth($1). userlist.txt has only the pgbouncer role; every other user's password is fetched dynamically from Postgres. Rotation = ALTER USER only.
• Saturation monitor query. SHOW POOLS exported as Prometheus gauges; alert on maxwait > 5s for 2m or cl_waiting > 50 for 2m. Runbook: SHOW POOLS → SHOW STATS → pg_stat_activity (find idle in transaction) → pg_terminate_backend(pid) → file bug.
• Client retry with backoff. Exponential backoff 0.1 × 2^n capped at 2 seconds, max 5 attempts, ±25% jitter. Retry only on OperationalError / InterfaceError — never on SQL-level errors. Open a fresh connection on each retry; never reuse a connection from a failed attempt.
• pgBouncer reload, not restart. RELOAD; from the admin database applies config changes without dropping existing connections. Use restart only for max_client_conn and unix_socket_dir changes that the reload path cannot apply.
• Two-instance HA topology. pgBouncer behind L4 load balancer (HAProxy or AWS NLB); each instance has its own pool; Postgres-side max_connections must accommodate the sum. Scale out when single-instance event loop saturates (~10000 qps), not before.
• PgCat vs pgBouncer. PgCat for primary + replicas, sharding roadmap, Prometheus-native, modern team. pgBouncer for single-Postgres, mature ecosystem, no replicas, smallest deployments. Migration cost between them is ~3 senior-engineer-months; choose once.

Frequently asked questions

pgBouncer vs PgCat vs Pgpool-II — which connection pooler should I use in 2026?

pgBouncer is the mature, battle-tested default — a single-process C implementation that handles transaction/session/statement pool modes against a single Postgres instance. Use it for one-Postgres deployments, mature operational teams, and workloads where the read/write or sharding story is not on the roadmap. PgCat is the Rust-native modern alternative — multi-shard, read-replica-aware, native Prometheus metrics. Pick it when you have a primary + read replicas, a sharding roadmap, or already run Prometheus + Grafana. Pgpool-II is the legacy "kitchen sink" that bundles pooling, load balancing, query routing, and a watchdog HA layer; the trade-off is operational complexity that most modern stacks avoid by composing pgBouncer (or PgCat) with a separate HA tool (Patroni / Stolon). The 2026 default for a new analytics deployment is pgBouncer if you're single-Postgres and PgCat if you're multi-replica or multi-shard.

Transaction pooling vs session pooling — when do I pick each?

Default to transaction pooling for analytics workloads — it gives 50:1 to 200:1 multiplexing and works for any client that uses SET LOCAL, transaction-scoped temp tables, and transaction-level advisory locks (pg_advisory_xact_lock). Switch to session pooling only for clients that need session-scoped Postgres features: LISTEN/NOTIFY, session-level advisory locks (pg_advisory_lock), session GUCs that aren't SET LOCAL, session-level temp tables, or driver-side prepared statements on pgBouncer versions older than 1.21. The right architecture is usually a split: most clients on a transaction-mode [databases] entry, a small subset on a session-mode entry pointing at the same physical Postgres. Never default to session pooling for the whole workload — you give up the multiplexing benefit that motivated the pooler in the first place.

Why do my prepared statements fail with ERROR: prepared statement "S_3" does not exist in pgBouncer?

The error is the session-scope mismatch between Postgres prepared statements and transaction pooling connection leases. Postgres prepares the statement on the specific backend that ran PREPARE; the named statement (S_1, S_2, ...) lives only in that backend's memory. Under transaction pooling, the next EXECUTE may land on a different backend that has never seen the name. The fix on modern pgBouncer (1.21+): set max_prepared_statements = 200 — pgBouncer intercepts PREPARE/EXECUTE/DEALLOCATE and transparently re-prepares the statement on whichever backend gets leased. The fix on older pgBouncer: disable driver-side auto-prepare (prepareThreshold = 0 for JDBC, prepare_threshold = None for psycopg). The wrong fix: switching the entire workload to session pooling — you lose the multiplexing ratio and pay 10–100× more Postgres backends.

How big should my connection pool sizing be — what's the formula?

The textbook formula: default_pool_size = ceil((active_clients × queries_per_client) / target_utilisation). Plug in target_utilisation = 0.65 (60–70% steady-state), and reconcile against postgres max_connections - admin_headroom (typically 50). If the formula exceeds the Postgres budget, the answer is never "raise max_connections" — it's "shard, replicate, or vertically scale Postgres." Add reserve_pool_size = 0.25 × default_pool_size to absorb transient spikes without sizing the steady-state pool for the worst case. Re-run the formula every quarter as the workload evolves. The most common mistake is setting default_pool_size = max_connections, which leaves no room for admin connections, autovacuum workers, or replication slots — and saturates the box on any single misbehaving client.

How do I monitor pgBouncer saturation in production?

Three layers. Pool layer: scrape SHOW POOLS every 15 seconds and export cl_active, cl_waiting, sv_active, sv_idle, and maxwait as Prometheus gauges (prometheus-pgbouncer-exporter for older pgBouncer; PgCat exposes /metrics natively). Alert on maxwait > 5s for 2m and cl_waiting > 50 for 2m. Stats layer: scrape SHOW STATS for total_wait_time, total_query_time, and total_xact_time deltas — spikes here are the leading indicator of pool stress. Postgres layer: scrape pg_stat_activity for idle in transaction rows where xact_age > 1 minute — leaked transactions are the single biggest cause of pool saturation. The runbook on alert: SHOW POOLS → SHOW STATS → pg_stat_activity → pg_terminate_backend(pid) if leaked → file bug ticket. Never just raise default_pool_size; you're masking the root cause.

Can the application driver pool instead of running pgBouncer?

For single-app deployments, sometimes — modern drivers (HikariCP, psycopg-pool, asyncpg pool) implement competent client-side pools. But the driver pool is per-process. Run 10 app servers with 50 connections each and you have 500 connections to Postgres before any application logic runs. The pgBouncer (or PgCat) layer collapses N × M client pools onto a single M backend pool — the multiplexing ratio only exists at the pooler layer. For any deployment with more than one app server, or more than ~50 total clients, or any plan to scale horizontally, you need a server-side pooler. The driver pool stays — it manages per-process connection lifecycle to the pooler — but the pooler in front is what makes the math work.

Practice on PipeCode

• Drill the SQL practice library → for the connection-saturation, transaction-leak, and pool-tuning problems senior interviewers love.
• Rehearse on the ETL practice library → for the throughput-sizing and Postgres-driven pipelines that motivate the pooler in the first place.
• Sharpen the tuning axis with the optimization practice library → for the saturation-monitor, sizing-formula, and prep-stmt-gotcha problems.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the pool-mode + sizing intuition against real graded inputs.

Lock in connection-pool muscle memory

Pooler docs explain modes. PipeCode drills explain the decision — when transaction pooling breaks prepared statements, when PgCat replaces pgBouncer, when the pool-sizing formula actually applies. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice optimization problems →