Connection Pooling at Scale

Learning outcomes

This page opens the scaling track because connection pooling is the first wall every busy PostgreSQL hits. Long before you shard, replicate, or partition, you discover that PostgreSQL refuses to scale the one thing every application multiplies first: client connections. A fleet of forty app pods, each holding a pool of fifty connections, wants two thousand backends. PostgreSQL wants a few dozen active. The pooler is what bridges that gap, and getting it right is the difference between a server that hums at three thousand transactions per second and one that collapses at three hundred.

After studying this page, you can:

• Explain why a PostgreSQL connection is a heavy operating-system process and why the active-connection ceiling lives in the hundreds, not the thousands.
• Choose between session, transaction, and statement pooling for a given workload, and list the session-level features each one breaks.
• Size PgBouncer for a multi-pod application and tune the timeouts that keep a leaked transaction from poisoning the database.
• Write application code (session state, prepared statements, advisory locks, LISTEN/NOTIFY) that survives transaction-pool multiplexing.
• Diagnose a connection-pool incident from pg_stat_activity and the PgBouncer admin console.

Before we dive in

You should be comfortable with the idea that a transaction is a unit of work between BEGIN and COMMIT, and that PostgreSQL gives each session its own settings, prepared statements, and temp tables. You do not need replication or sharding yet; we cover them in their own pages.

A few words used heavily below. A backend is one PostgreSQL server process that handles one client connection at a time. A pool is a finite set of those backends a pooler holds open and hands out. Multiplexing is the trick of serving many client connections with fewer backends by handing one out only for the duration of a unit of work. A session-level feature is anything PostgreSQL keeps on the backend between statements: SET values, server-side prepared statements, LISTEN channels, advisory session locks, and temp tables. Hold those four. The whole design question of “which pool mode” is really “how much session-level state can my code tolerate losing.”

Mental Model

The wrong model, and it is the model behind most connection-pool incidents, is that a connection is cheap, the way a thread or a goroutine is cheap. Under that model you set max_connections = 5000, give every app pod a fat client pool, and assume the database will sort it out. It will not.

The better model is that a PostgreSQL connection is a small Unix server. Each one is a forked operating-system process with its own memory, its own planner caches, its own prepared-statement state, and its own slot in the shared lock manager and proc array. Two thousand of them is two thousand processes the kernel must schedule, two thousand entries every snapshot must scan, twenty gigabytes of resident memory before a single query runs. Long before you “run out of connections,” you run out of CPU spent context-switching between processes that are mostly idle.

Hold that picture. A connection is expensive at the operating-system level, so the throughput ceiling is well below thousands of connections, often at a few hundred actively running queries. A pooler exists to let the application keep its cheap client-side connections cheap (one per request handler, opened freely) while keeping the expensive server-side backends scarce and busy.

Breaking it down

1. Why a PostgreSQL connection is expensive

Start with the cost of one connection, because every later number is a multiple of it. When a client connects, the PostgreSQL postmaster forks a brand-new backend process. That process is not a lightweight thread; it is a full Unix process with its own address space. As of PG 16 and 17 a freshly idle backend takes on the order of ten megabytes of resident memory before you query anything, and that footprint grows as you build up planner state, per-relation caches, and prepared statements.

That alone would only bound you on memory. The harder ceiling is shared. Every backend takes a slot in the PostgreSQL proc array, which every snapshot must scan to decide what is visible. Every backend can hold lock-manager entries that other backends must traverse. Every backend the kernel must context-switch into when its turn comes. At a few thousand backends, more time goes into shuffling processes than into running queries, and throughput collapses even though no single backend is doing much work.

This is why the community wiki tuning page suggests starting from ((cores * 2) + effective_spindle_count) as a target for the number of actively running connections, not as max_connections. On a 32-core box with SSDs (effective spindle count usually 1 or higher), that is a starting point around 64 to 100 active connections. The real ceiling is workload-dependent and you measure it, but the order of magnitude is hundreds, not thousands.

flowchart LR
    A[Client connects] --> B[Postmaster forks backend process]
    B --> C[New OS process, about 10 MB resident]
    C --> D[Slot in proc array and lock manager]
    D --> E[Planner caches, prepared statements grow per session]
    E --> F[Even idle, this backend costs CPU and memory]

Read that chain twice. Forward, it says every new connection is a process, not a thread, and it pays a fixed tax before it does any work. Backward, it explains the symptom every senior PostgreSQL operator has seen: a max_connections bump from 200 to 2000 makes the database slower, not faster, because the proc array and the lock-manager scans dominate.

The bands are workload-shaped, not magic numbers; tune to your own CPU count and IO profile. But the shape is the lesson: throughput rises, plateaus near the wiki rule of thumb, and then falls. The pooler exists to keep you on the left side of that hill.

2. The shape of the problem: thousands of clients, dozens of backends

Now you have the cost on the server side. Look at the client side. A modern application has many pods, each with its own connection pool inside the language runtime (HikariCP for the JVM, pgx or database/sql in Go, psycopg in Python, node-postgres in Node). Each pod opens, say, fifty connections so request handlers do not wait. Forty pods give you two thousand client connections demanded.

The shapes do not match. The application wants thousands. The database wants dozens. You have two choices: tell every team to use a tiny client pool (which makes their request latency worse and their code more complex) or put a multiplexer in the middle that holds a small server-side pool and hands its server connections out to client connections only for the duration of one transaction or one statement. The multiplexer is the pooler.

flowchart LR
    subgraph Clients
      C1[app pod 1, 50 client conns]
      C2[app pod 2, 50 client conns]
      C3[app pod N, 50 client conns]
    end
    Clients --> P[PgBouncer: max_client_conn=5000, default_pool_size=50]
    P --> S[PostgreSQL: a few dozen busy backends]
    S --> D["Shared buffers, WAL, disk"]

That single arrow from PgBouncer to PostgreSQL is where the magic happens: the same fifty server backends serve the five thousand client sessions, because at any instant only a fraction of the clients are actually inside a transaction. The pooler keeps the cheap side cheap and the expensive side scarce.

3. PgBouncer pool modes and what you trade

PgBouncer is the dominant pooler in the PostgreSQL ecosystem in 2026: small, written in C, single-threaded with an event loop, extremely fast, deployed everywhere from a sidecar on a laptop to AWS RDS Proxy under the hood. The single most important configuration choice in PgBouncer is pool_mode.

There are three modes. The mode controls when a server connection is returned to the pool, which in turn controls what session-level state can survive across that boundary.

Session mode is the safest: it behaves exactly like a direct connection because the same backend is yours from the moment you connect until you disconnect. The cost is that it does not multiplex. If you have two thousand client connections, you need two thousand server connections. You use session mode when you genuinely need session-level state (a long-running consumer that uses LISTEN, an admin tool with temp tables) or when you just need a connection limiter, not a multiplexer.

Transaction mode is what almost every web application uses, and it is the mode the rest of this page assumes by default. A server connection is yours from BEGIN (or the implicit one around an autocommit statement) until COMMIT or ROLLBACK. The instant your transaction ends, that server is in the pool and the next client gets it, possibly mid-script. Anything you set with plain SET is gone. Anything you LISTENed for is gone. Any pg_advisory_lock you took is gone the next time you commit. This is the trade that lets fifty server backends serve five thousand clients, and it is the trade your application code must respect.

Statement mode releases the server after every statement, which means you cannot run multi-statement transactions at all. It exists for very narrow use cases (read-only fan-outs through a load balancer) and is rarely the right choice. If you reach for it, you almost always wanted transaction mode with shorter transactions.

The mental rule: pick the most aggressive mode whose forbidden features your application does not need, and the next subsection covers the biggest change in that rule in a decade.

4. Prepared statements in transaction mode, finally

For most of PgBouncer’s life the rule was bleak: in transaction mode, server-side prepared statements did not work. Your driver would PREPARE a statement on one backend, the transaction would commit, the backend would go back to the pool, and the next time your driver tried to EXECUTE the named statement it would land on a different backend that had never heard of it. Drivers worked around this by disabling server-side prepared statements entirely, which means every query was re-parsed and re-planned on every execution. That is a real cost: parsing and planning is a meaningful fraction of total query time for short OLTP queries.

PgBouncer 1.21 (2023) changed this with protocol-level prepared-statement support. When enabled, PgBouncer itself tracks which prepared statements your client has created, and transparently re-prepares them on whatever backend it routes you to next. To your driver, the prepared statement is always there; behind the scenes, PgBouncer is keeping the backends in sync. This requires the extended query protocol (what libpq’s PQprepare and most modern drivers use), not the obsolete textual PREPARE SQL statement.

You enable it by setting max_prepared_statements to a non-zero value:

; pgbouncer.ini
[pgbouncer]
pool_mode = transaction
max_prepared_statements = 200

The value is the per-connection cap on tracked statements. A typical web app has a few dozen distinct queries, so 100 to 200 is fine; raising it costs PgBouncer some memory per pooled server. Once this is on, drivers that previously disabled prepared statements behind PgBouncer (pgx in Go, JDBC, asyncpg in Python) can turn them back on and reclaim the parse-and-plan tax. This is the single biggest reason to be on a current PgBouncer in 2026.

There is one footgun. max_prepared_statements works with protocol-level prepared statements only. A SQL-level PREPARE foo AS ... followed by EXECUTE foo still breaks in transaction mode, because PgBouncer cannot see the text-level state on the backend. The fix is the same as it has always been: use your driver’s prepared-statement API, not the SQL PREPARE keyword.

5. Sizing the pool and the surrounding knobs

A PgBouncer config has half a dozen knobs that matter; the rest are leave-them-alone. Here is a minimal but production-shaped configuration:

; pgbouncer.ini
[databases]
appdb = host=primary.internal port=5432 dbname=appdb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 5000
default_pool_size = 50
reserve_pool_size = 10
reserve_pool_timeout = 5

server_idle_timeout = 600
server_lifetime = 3600
server_reset_query = DISCARD ALL
server_check_query = select 1
server_login_retry = 15

max_prepared_statements = 200

Read it knob by knob, because each one corresponds to a failure mode.

max_client_conn is the cap on client connections PgBouncer will accept at once. It can be very large (five thousand here) because each client connection in PgBouncer is a small kernel socket and a few KB of bookkeeping; nothing like a PostgreSQL backend. This is the number your app pods see.

default_pool_size is the cap on server connections PgBouncer will open per database, per user. This is the small number that protects PostgreSQL. The product of default_pool_size and the number of (database, user) pairs is the maximum backends PgBouncer can ever open, and it must stay well below the PostgreSQL max_connections (the next section covers that). Size it from your active-connection target, not your client count: if you measured that 64 active backends are optimal and you have one app database, default_pool_size = 64 is reasonable.

reserve_pool_size and reserve_pool_timeout are the shock absorber. When the pool is fully used and a client has waited longer than reserve_pool_timeout seconds, PgBouncer opens up to reserve_pool_size extra backends to relieve the spike. Without reserve, a brief queue at the pool turns into client timeouts. Set the reserve large enough to absorb a normal spike (10 to 20 percent of the pool is a reasonable start) and the timeout short enough that real spikes engage it quickly.

server_idle_timeout closes idle backends after the given seconds, returning them to PostgreSQL. server_lifetime recycles a backend after this many seconds regardless of idle, which protects you from per-backend memory leaks in long-lived processes (planner caches, JIT artifacts) and gives DNS or auth changes a chance to take effect.

server_reset_query is the SQL PgBouncer runs to scrub a server before reusing it for a new client. In transaction mode the default is empty (transactions should leave no state to scrub); in session mode you typically want DISCARD ALL so prepared statements, temp tables, and session settings do not leak between clients.

server_check_query (default SELECT 1) is the liveness probe before handing a server to a new client. If PgBouncer cannot run it within the configured timeout, the server is dropped and a new one is opened. server_login_retry is how long PgBouncer waits before retrying after a failed login (a common PostgreSQL restart smoother).

6. The PostgreSQL-side ceiling and the timeouts that save you

The pooler is half the story. PostgreSQL itself has knobs that must agree with PgBouncer or you get bewildering behavior.

max_connections is the hard ceiling on backends. It is allocated at startup (lots of shared memory is sized from it), so it cannot be raised without a restart. The whole point of the pooler is that this number is small: hundreds, not thousands. A typical pattern is max_connections = 300 on a busy primary, with the sum of all PgBouncer default_pool_size + reserve_pool_size values across all (database, user) pairs sized to fit comfortably below it (leaving room for replication slots, autovacuum workers, and superuser sessions).

statement_timeout caps how long a single statement can run. Set it per workload tier: an OLTP database might have statement_timeout = '30s' as a global default, with reporting roles raising it to several minutes via ALTER ROLE reporting SET statement_timeout = '10min';. A query that exceeds the timeout is cancelled, which frees the backend back to PgBouncer.

idle_in_transaction_session_timeout is the one parameter most likely to save your career. It cancels (and disconnects) a transaction that stays open without doing work for too long. The reason it matters is in the MVCC and table-bloat-and-hot-updates pages: a single open transaction holds the xmin horizon back, which stops vacuum from cleaning dead tuples across the entire database. A leaked transaction at 3 AM turns into a database-wide bloat incident by morning. Setting idle_in_transaction_session_timeout = '60s' (or even tighter, like 10 to 30 seconds for a strict OLTP service) ensures no leaked transaction can ever do that damage.

idle_session_timeout (added in PostgreSQL 14) similarly kills a connection that is idle outside a transaction for too long. It is mostly redundant when PgBouncer is in front (PgBouncer’s server_idle_timeout already cleans up), but it is a useful belt-and-braces setting on databases that accept direct connections too.

; postgresql.conf, OLTP defaults
max_connections = 300
statement_timeout = '30s'
idle_in_transaction_session_timeout = '60s'
idle_session_timeout = '10min'

Read those four together. max_connections caps the number of backends and must agree with the pooler. statement_timeout caps a runaway query. idle_in_transaction_session_timeout caps a leaked transaction. idle_session_timeout caps a dead connection. With all four set, the database has a hard ceiling on how badly any one client can hurt it.

7. Writing application code that survives transaction pooling

Transaction-pool mode is fast and dense, but it makes a promise to your code: the only state you can rely on across statements is what is inside the transaction. Several common patterns must change.

First, SET becomes SET LOCAL. A SET statement_timeout = '5s' at the top of a request handler will silently leak to the next client of that backend. SET LOCAL is scoped to the current transaction and disappears at commit, which is the only correct behavior under transaction pooling:

begin;
set local statement_timeout = '5s';
set local search_path to tenant_42, public;
-- ... queries scoped to this transaction ...
commit;

Second, server-side prepared statements only work as discussed in section 4: protocol-level (via your driver’s prepared-statement API), not the SQL PREPARE keyword, and only with max_prepared_statements > 0.

Third, LISTEN and NOTIFY are session-level: a LISTEN orders registers interest on the current backend, not on the database as a whole. In transaction mode you might be on a different backend a millisecond later. The fix is to put a long-lived LISTEN consumer on a separate connection pointed at a session-mode PgBouncer (or directly at PostgreSQL), and use transaction-mode for the rest of the app. A common deployment runs PgBouncer twice on the same host, on different ports, with different pool_mode settings.

Fourth, advisory locks: pg_advisory_lock (session-scoped) is broken by transaction pooling because the session ends at commit. Use pg_advisory_xact_lock instead, which is automatically released at commit or rollback. The transaction-scoped advisory lock fits transaction pooling perfectly.

Fifth, temp tables: a CREATE TEMP TABLE lives on the backend that created it. After commit the backend returns to the pool, and your next statement may land elsewhere and see no temp table. Either keep temp-table use inside a single transaction, or move the workload to session mode.

The rule of thumb: in transaction pooling, treat the connection as a temporary loan that ends at every commit. If a feature requires state that outlives a transaction, you need a session-mode pool for it.

8. Layered deployment and managed alternatives

In real deployments PgBouncer rarely runs in one place. Two layers are common, and both serve a real purpose.

App-local PgBouncer. A PgBouncer process runs on each application host (as a sidecar or daemonset). It accepts loopback connections from the app processes, so the app pays no TCP cost for “connect to the database.” It funnels those into a small pool of long-haul connections to the next tier. This is the layer that absorbs spikes from a single host without hammering the central database with new TCP handshakes.

Regional or cluster PgBouncer. A pool of PgBouncer instances sits in front of the PostgreSQL primary (and read replicas), behind a load balancer. This layer aggregates many app hosts, applies the global default_pool_size against PostgreSQL max_connections, and is where you do auth and TLS termination. If it goes down, app-local PgBouncer instances can be configured to retry briefly, but a regional PgBouncer outage is a database outage in practice.

flowchart LR
    App[App process] -->|loopback| Local[App-local PgBouncer]
    Local -->|few long-haul conns| Regional[Regional PgBouncer pool]
    Regional --> PG["PostgreSQL primary"]
    Regional --> RR["Read replicas"]

There is one classic mistake here: stacking PgBouncer in front of PgBouncer with the same pool_mode = transaction. It works, but every transaction now traverses two event loops and two TCP hops, which roughly doubles the latency floor for short queries. Reserve the multi-layer pattern for when the per-host concentration genuinely earns its keep.

Managed alternatives. Two cloud offerings are worth knowing in 2026. AWS RDS Proxy is a managed pooler in front of RDS for PostgreSQL: it speaks the PostgreSQL wire protocol, supports transaction pooling, and integrates with IAM auth and secrets rotation. The trade is less control over knobs and a per-connection price. Google Cloud’s AlloyDB for PostgreSQL ships with a built-in pooler and is configured at the cluster level; it covers the same ground without a separate process to manage. Both are reasonable choices when you want one less moving piece, and both make the same multiplexing trades described above.

pgcat is the notable newer entrant: a Rust-based pooler from Instacart, designed for cloud workloads, with built-in sharding, replica routing, and load balancing. It is wire-compatible with PgBouncer in the senses that matter (clients see a PostgreSQL endpoint and the same pool-mode semantics), and it is a viable alternative when you want pool-level routing without bolting a separate router on top. Adoption is growing in 2026 but PgBouncer remains the default.

9. Diagnostics: what to look at when it hurts

When a pooled deployment misbehaves, you have two views: the PostgreSQL side and the PgBouncer side. Use both.

On the PostgreSQL side, pg_stat_activity is your map. The state column tells you what each backend is doing: active (running a query), idle (between transactions, waiting for the next statement), idle in transaction (in a transaction, waiting for the next statement), idle in transaction (aborted) (in a rolled-back transaction, still open). A healthy busy database has many active and few idle in transaction. A sick one has the reverse.

-- The state distribution: who is doing what right now?
select state, count(*)
from pg_stat_activity
where datname = 'appdb'
group by state
order by count(*) desc;

-- Who is blocking, and what are they waiting on?
select pid, state, wait_event_type, wait_event, xact_start, query
from pg_stat_activity
where datname = 'appdb' and state <> 'idle'
order by xact_start;

The wait_event_type and wait_event columns tell you what a non-idle backend is waiting for: Lock (waiting on another transaction), LWLock (internal latch contention, sometimes a sign of too many backends), Client (waiting on the network), IO (waiting on disk or WAL). A pile-up on LWLock-style waits when you raise max_connections is exactly the contention the rung-1 hill predicted.

On the PgBouncer side, you connect to the admin database, which is named pgbouncer and exposed on the same listening port:

psql -h /var/run/postgresql -p 6432 -U pgbouncer_admin pgbouncer

Once in, the SHOW commands answer the operational questions:

• SHOW POOLS; lists each (database, user) pool: how many client connections are cl_active (running a query), cl_waiting (waiting for a server), how many server connections are sv_active (handed to a client), sv_idle (in the pool, ready), sv_used (held briefly but free), and maxwait (the longest a client has been waiting). A non-zero cl_waiting or a growing maxwait says the pool is too small for the load.
• SHOW STATS; is the throughput view: transactions, queries, bytes, and average times per database. The averages are the right thing to alert on; spikes here usually predate the corresponding PostgreSQL incident.
• SHOW CLIENTS; and SHOW SERVERS; list every connection on each side. They are how you find one client holding a transaction open for an hour.

Together those views answer the two questions a paged-out engineer has: is PgBouncer the bottleneck (queue building up in SHOW POOLS), or is PostgreSQL the bottleneck (queue empty in PgBouncer, but pg_stat_activity full of active or Lock waits)?

10. Failure modes you will meet in production

These are the patterns that have caused real incidents, in the order you are likely to hit them.

max_connections = 5000, no pooler. The classic mistake. CPU collapses under modest load because the proc array and lock manager dominate. The fix is not more hardware; it is fewer backends, which means a pooler. Set max_connections back to a few hundred, put PgBouncer in front, and the same hardware suddenly handles five times the load.

Transaction pool with server-side PREPARE (pre-1.21). Driver issues PREPARE order_lookup AS ... on one transaction, executes it, commits. Next transaction lands on a different backend, the EXECUTE order_lookup fails with “prepared statement does not exist.” The application looks broken at random. Upgrade PgBouncer to at least 1.21 and turn on max_prepared_statements, or have the driver use unnamed prepared statements only.

Leaked transaction, no idle_in_transaction_session_timeout. An application bug commits sometimes and not others; one connection sits in idle in transaction for hours. With the timeout set, PostgreSQL kills it after a minute and the bug surfaces immediately. Without it, the xmin horizon freezes and dead tuples pile up across the whole database. By the time pagers fire, vacuum is behind by a million tuples and queries are slow. Set the timeout. Always.

PgBouncer in front of PgBouncer with the same pool_mode. Two event loops, two TCP hops, doubled latency floor on short queries. Either collapse to one layer or differentiate the layers (loopback app-local + regional aggregation, not two layers doing the same job).

Reserve pool too small for the spike. Default default_pool_size = 50, reserve_pool_size = 0, reserve_pool_timeout = 30. A burst of two hundred clients lands; fifty get served, the rest wait, and after thirty seconds the reserve has not engaged because there is no reserve, so they all time out. The fix is a real reserve (10 to 20 percent of pool) with a short timeout (a few seconds).

Session-mode features in a transaction-mode pool. A LISTEN registered on connect, an advisory session lock taken at the start of a batch job, a temp table assumed to outlive a commit. Each one works under direct connection, breaks subtly under transaction pooling, and is hell to debug because the failure is non-deterministic. The fix is the application-side patterns in section 7, or a separate session-mode pool for the features that need session state.

Mastery Questions

1. Your team raises max_connections from 200 to 2000 to handle a Black Friday spike, and throughput drops by 40 percent at peak. The DBA on call has done this before and is not surprised. Why does adding capacity hurt, and what is the correct fix at the architecture level?

   Answer. Each PostgreSQL connection is a forked operating-system process, not a thread. Two thousand backends mean two thousand processes the kernel must schedule, two thousand entries every snapshot scan touches, two thousand slots in the proc array and the lock manager, and on the order of 20 GB of resident memory before a single query runs. Long before any backend exhausts shared memory, context-switching and lock-manager and proc-array contention dominate, and total throughput falls. The community wiki tuning rule of thumb, ((cores * 2) + effective_spindle_count), points at the right order of magnitude: dozens to low hundreds of actively running connections on commodity hardware, not thousands. The correct fix is not more max_connections; it is a pooler. Put PgBouncer in transaction mode in front of the database, set default_pool_size near the active-connection target, set max_client_conn high (the cheap side), and lower max_connections back to a few hundred. The application keeps its big client pool, the database keeps its small server pool, and the same hardware suddenly handles the spike because the backends are busy instead of waiting on the kernel.

2. A Go service uses pgx with server-side prepared statements behind PgBouncer in transaction mode. After a recent rollout, you start seeing intermittent “prepared statement does not exist” errors at random rates, and pg_stat_statements shows the same query texts appearing as thousands of distinct entries with one execution each. What is going on, and what are the two fixes (one for the symptom, one for the underlying performance loss)?

   Answer. The symptom is the classic transaction-mode-plus-server-side-prepared-statements failure. The driver calls PREPARE order_lookup AS ... on one backend, runs an EXECUTE, commits. The transaction-mode pooler returns the backend to the pool, the next request gets a different backend that has never seen order_lookup, and the EXECUTE fails. The first fix, treating the symptom, is to have pgx disable server-side prepared statements (statement_cache_capacity = 0 style) so every query uses the unnamed protocol and works on any backend. That removes the errors but loses the parse-and-plan tax, which is what the pg_stat_statements explosion shows: every execution is a distinct parse, so each shows as its own entry. The second fix, addressing the underlying cost, is to upgrade PgBouncer to at least 1.21 and set max_prepared_statements to a non-zero value (100 to 200 is typical). With protocol-level prepared statements on, PgBouncer transparently re-prepares the statement on whatever backend it routes you to next; the driver can turn prepared statements back on; and the parse cost disappears. The duplicate pg_stat_statements entries collapse to one entry per logical query, and latency drops.

3. Overnight, the database-wide on-disk size grows by 50 GB even though application write volume was flat. pg_stat_activity shows one connection in idle in transaction with an xact_start from 8 PM the previous evening. Walk through the connection-pool and timeout settings that would have prevented this, and explain why each one matters.

   Answer. The diagnosis is the long-transaction trap covered in the MVCC and tuple-visibility page: a single open transaction holds the xmin horizon back, so vacuum cannot remove any dead tuple newer than the moment that transaction started. With normal write volume, that means a night’s worth of dead UPDATE and DELETE versions cannot be cleaned anywhere in the database, and on-disk size grows even on tables the offending transaction never touched. Several layers should have caught it. First and most importantly, idle_in_transaction_session_timeout = '60s' on the PostgreSQL side would have cancelled and disconnected the leaked transaction within a minute of the application bug, before any meaningful bloat accumulated; this is the single most valuable setting on a busy OLTP database. Second, statement_timeout is irrelevant here (the connection is between statements, not in one) but pairs naturally with the idle-in-transaction timeout in a defense-in-depth configuration. Third, the pooler should have been in transaction mode in the first place: in transaction mode the application’s notion of “a connection” is a sequence of short transactions, so a leaked transaction stands out immediately in SHOW POOLS and in pg_stat_activity’s idle in transaction count, where alerting can catch it before it does damage. Finally, server_lifetime in PgBouncer (3600 seconds by default) does not help with a stuck transaction (it only recycles idle servers), but it explains why even healthy backends should not be considered immortal: long-lived backends accumulate per-backend state that a periodic recycle keeps bounded. The fix in the incident is to kill the offending connection and let autovacuum catch up; the durable fix is to set the timeout and add an alert on backends whose xact_start is older than a threshold.