Free tool
How long until this database stops accepting writes?
Enter the oldest age(datfrozenxid), how fast you burn transaction IDs, and how big the table is. The answer comes back in days and hours, for the major version you actually run.
Run it on your numbers
The largest value the check-up query returns from pg_database.
Read-only transactions do not consume one, so the figure derived from pg_stat_database is an upper bound. That is the safe direction to be wrong in.
Heap plus indexes. The anti-wraparound vacuum has to get through this one before the oldest XID moves.
What you have measured, not what the disk can do: the cost delay is usually what sets it.
Added in PostgreSQL 14. Never effective below 1.05 x autovacuum_freeze_max_age.
The counter almost nobody graphs, and the one that stops a cluster taking writes while every wraparound dashboard is green.
Row locks shared between transactions: foreign keys, SELECT FOR SHARE, and the update patterns behind them.
Why subtracting your age from two billion is the wrong sum
Every monitoring tool already shows you age(datfrozenxid)
next to a limit, and the gap between them looks like the answer. It is not, because the gap is
not yours to spend. Long before the stop limit, the server starts an anti-wraparound vacuum
that will not be cancelled by a conflicting lock request and will not be talked out of
finishing. From that moment the question stops being how many transaction IDs are left and
becomes whether the freeze can get through your largest table before the ones that are left
run out.
That is a race between two things you already know and probably have never written on the same line: how fast you burn transaction IDs, and how fast the vacuum actually moves on that table. A four terabyte table at twelve megabytes a second is eight days of freezing. If the runway between the forced vacuum and the stop limit is nine days, you have a plan. If the table grew and the cost limit did not, you have a countdown.
How many hours the oldest table has left
The four countdowns above are the four things the server does, in the order it does them, and the version selector matters for every one. Before PostgreSQL 14 the warning began eleven million transaction IDs from the wrap point and writes stopped one million out, with recovery through a single-user backend. From 14 those became forty million and three million, and the failsafe arrived: past a configured age the vacuum drops its cost delay, skips index vacuuming and gives up its buffer strategy, which is the difference between a slow freeze and a fast ugly one.
The multixact line is there because it is the one people find out about afterwards. It has its own counter, its own forced vacuum and its own stop limit, and a cluster with foreign keys under contention can reach it while the transaction ID graph everyone watches is flat. On 13 its stop limit sits one hundred multixacts before the wrap point rather than a million, which is not a rounding difference.
What to change, and what each change costs
The first instinct when forced vacuums become inconvenient is to raise
autovacuum_freeze_max_age, and it
works, in the sense that the vacuums stop happening as often. What it also does is move the
start of the race later while leaving the finish line exactly where it was. The tool prints
both numbers so the trade is visible before it is made rather than after.
The other lever is throughput, and it is usually the better one: a higher cost limit or a
smaller delay makes the freeze take less of the runway without shortening the runway. And
vacuum_failsafe_age has a floor
nobody expects, because the server takes the larger of that setting and 1.05 times the forced
vacuum age, so raising one silently raises the other.
Nobody wants to be the one who ran the query too late.
DBExplore watches the numbers behind these checks on every cluster it is pointed at, and asks before it changes anything. We onboard design partners in small batches.