PostgresトランザクションIDラップアラウンド:最悪の土曜まで4時間

Saturday morning. pg_stat_activity shows autovacuum: VACUUM (to prevent wraparound) on your largest table. Application writes slow down. Then the throughput craters. If you do nothing, in a few hours your DB stops accepting writes entirely. If you cancel the vacuum, transaction-ID wraparound happens and you have a corruption risk. This is the runbook.

What’s actually happening

Postgres uses a 32-bit transaction ID. Every row stores the txid that wrote it. To know whether a row is visible to your transaction, Postgres compares txids. Once txids approach 2^31, the math wraps and old rows could appear newer than they are — a data-corruption hazard.

Postgres prevents this by “freezing” old rows: marking them with a special sentinel that means “older than any current txid.” Vacuum freezes pages as it goes. When a table’s oldest unfrozen txid hits autovacuum_freeze_max_age (default 200M), Postgres triggers an aggressive autovacuum that holds an exclusive lock more often and runs faster — but it’s still rate-limited by autovacuum_vacuum_cost_limit.

Early warning that buys you weeks

-- Tables nearest to wraparound — alert when any value > 800M
SELECT
  c.relnamespace::regnamespace || '.' || c.relname AS table,
  age(c.relfrozenxid) AS xid_age,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relkind IN ('r', 't', 'm')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;

-- Cluster-wide datfrozenxid
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

Already in aggressive mode

• Don’t cancel the vacuum. If you do, autovacuum will restart it on the same table; you’ve made no progress and burned IO.
• Raise autovacuum_vacuum_cost_limit aggressively. Default 200 → 5000+. The vacuum will use more IO but finish faster.
• Reduce autovacuum_vacuum_cost_delay to 0 if you’re comfortable with the IO load.
• Pause non-essential writes if possible — vacuum competes with writers for buffer locks.
• Don’t restart the server. Restarts force a recovery scan that doesn’t advance frozen state.

-- Apply temporarily; revert after the fire is out
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 10000;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 0;
SELECT pg_reload_conf();

-- Watch progress
SELECT * FROM pg_stat_progress_vacuum;

After the fire is out

• Lower autovacuum_freeze_max_age on hot tables (per-table override) so freeze runs more often, less aggressively.
• Raise autovacuum_max_workers if you have CPU/IO budget — multiple tables can freeze in parallel.
• Add the relfrozenxid alert above to your monitoring stack permanently.
• Audit any process that resets statistics (pg_stat_reset) — aggressive resets can hide vacuum progress.

FAQ