Skip to main content
← PROBLEMS / ARCHITECTURE
An end-to-end domain learning journey

Preventing PostgreSQL XID
Wraparound Shutdowns.

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.
Start the journey5 stages
Loading diagram…
Preventing PostgreSQL XID Wraparound Shutdowns architecture sketch diagram
FIRST PRINCIPLES. PRODUCTION SCALE.
The idea to keep with you

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.

01
ARCHITECTURE · STAGE 01L4 · FUNDAMENTALS

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.

ChoiceWhat it buysWhat it costs
Trade-off 1Emergency 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;
02
ARCHITECTURE · STAGE 02L5 · APPLIED DESIGN

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.

ChoiceWhat it buysWhat it costs
Trade-off 1Skipping 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;
03
ARCHITECTURE · STAGE 03L6 · SCALE & TRADE-OFFS

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.

ChoiceWhat it buysWhat it costs
Trade-off 1Higher 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);
04
ARCHITECTURE · STAGE 04L6 · SCALE & TRADE-OFFS

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.

ChoiceWhat it buysWhat it costs
Trade-off 1Long 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
05
ARCHITECTURE · STAGE 05L7 · STAFF / PRINCIPAL

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.

ChoiceWhat it buysWhat it costs
Trade-off 1Alerting 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-running idle in transaction sessions and orphaned replication slots that pin xmin, and tune aggressive per-table autovacuum_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.
Behavioral Progression · Postgresql Xid Wraparound Prevention

Postgresql Xid Wraparound Prevention — Normal → Load / Fault → Recovery

Send Request
1. Normal Request Ingress
Traffic: 1x baseline · Latency (p99): 4 ms
Process State
2. Core Execution & State Lookup
Traffic: 1x baseline · Latency (p99): 9 ms
Inject 10x Spike
3. Scale Spike / Failure Injection
Traffic: 10x burst · Latency (p99): 185 ms

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.

Synthesis / One connected design

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.

A candidate architecture, not a universal answer

Putting Preventing PostgreSQL XID Wraparound Shutdowns Together

Step 1
Step 2
Step 3
  1. Enter postgres --single.
  2. Drop dead slot, raise vacuum_cost_limit, run VACUUM (FREEZE, INDEX_CLEANUP OFF).
  3. Restart primary and run online VACUUM.
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.
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.

Practice / Explain the consequences

Make the trade-off yourself.

Practice Check

Test your understanding

Question 1 of 3

When is ACID Transaction Fundamentals the right default for a production team?

Key takeaway

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.