XID wraparound emergency simulation

XID wraparound emergency simulation
Type: incident simulation · Topic: Vacuum & Bloat · Level: L4 · Duration: 10–15 min
Launch: ride postgres start stage-07/08-xid-wraparound-emergency-simulation

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: one database (tenant_b) had a high age(datfrozenxid) — a simulated
approach toward transaction-id wraparound — while the other databases were fresh.
Left unaddressed, wraparound forces the cluster into read-only protection; the
remediation is to FREEZE the at-risk database.

How it was found: pg_database ordered by age(datfrozenxid) pinpointed tenant_b;
the relation-level age(relfrozenxid) inside it confirmed the oldest relations.

The mitigation: a database-wide VACUUM FREEZE on tenant_b, which advances
datfrozenxid and clears the risk.

Lesson: monitor age(datfrozenxid) per database and FREEZE before reaching
autovacuum_freeze_max_age. Wraparound risk is per-database — find the at-risk one
and freeze it; don't freeze only the default database, disable autovacuum, or add
an index.

NOTE: this is a SAFE, bounded simulation (a small number of transactions and a low
threshold). pgpg never drives a real cluster toward wraparound; production XID ages
are in the hundreds of millions, but the diagnosis and fix are identical.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. An emergency-style XID-age alert is firing. Find which database is at risk: SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY age(datfrozenxid) DESC; tenant_b is far ahead of the others.
2. Inside the risk database, see the oldest relations: \connect tenant_b then SELECT c.oid::regclass AS relation, age(c.relfrozenxid) AS xid_age FROM pg_class c WHERE relkind IN ('r','m') ORDER BY age(c.relfrozenxid) DESC LIMIT 10; The remediation is to FREEZE that database.
3. Freeze tenant_b: \connect tenant_b then VACUUM FREEZE; (database-wide). Don't freeze only the default database, don't disable autovacuum, and don't add an index.