Acts 4–6 in depth: MVCC bloat, connection pooling, and safe migrations

A nightly report runner holds one transaction open for four hours. It reads a snapshot — no writes, nothing harmful. But VACUUM cannot reclaim dead tuples below the xmin horizon. The table swells from 200 MB to 80 GB. No new rows were inserted. The report runner is the only culprit.

Act 4 — MVCC and the xmin horizon

Every row in Postgres carries xmin (creation transaction id) and xmax (deletion transaction id). A DELETE does not free the row — it sets xmax. VACUUM reclaims rows whose xmax is older than the global xmin horizon — the oldest active transaction ID visible across the cluster.

If one analyst session leaves a transaction open for hours, the horizon does not advance. No dead tuple created after that horizon can be reclaimed. The table swells; queries that scan the table get slower because they read dead rows alongside live ones.

The fix is operational:

1. Kill the long transaction (SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND query_start < now() - interval '5 minutes').
2. Set idle_in_transaction_session_timeout = 60s — any session that holds a transaction open without executing a query for 60 seconds is terminated.
3. Run pg_repack to reclaim disk without an AccessExclusiveLock (VACUUM FULL takes one).
4. Permanent fix: move reporting to a read replica; the xmin horizon on a replica does not block vacuum on the primary when hot_standby_feedback = off.

At 1B rows, one idle session can bloat the table by 10% in a single day. Bloat slows every subsequent query because the heap pages contain dead tuples alongside live ones, forcing the sequentialscanner to read and skip them.

Act 5 — Connection pool sizing math

Each Postgres backend is a forked OS process with its own memory footprint (~5–10 MB RSS). A server with 32 GB RAM can support roughly 2000–3000 backends before the kernel scheduler thrashes and query latency degrades.

At 50 app pods with 20 workers each, a cold-start or pod-rollout event opens 1000 backends simultaneously. Queries that previously took 4 ms take 4 s not because of data volume, but because the OS scheduler cannot efficiently time-slice 1000 processes.

PgBouncer in transaction-mode:

• 100 server-side backends (fits in kernel scheduler’s sweet spot).
• 10000 client connections multiplexed onto those 100 backends.
• Each backend is used only during active transaction time, then returned to the pool.

Pool sizing math:

pool_size = active_concurrent_transactions × safety_factor

Not max_app_workers. The relevant number is how many transactions hit the database simultaneously, not the total worker count. For a 16-core SSD-only server, 30–60 server-side backends is typically the sweet spot.

The transaction-mode prepared statement trap: PgBouncer in transaction-mode rebinds connections after each transaction, so server-side prepared statements (PREPARE foo AS SELECT ...) are lost between transactions. PgBouncer 1.21+ ships server_prepared_statements = on which caches plan IDs server-side and replays them — without it, app frameworks that auto-prepare statements pay re-prepare overhead on every transaction.

Act 6 — The lock matrix and expand-contract

ALTER TABLE takes AccessExclusiveLock by default — incompatible with every other lock mode including reads. While it waits for the lock, every new query waits behind it. While it holds the lock, every query waits.

A short ALTER under low load completes in milliseconds. An ALTER behind a long-running query (as in Act 4) waits, builds a queue, and the queue freezes the database. A single 60-second lock on a hot table can cascade into a 10-minute incident due to connection timeout and app-level retry storms.

The expand-contract recipe for adding a tenant_id BIGINT NOT NULL column safely:

1. ALTER TABLE users ADD COLUMN tenant_id BIGINT — nullable, no default. No rewrite.
2. Backfill: UPDATE users SET tenant_id = 0 WHERE tenant_id IS NULL LIMIT 10000 — batched, low lock pressure.
3. ALTER TABLE users ADD CONSTRAINT chk_tenant_id CHECK (tenant_id IS NOT NULL) NOT VALID — does not scan the table.
4. ALTER TABLE users VALIDATE CONSTRAINT chk_tenant_id — scans the table under a ShareUpdateExclusiveLock, compatible with reads and writes.
5. ALTER TABLE users ALTER COLUMN tenant_id SET NOT NULL — metadata-only after a successful validation, milliseconds.
6. ALTER TABLE users DROP CONSTRAINT chk_tenant_id — cleanup.

Tools like pgroll and Atlas codify this recipe. Without it, the naive ADD COLUMN ... NOT NULL DEFAULT on Postgres 10 and earlier causes a full table rewrite; on Postgres 11+ it is a metadata change for constant defaults, but the lock still queues all queries.