Monitoring says the age of the oldest transaction ID on this PostgreSQL database keeps climbing. What is it measuring, and what happens if it is ignored?

What the number is counting

Every transaction that writes gets a transaction ID, and every row version records the ID of the transaction that created it. Visibility is then decided by comparing IDs: this row was written by a transaction older than my snapshot, so I can see it. That comparison is the whole basis of MVCC in PostgreSQL, and it is done on a 32-bit counter, treated as circular so that at any moment roughly half the space counts as the past and half as the future. A counter that wraps means an ID that is very old eventually looks like one from the future, which would make committed rows abruptly invisible.

The mechanism that prevents this is freezing. Vacuum marks row versions old enough to be visible to everyone as frozen, meaning unconditionally visible, and a frozen row no longer needs its creating ID compared against anything. Once every row in a table is frozen up to some point, that table's relfrozenxid can move forward, and the database-level datfrozenxid follows the oldest table. The metric being alerted on is the distance between the current counter and that oldest frozen point:

-- Per database, then the specific tables holding it back.
SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY xid_age DESC;

SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind IN ('r', 'm')   -- ordinary tables and materialised views
ORDER BY xid_age DESC
LIMIT 5;

A rising age is not damage in itself. It says freezing work is being generated faster than it is completed, which is a race you lose slowly.

Why the age refuses to fall

The interesting part of the diagnosis is that adding vacuum capacity does not always help, because vacuum cannot freeze a row that some transaction might still need to see the pre-image of. Anything holding an old snapshot open therefore pins the horizon in place, and the usual culprits are all things that look harmless in isolation.

A long-running transaction is the obvious one, including a session left idle in transaction by an application that opened a transaction and then went to do something else. A prepared transaction from a two-phase commit that was never committed or rolled back holds its snapshot indefinitely and survives restarts, which makes it the nastiest of the set. A replication slot for a standby or a logical consumer that has disconnected holds the horizon back so that the absent consumer can still be served. And with hot_standby_feedback enabled, a long query on a standby propagates its requirement back to the primary.

Each of those has a different remedy, and none of them is "vacuum harder", so identifying which applies is the actual work. This is also where the topic joins bloat: the same held-back horizon that stops freezing also stops dead row versions being reclaimed, which is why an unnoticed idle transaction shows up simultaneously as growing tables and a growing transaction ID age.

What the server does as the margin closes

The escalation is deliberate and it happens in stages. Long before anything breaks, autovacuum starts launching vacuums specifically to freeze, triggered by table age rather than by how many rows changed, so tables that receive no writes at all are still visited — a static archive table is exactly the sort of thing that gets forgotten and then dominates the age.

As the remaining margin shrinks, the server begins emitting warnings on every commit telling you how many transactions remain. If that is also ignored, it stops assigning new transaction IDs rather than risk misjudging visibility. Reads continue; anything that needs to write fails. Recovery from that state is a vacuum, and in the worst case that means starting the server in single-user mode to perform it, which is an outage measured by the size of your largest table rather than by anything you control on the day. That asymmetry is the reason this metric deserves an alert with real headroom rather than a threshold near the cliff.

There is a second-order effect worth mentioning because it turns a slow problem into an urgent one. A routine autovacuum yields when it blocks another session's lock request, so it is largely invisible operationally. A vacuum running to prevent wraparound does not yield in the same way, so it can sit in front of a migration or an ALTER TABLE and hold up a deployment. Teams often discover anti-wraparound vacuum for the first time as a mysterious lock queue during a release, which is the same problem arriving through a different door.

What actually fixes it

The durable fixes are on the generating side rather than the consuming side. Cap how long a transaction may stay open, so an application bug cannot pin the horizon for a day; PostgreSQL offers timeouts aimed precisely at this, including one for sessions idle inside a transaction. Monitor replication slots for lag and for consumers that have gone away, and be willing to drop a slot whose consumer is not coming back. Give autovacuum enough throughput on the largest tables that freezing keeps pace, since the default cost limits are set for a much smaller machine than most production servers.

And keep the alert on the age, not on vacuum activity. The age is the only number that measures the thing you care about, which is how much of the counter remains before the server protects itself at your expense.

Transaction ID age is a countdown that vacuum resets, and anything holding an old snapshot open stops the reset. Alert on the age with plenty of margin, because the emergency remedy is a vacuum of your biggest table while writes are refused.