SSI internals and production autovacuum tuning

A production OLTP cluster suddenly logs hundreds of “could not serialize access” errors per minute on the orders table. You have 15 minutes before the on-call engineer escalates. The diagnosis path — isolation level confirmation, predicate-lock count, workload genuineness — is a step-by-step procedure, not guesswork.

Serializable Snapshot Isolation (SSI) internals

Postgres 9.1 introduced SSI per Cahill, Röhm, and Fekete’s 2008 paper. The mechanism:

1. Every read tracks “predicate locks” — lightweight, in-memory references to “I read this set”
2. Every write tracks rw-antidependencies — “this transaction wrote something my predicate matched after I read”
3. At commit time, if a transaction is part of a “dangerous structure” — a cycle of rw-antidependencies — SSI aborts it with SQLSTATE 40001 could not serialize access due to read/write dependencies

The math is conservative: SSI sometimes aborts transactions that would have committed safely (false positives are correct but cost throughput), but it never allows a non-serializable schedule.

Predicate-lock memory is bounded by max_pred_locks_per_transaction × max_connections. Under memory pressure the locks coarsen from row → page → relation, raising false-positive rate further.

Heap fillfactor and HOT chain length

pg_stat_all_tables.n_tup_hot_upd divided by n_tup_upd gives the HOT ratio. On UPDATE-heavy schemas you want this above 80%. Below 50% means fillfactor is too aggressive or the schema has the wrong index set for the workload.

The pgstattuple extension exposes tuple_count, dead_tuple_count, and free_space per table for deeper introspection.

Production tuning of autovacuum

The default knobs are too lax for any high-write table. Per-table overrides via storage parameters:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_limit = 2000,
  autovacuum_vacuum_cost_delay = 2
);

This turns on autovacuum at 5% dead-tuple ratio (instead of 20%), with higher cost-budget per cycle and shorter delay between IO chunks. On NVMe-backed clusters the cost_delay can be zero.

For tables with bursty write patterns, an explicit VACUUM (PARALLEL 4) orders; overnight via cron is common in addition to autovacuum.

autovacuum_max_workers bumps the worker pool from 3 to 6 or 8 on large hosts.

Watch pg_stat_progress_vacuum.phase to see live progress:

• scanning heap
• vacuuming indexes
• vacuuming heap
• cleaning up indexes
• truncating heap
• performing final cleanup

Observability: the metrics that actually move

Minimum-viable Postgres bloat dashboard:

-- Dead tuple ratios
SELECT relname, n_dead_tup, n_live_tup,
       n_dead_tup::float / NULLIF(n_live_tup + n_dead_tup, 0) AS dead_ratio
FROM pg_stat_user_tables ORDER BY dead_ratio DESC LIMIT 20;

-- Snapshot pinners
SELECT pid, backend_xmin, now() - xact_start AS duration, state, query
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY backend_xmin LIMIT 5;

-- Orphan replication slots
SELECT slot_name, xmin, confirmed_flush_lsn FROM pg_replication_slots WHERE xmin IS NOT NULL;

-- Wraparound ages
SELECT datname, age(datfrozenxid), age(datminmxid) FROM pg_database;