At 03:15 UTC, your primary PostgreSQL 15 database suddenly refuses all writes with `ERROR: database is not accepting commands to avoid wraparound data loss in database "orders"` and instructs you to run `VACUUM` in single-user mode. Logs show `WARNING: database "orders" must be vacuumed within 9,840,211 transactions` started 6 days ago, while autovacuum on the 800 GB `ledger_entries` table repeatedly aborted or fell behind due to a 14-hour analytical query and an abandoned Debezium replication slot.
From patterns to decisions.
At 03:15 UTC, your primary PostgreSQL 15 database suddenly refuses all writes with `ERROR: database is not accepting commands to avoid wraparound data loss in database "orders"` and instructs you to run `VACUUM` in single-user mode. Logs show `WARNING: database "orders" must be vacuumed within 9,840,211 transactions` started 6 days ago, while autovacuum on the 800 GB `ledger_entries` table repeatedly aborted or fell behind due to a 14-hour analytical query and an abandoned Debezium replication slot.
32-bit XIDs wrap after ~2.
32-bit XIDs wrap after ~2.14B transactions; an abandoned slot or long query pins global xmin, preventing vacuum from freezing or pruning tuples.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Emergency shutdown sacrifices availability to prevent irreversible silent data loss. |
Step 1
Explain modular 32-bit XID arithmetic (past vs future 2B split).
Step 2
Show how pg_replication_slots.xmin and pg_stat_activity.backend_xmin block freeze progress.
Alternative 1
Partitioning large tables so older partitions stay frozen permanently.
Failure Mode 1
Ignoring wraparound warnings until the hard 2B stop forces single-user recovery.
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;
SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;
Drop the dead replication slot.
Drop the dead replication slot in single-user mode and run VACUUM (FREEZE, INDEX_CLEANUP OFF) with high vacuum_cost_limit, then finish index cleanup online.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Skipping index cleanup cuts downtime from 22h to ~45m, at the cost of running an online VACUUM afterward. |
Step 1
Enter postgres --single.
Step 2
Drop dead slot, raise vacuum_cost_limit, run VACUUM (FREEZE, INDEX_CLEANUP OFF).
Step 3
Restart primary and run online VACUUM.
Alternative 1
Fail over to an up-to-date replica if the replica already has lower freeze age (rare, since WAL replicates XIDs).
Failure Mode 1
Running normal VACUUM FREEZE in single-user mode keeps the app offline for 20+ hours.
SET vacuum_cost_limit = 10000;
VACUUM (FREEZE, INDEX_CLEANUP OFF) ledger_entries;
Default cost_limit=200 throttles vacuum to.
Default cost_limit=200 throttles vacuum to ~10-20 MB/s (designed for spinning HDDs); NVMe SSDs easily sustain cost_limit=4000 with cost_delay=2ms.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Higher cost_limit increases background I/O slightly while preventing multi-day vacuum backlogs. |
Compute vacuum I/O budget
cost_limit / cost_delay * 8KB page costs.
Alternative 1
Time-range partitioning on ledger_entries (30 GB/month partitions).
Failure Mode 1
Setting cost_limit too high on low-IOPS gp2 EBS volumes can starve OLTP reads.
ALTER TABLE ledger_entries SET (autovacuum_vacuum_cost_limit = 4000, autovacuum_vacuum_cost_delay = 2);
Turn hot_standby_feedback = off on.
Turn hot_standby_feedback = off on analytics replicas, set max_standby_streaming_delay = '60s' or statement_timeout = '5min', and use logical replication/warehouse export for 14-hour BI jobs.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Long BI queries on the streaming replica may cancel with conflict errors instead of endangering primary XIDs. |
Step 1
Disable hot_standby_feedback on BI replicas.
Step 2
Enforce statement_timeout = '5min' for BI roles.
Alternative 1
Offload 14-hour BI queries to BigQuery/Snowflake/ClickHouse via CDC.
Failure Mode 1
Enabling hot_standby_feedback on a BI replica effectively turns primary autovacuum off for hours.
hot_standby_feedback = off
max_standby_streaming_delay = 60s
Implementation & Guardrails (5).
Alert on max(age(datfrozenxid)) > 500_000_000 (warning) and > 1_000_000_000 (page), plus inactive replication slots and long transactions > 15 minutes.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Alerting at 500M provides 4-5 days of runway at 300M XIDs/day. |
Step 1
Export pg_database_xid_age and pg_replication_slots_active via postgres_exporter.
Alternative 1
Daily cron check on age(relfrozenxid) top-10 tables.
Failure Mode 1
Alerting only at 1.9B leaves < 12 hours to vacuum an 800 GB table.
- alert: PostgresXidAgeHigh
expr: pg_database_xid_age > 500000000
for: 15m
Theory & Tips and Tricks
Practical heuristics & interview traps
- 01Interview anchor: Monitor
age(datfrozenxid)continuously, eliminate long-runningidle in transactionsessions and orphaned replication slots that pinxmin, and tune aggressive per-tableautovacuum_freeze_max_age+ cost limits. - 02Trade-off tip: Higher cost_limit increases background I/O slightly while preventing multi-day vacuum backlogs.
- 03Watch out for: Enabling hot_standby_feedback on a BI replica effectively turns primary autovacuum off for hours.
Postgresql Xid Wraparound Prevention — Normal → Load / Fault → Recovery
Monitor age(datfrozenxid) continuously, eliminate long-running idle in transaction sessions and orphaned replication slots that pin xmin, and tune aggressive per-table autovacuumfreezemaxage + cost limits.
Putting Preventing PostgreSQL XID Wraparound Shutdowns Together
Monitor `age(datfrozenxid)` continuously, eliminate long-running `idle in transaction` sessions and orphaned replication slots that pin `xmin`, and tune aggressive per-table `autovacuum_freeze_max_age` + cost limits.
Putting Preventing PostgreSQL XID Wraparound Shutdowns Together
- Enter postgres --single.
- Drop dead slot, raise vacuum_cost_limit, run VACUUM (FREEZE, INDEX_CLEANUP OFF).
- Restart primary and run online VACUUM.
Interview Readiness Check
Turn hot_standby_feedback = off on analytics replicas, set max_standby_streaming_delay = '60s' or statement_timeout = '5min', and use logical replication/warehouse export for 14-hour BI jobs.
Walk through bottlenecks, back-of-the-envelope math, and failure recovery.
Make the trade-off yourself.
Practice Check
Test your understanding
When is ACID Transaction Fundamentals the right default for a production team?
Preventing PostgreSQL XID Wraparound Shutdowns
Monitor age(datfrozenxid) continuously, eliminate long-running idle in transaction sessions and orphaned replication slots that pin xmin, and tune aggressive per-table autovacuum_freeze_max_age + cost limits.
