Diff v1 → v2

  # 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);
+ > idempotent. Companion to [[push-compute-to-datastore|Push Compute to the Datastore]] (§3 transactions/SKIP LOCKED);
  > this is the depth. GUC defaults are PG16/17; version-gated items flagged.[^vacuum]
  
  ## 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]
  
  - **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).[^vacuum]
  - **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).[^locks]
  
  **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.[^locks]
  
  - `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**.[^iso]
  - **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.[^pool]
  
  - **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.**[^bulk]
  
  - **`unnest()` with array params** approaches COPY speed while staying an ordinary
    parameterized statement usable inside ORMs/transactions, and sidesteps the ~65535
    bind-parameter limit:
    ```sql
    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):[^idem]
  
  ```sql
  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).
  
  ## See also
- - [[Push Compute to the Datastore]] — the hub.
- - [[Postgres Scale-Out and Replication]] — replicas, sharding, CDC when one box isn't enough.
- - [[Postgres Query Planning and EXPLAIN]] — bloat and stale stats produce bad plans.
+ - [[push-compute-to-datastore|Push Compute to the Datastore]] — the hub.
+ - [[postgres-scale-out|Postgres Scale-Out & Replication]] — replicas, sharding, CDC when one box isn't enough.
+ - [[postgres-query-planning|Postgres Query Planning & EXPLAIN]] — bloat and stale stats produce bad plans.
  
  [^vacuum]: Routine vacuuming — <https://www.postgresql.org/docs/current/routine-vacuuming.html>.
      The `autovacuum_vacuum_max_threshold` cap is PG18-era; older versions use the uncapped formula.
  [^locks]: Explicit locking — <https://www.postgresql.org/docs/current/explicit-locking.html>.
  [^iso]: Transaction isolation / serialization-failure handling —
      <https://www.postgresql.org/docs/current/transaction-iso.html>.
  [^pool]: PostgreSQL wiki "Number Of Database Connections"; pgbouncer.org config/features.
      Per-connection MB figure is a range, not a flat number.
  [^bulk]: Populate / COPY — <https://www.postgresql.org/docs/current/populate.html>. Row/s figures
      are hardware-dependent benchmarks, directional only.
  [^idem]: Stripe engineering + brandur.org "Implementing Stripe-like Idempotency Keys in Postgres".