Postgres Concurrency, MVCC & Write Throughput (deep dive)

Postgres Concurrency, MVCC & Write Throughput

When to use it: diagnosing bloat/slow-after-a-while tables, lock waits and deadlocks,
"too many connections", a bulk load that crawls, isolation/race bugs, or making a write
idempotent. Companion to Push Compute to the Datastore (§3 transactions/SKIP LOCKED);
this is the depth. GUC defaults are PG16/17; version-gated items flagged.¹

1. MVCC, VACUUM, bloat, wraparound

Every UPDATE is delete-mark + insert — a new heap tuple (and, unless HOT applies, new
index entries) even if one column changed. So UPDATE-heavy tables need aggressive autovacuum,
not occasional VACUUM. Old versions physically coexist until vacuumed.¹

VACUUM marks dead-tuple space reusable; it does not return it to the OS (except
trailing empty pages). The visibility map ('all-visible' bit) is what lets index-only
scans skip the heap — dead weight on constantly-churning tables.
A dead tuple is reclaimable only past the global xmin horizon — the minimum snapshot
across every backend, every database, replication slots, and prepared txns. So one
idle-in-transaction session anywhere bloats unrelated tables cluster-wide. Guard with
idle_in_transaction_session_timeout (set per-role so batch jobs aren't killed).¹
Detect bloat cheaply with pg_stat_user_tables.n_dead_tup / n_live_tup (trend it);
reserve pgstattuple for exact-but-expensive checks. Remediate live tables with
pg_repack or REINDEX CONCURRENTLY (SHARE UPDATE EXCLUSIVE), not VACUUM FULL
(ACCESS EXCLUSIVE, blocks everything, and fails during a wraparound emergency).
XID wraparound: 32-bit XIDs; rows must be frozen before reuse. Warning ~40M txns out,
hard stop <3M (ERROR: database is not accepting commands that assign new XIDs). Recover
with a plain database-wide VACUUM (never VACUUM FULL — it needs an XID). Watch:
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

2. Locking

Row locks: FOR NO KEY UPDATE (for non-key updates) over FOR UPDATE when you don't touch
key columns — it's compatible with the implicit FK-check lock, so it won't block child-row
INSERTs. NOWAIT (error if locked) vs SKIP LOCKED (skip — queues only).²

Only ACCESS EXCLUSIVE blocks a plain SELECT. The trap: Postgres's lock queue is FIFO and
a waiting strong lock blocks everything behind it. One long SELECT + one queued
ALTER TABLE (waiting for ACCESS EXCLUSIVE) takes the whole table offline for unrelated
readers. Always run DDL with SET lock_timeout='2s' and prefer off-peak.²

ALTER TABLE ADD COLUMN ... DEFAULT <const> is cheap (PG11+, catalog-only) — but adding
NOT NULL or a volatile default (now()) in the same statement forces a full rewrite
under the exclusive lock. Split ADD CONSTRAINT ... NOT VALID + VALIDATE CONSTRAINT.
CREATE INDEX CONCURRENTLY: doesn't block writes, needs two scans, can't run in a txn
block, and can leave an INVALID index on failure — check pg_index.indisvalid after.
Deadlocks: fix by acquiring locks in a consistent order (e.g. by ascending PK)
everywhere; retry alone just reproduces them. In the absence of a detected cycle, a lock
wait blocks indefinitely.
Advisory locks: use pg_advisory_xact_lock (auto-released at txn end), not
pg_advisory_lock, under PgBouncer transaction pooling — session locks assume a stable
backend the pool doesn't guarantee.

3. Isolation anomalies

Lost update only arises from app-side read-then-write; a single UPDATE t SET x=x-1
never loses updates. Under REPEATABLE READ the second committer aborts with SQLSTATE
40001 — you must catch and retry the whole transaction.³
Write skew: two txns read overlapping data, write different rows, no write-write
conflict, but break a cross-row invariant ("≥1 doctor on call"). REPEATABLE READ does
not catch it; SERIALIZABLE (SSI) does. Don't assume RR is "safe enough" for any
invariant spanning >1 row.
SERIALIZABLE: non-blocking predicate locks; false positives possible; all 40001
failures need a full-transaction retry (Postgres won't auto-retry). Unconditionally retry
40001 and 40P01 (deadlock).
SELECT ... FOR UPDATE is the pragmatic alternative for single-row/single-table
read-modify-write (blocking beats abort-retry). Its limit: it can't lock rows that don't
exist yet or rows outside its query — those write-skew cases still need SERIALIZABLE.

4. Connections & pooling

Postgres forks a process per connection (~1–15MB each, workload-dependent), and pre-PG14
every query scanned the whole ProcArray (PG14 reworked this — ~2.1× throughput at 5000 conns).
Don't raise max_connections (default 100) as the fix for "too many clients" — put a pooler
in front.⁴

PgBouncer transaction mode is the default for web apps (a small backend pool serves
many clients). Breaks under transaction pooling: session SET, LISTEN/NOTIFY, WITH HOLD cursors, session-level advisory locks, and named prepared statements (unless PgBouncer
≥1.21 with max_prepared_statements>0).
Pool-size starting point: (cores×2)+effective_spindles → on SSD, roughly (cores×2)+1.
Load-test from there; it's a heuristic, not a law.

5. Write throughput

COPY ≫ multi-row INSERT ≫ single-row INSERT — official docs: COPY is "almost always
faster than INSERT even if PREPARE is used and batched." Order-of-magnitude (directional):
single-row ~1k rows/s, batched ~40k, COPY ~300k. Never loop single-row INSERTs for bulk
loads.⁵

unnest() with array params approaches COPY speed while staying an ordinary
parameterized statement usable inside ORMs/transactions, and sidesteps the ~65535
bind-parameter limit:
```
INSERT INTO events (user_id, kind, at)
SELECT * FROM unnest($1::bigint[], $2::text[], $3::timestamptz[]);
```
INSERT ... SELECT moves data server-side (no round-trip out and back).
SET LOCAL synchronous_commit = OFF for replayable bulk/log ingestion (bounded data-loss
window, no corruption) — scoped, never for a single unrepeatable user write.
Bulk-load a fresh table: create table → COPY → then build indexes and re-add FKs (bulk
build beats per-row maintenance). Defer FKs (SET CONSTRAINTS ALL DEFERRED) for
forward-referencing data. Bump maintenance_work_mem before the index build.
INSERT ... ON CONFLICT: DO UPDATE locks the conflicting row even on a no-op (hot-key
contention); DO NOTHING doesn't lock.

6. Idempotency at the app boundary

Make a retried request safe with a unique constraint + ON CONFLICT, not app-side
SELECT-then-INSERT (which races):⁶

INSERT INTO idempotency_keys (idempotency_key, response_status, response_body)
VALUES ($1, $2, $3)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING *;   -- empty result ⇒ replay: look up and return the stored response

Production-grade (Stripe/brandur pattern): persist the full original response keyed by the
idempotency key with an expiry (≥24h), and add a locked_at/status column to guard the
in-progress window (return 409 if a key's work is already running).