PostgreSQL transaction ID wraparound and autovacuum, on your iPhone
Wraparound gives you weeks of warning before the outage, if you look. HeapDeck shows how close each server is, the tables holding it back and the common reasons autovacuum falls behind.
Updated · Vladimir Chemeris, the developer of HeapDeck
Why wraparound is worth a tile
PostgreSQL transaction IDs are 32-bit. Rows carry the ID of the transaction that wrote them, and vacuum has to freeze old rows before the counter comes around again, about 2.1 billion transactions later. A database’s datfrozenxid is the oldest unfrozen ID it holds. If its age gets too close to the limit, PostgreSQL stops accepting commands that assign new transaction IDs. Writes stop until a vacuum finishes, and on a big table that vacuum can take hours.
Autovacuum prevents this on its own in the common case: once a table’s age passes autovacuum_freeze_max_age (200 million by default), it starts an aggressive vacuum. Trouble starts when something keeps that vacuum from advancing the horizon.
You can check the age yourself:
SELECT datname,
age(datfrozenxid) AS xid_age,
round(100.0 * age(datfrozenxid) / 2147483648, 1) AS pct_of_limit
FROM pg_database
ORDER BY xid_age DESC;
The Vacuum & Bloat screen
The Wraparound tile on Health shows the worst database as a share of 2³¹, like 52% with 1.08B of 2.1B. It turns amber at 25% and red at 75%.
Tap it and Vacuum & Bloat opens with the same number, autovacuum_freeze_max_age and the oldest table. From 75% the screen warns you, The database will stop accepting writes at 100%, and names the common reasons autovacuum can’t freeze old rows:
- A long-running open transaction. Its snapshot pins the oldest visible ID. The link opens Activity filtered to
idle in transaction. - An inactive replication slot. It holds back the horizon until you drop it or its consumer comes back. The link opens Replication › slots.
- A stuck autovacuum worker, for example one blocked on a lock it can’t get.
Under that you get the oldest tables by relfrozenxid age in the current database.
Dead tuples and unused indexes
The same screen lists tables by dead tuples: the dead share of each table, 18.1M dead / 42M live, and the last vacuum, or never vacuumed in red. Tap a table for its card with live and dead rows and the last VACUUM, autovacuum and ANALYZE. Swipe a row to run VACUUM (ANALYZE) or ANALYZE on that table. HeapDeck runs the operation on its own short connection while monitoring continues, and you can stop it from the screen.
VACUUM FULL sits on the same sheet, behind a warning about its ACCESS EXCLUSIVE lock for the whole rewrite and a field where you type the table name.
Unused indexes lists indexes with no scans since the last statistics reset, largest first, with the space they hold. If the statistics are less than seven days old, the screen warns you, and an index that backs a foreign key names the constraint it serves. Scans on replicas don’t count here. HeapDeck leaves dropping them to you: the button copies DROP INDEX CONCURRENTLY schema.index; for you to run when you can watch it.
Free and Pro
You can see everything on this page for free on one saved server. Vacuum & Bloat reads the server when you open it; pull down to read it again. Running VACUUM and ANALYZE from the phone, and adding more servers, is part of HeapDeck Pro, a one-time purchase.
Questions
What does database is not accepting commands to avoid wraparound data loss mean?
The age of a database's datfrozenxid got too close to the limit, so PostgreSQL refuses commands that assign new transaction IDs. Clear what holds the horizon back, such as long transactions and inactive slots, then vacuum the oldest tables. Writes resume when the age falls.
How do I check how close I am to wraparound?
Run SELECT datname, age(datfrozenxid) FROM pg_database; and divide by 2147483648. HeapDeck's Wraparound tile shows the worst database as that percentage, amber at 25% and red at 75%.
Why is autovacuum not removing dead tuples?
Something older than those rows still needs them: a long-running or idle in transaction session, an inactive replication slot, or a prepared transaction. Vacuum runs, and it leaves the dead rows in place until that holder is gone.