For three months, autovacuum ran against our orders table like clockwork — three, sometimes four runs a day, dead tuples collected, indexes skimmed, everything green. And for those same three months, age(relfrozenxid) on that table climbed from 480 million to 620 million, roughly two million transaction IDs a week, while every vacuum politely declined to freeze anything. On a Tuesday at 11:40 the age crossed the threshold we had quietly raised a year earlier, and the anti-wraparound vacuum that finally launched spent the whole afternoon reading 420 gigabytes of heap, pushing read latency on the primary from 2 ms to 18 ms and doubling our WAL rate until 16:20. Nothing failed. Everything was slow, and it was entirely self-inflicted.
The table was 1.1 billion rows, about 420 GB, with a write profile that is a trap for freezing: furious churn in the last ninety days of rows and near-silence in everything older. This post is the freeze strategy I wish we had set before that Tuesday. The wraparound endgame itself is covered in the wraparound emergency notes; what follows is about never needing it.
What does vacuum freezing actually do?
Freezing rewrites a tuple header so its xmin is replaced by FrozenTransactionId — the reserved value 2 — which every transaction, now and after any number of wraparounds, treats as "committed long ago." PostgreSQL's transaction IDs are 32-bit and compared in a circle: a tuple whose xmin sits more than about two billion transactions in the past stops being old and starts looking like it belongs to the future, which is the wraparound failure that shutdown-with-warning exists to prevent. Freezing is how a tuple escapes the circle. Once frozen, it owes nothing to the xid counter, and the ID its header used to carry is free to be reused.
Two consequences matter operationally. First, freezing is physical work: the page is dirtied, the change is WAL-logged, and the visibility map can then mark the page all-frozen so future vacuums skip it entirely. Second, relfrozenxid on pg_class is the table's watermark — the oldest xid that might still appear unfrozen anywhere in the heap — and it only advances when a vacuum scans the table aggressively, meaning every page not already marked all-frozen. A vacuum that skips pages via the visibility map cannot promise it saw the oldest tuple, so it leaves relfrozenxid alone. That single rule explains our three months of drift.
The visibility map is also where the eager-versus-lazy distinction lives. Lazy freezing is what a normal, map-guided vacuum does: it visits only pages that might contain dead tuples and freezes headers opportunistically on the pages it happens to touch, so a stable cold region can sit unfrozen for years while autovacuum runs daily and freezes almost nothing. That is efficient for dead-tuple cleanup and useless for advancing the watermark. Eager freezing is what an aggressive scan does: it walks every page not already marked all-frozen, freezes whatever is older than freeze_min_age, and — the part that pays later — sets the all-frozen bit, so every future lazy vacuum skips that page for both purposes. Read strategically, eager freezing is front-loaded IO that buys permanent exemptions. A cold table aggressively frozen once costs almost nothing to vacuum ever again; a table you protect from aggressive scans accumulates an unbounded debt of unvisited pages, and the debt always comes due as one giant scan instead of many small ones. The work was never avoidable, only deferrable.
How do the three freeze knobs interact?
vacuum_freeze_min_age decides whether an individual tuple is old enough to freeze when its page is being vacuumed — default 50 million xids. vacuum_freeze_table_age decides when a vacuum stops trusting the visibility map and scans the whole table anyway — default 150 million, measured as age(relfrozenxid). autovacuum_freeze_max_age is the hard tripwire — default 200 million — past which the launcher forces an anti-wraparound autovacuum on the table whether it has dead tuples or not, and per-table autovacuum_enabled = false does not save you from it.
The interaction that bites people is the gap between table_age and max_age. In the healthy path, a table crosses 150 million, its next regular autovacuum runs an aggressive scan, freezes the cold pages, marks them all-frozen, advances relfrozenxid, and the meter resets — a steady, boring tax. If you raise table_age to postpone that tax, you do not get fewer freezes; you get one bigger one later, and if you raise max_age along with it — we had set both to one billion after an earlier freeze scare, a configuration I now consider a bug — the forced vacuum at the end arrives on the launcher's schedule, not yours, during whatever traffic Tuesday afternoon brings. Lowering max_age below default is occasionally sensible on very large tables so the forced run happens while the heap is smaller; raising it past a few hundred million is just borrowing IO from your future self at bad rates.
Why does a low freeze_min_age hurt hot tables?
Because freezing is permanent work spent on tuples that are about to be rewritten anyway. A row that gets updated next week does not need a frozen header; it needs a regular vacuum to remove its dead version after it dies. If freeze_min_age is set low — ten million, five million, values I see recommended for "staying ahead of wraparound" — then on a hot table every aggressive vacuum freezes rows that are still within their normal lifetime, dirties and WAL-logs those pages, and then the next UPDATE dirties them again to install a fresh xmin. You pay the freeze IO and the update IO on the same page, repeatedly, and relfrozenxid barely benefits because the table's oldest unfrozen tuples live somewhere else entirely. On our orders table the young ninety-day region was re-frozen on every aggressive pass while the ten-year-old archive pages — the ones actually holding the watermark back — sat all-visible and untouched by lazy vacuums.
The honest cost sheet: a higher freeze_min_age means more of the heap carries unfrozen headers for longer, which is exactly what table_age and max_age exist to bound — you are not skipping freezing, you are batching it into the aggressive scans that have to happen anyway. For hot, churn-heavy tables I set the per-table minimum well above the default and keep the table-age trigger near it, so freezing happens rarely, in bulk, and on tuples that are genuinely old:
ALTER TABLE public.orders SET (
autovacuum_freeze_min_age = 400000000,
autovacuum_freeze_table_age = 500000000,
autovacuum_freeze_max_age = 600000000
);Those per-table storage parameters exist precisely for this asymmetry: the archive table that never changes can keep defaults, while the churn table gets a strategy. One caveat worth stating plainly — per-table max_age above the global default is a deliberate choice to let a large table freeze less often, and it only stays honest if you are actually watching the age trend. The general vacuum tuning background is in the PostgreSQL vacuum guide.
What is the difference between eager and lazy page freezing?
Lazy freezing is what a normal, visibility-map-guided vacuum does: it visits only pages that might contain dead tuples, and it freezes headers opportunistically on the pages it happens to touch. Pages marked all-visible are skipped, so a stable cold region can sit unfrozen for years while autovacuum runs daily and freezes almost nothing. That is efficient for dead-tuple cleanup and useless for advancing relfrozenxid — precisely our three-month drift. Eager freezing is what an aggressive scan does: it walks every page not already marked all-frozen, freezes whatever is older than freeze_min_age, and — the part that pays later — sets the all-frozen bit in the visibility map, so the next lazy vacuum skips that page for both purposes.
The strategic reading: eager freezing is front-loaded IO that buys permanent exemptions. A cold table that has been aggressively frozen once costs almost nothing to vacuum ever again. A table you protect from aggressive scans accumulates an unbounded debt of unvisited pages. When people say their vacuum "never finishes" on a big table, the cause is usually this debt coming due as one giant aggressive scan instead of many small ones — the work was never avoidable, only deferrable.
What should you do when the freeze storm hits anyway?
You throttle or you schedule — those are the only two levers, and both are vacuum_cost settings. autovacuum_vacuum_cost_delay and autovacuum_vacuum_cost_limit (or vacuum_cost_delay and vacuum_cost_limit for a manual run) meter how much IO vacuum may do per unit time; the default limit of 200 with the modern 2 ms delay lets a single worker pull several hundred megabytes per second of read traffic on a big heap, which is exactly the storm we ate. Halving effective throughput by lowering the limit stretches the freeze over more wall-clock hours but keeps p99 read latency survivable; raising the limit finishes sooner at the price of a heavier but shorter hit. Neither changes the total bytes.
The move that actually fixed our Tuesdays was taking the freeze out of autovacuum's hands for the biggest tables: watch the age trend, and when a table is heading toward its table_age, run the aggressive freeze yourself in the small hours, at a cost profile you chose:
SET vacuum_cost_delay = '2ms';
SET vacuum_cost_limit = 1000;
VACUUM (FREEZE, VERBOSE) public.orders;VACUUM FREEZE treats the freeze ages as zero for that run — it freezes everything it can and marks pages all-frozen, which is the deepest reset you can buy, and on a table with a genuinely cold region it is cheaper than it looks because the young churned region is small. Run it monthly or quarterly off-peak, and the launcher never gets to surprise you. One warning from experience: do not cancel an anti-wraparound autovacuum that is already running unless you have a plan — it restarts from the beginning, and a half-frozen afternoon followed by a full-frozen evening is strictly worse.
How do you monitor freeze age per table?
age(relfrozenxid) per relation, ranked, against the limit that will trigger a forced run — this query is the entire early-warning system, and it costs nothing:
SELECT n.nspname AS schema,
c.relname,
age(c.relfrozenxid) AS xid_age,
round(100.0 * age(c.relfrozenxid)
/ current_setting('autovacuum_freeze_max_age')::numeric, 1) AS pct_of_max_age,
pg_size_pretty(pg_relation_size(c.oid)) AS heap_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;Read it two ways. The pct_of_max_age column tells you who gets forced next — anything above 70 percent on a large table deserves a scheduled manual freeze this week, not next quarter. The trend tells you whether your strategy works: after an aggressive vacuum the age should drop to near zero; if it drops only a little, the scan was interrupted before it could advance the watermark, and if it climbs steadily between runs, your table_age is too high relative to your xid consumption rate. Multiplying the weekly climb against the remaining headroom gives you a calendar date for the next forced run — ours would have said "Tuesday, around lunchtime" three weeks in advance.
Watching freeze age with MonPG
Everything in this post is a time series: age(relfrozenxid) per table against its max_age trigger, autovacuum run counts and durations, dead-tuple accumulation, WAL generation rate, and read latency during vacuum windows. MonPG graphs exactly these series as part of its PostgreSQL monitoring, with alerting on xid age percentage, so the slow drift we ignored for three months shows up as a line bending toward the threshold weeks before the storm — and the fix becomes a quiet scheduled VACUUM FREEZE at 03:00 instead of an afternoon of 18 ms reads.