xmin horizon: the cutoff vacuum obeys

The xmin horizon is the oldest transaction id that anything in the cluster might still need to see. Every row version carries the id of the transaction that created it, and vacuum may only remove a version once no possible reader could still want it. The horizon is where that line falls. It is a single cluster-wide value, computed from the oldest snapshot anyone holds, and it moves forward only when whatever is holding it back lets go.

Why a vacuum can run for an hour and free nothing

Four things hold the horizon back, and they are not equally obvious. An open transaction holds it, including one sitting idle after a single SELECT. A replication slot holds it, whether or not a consumer is attached, which is why an abandoned slot is the classic cause. A prepared transaction nobody committed holds it indefinitely. And on a standby with feedback enabled, a query running there holds the primary’s horizon from a different machine entirely.

This is the mechanism behind the most misread line in any vacuum log: autovacuum ran, took time, and the dead row count did not drop. The vacuum did its job. It examined the dead versions, found their ids newer than the horizon, and left them because it was not allowed to do anything else. Reading that as “autovacuum is not keeping up” leads to tuning the wrong knob, usually more aggressive thresholds, which makes the server work harder at removing nothing.

Finding what is holding it

One query gives you the current horizon and a count of each kind of thing that can be pinning it. Run it on the database you care about; the horizon itself is cluster-wide.

SELECT pg_snapshot_xmin(pg_current_snapshot())                                AS horizon,
       (SELECT count(*) FROM pg_stat_activity WHERE backend_xmin IS NOT NULL) AS backends_holding_it,
       (SELECT count(*) FROM pg_replication_slots
          WHERE xmin IS NOT NULL OR catalog_xmin IS NOT NULL)                 AS slots_holding_it,
       (SELECT count(*) FROM pg_prepared_xacts)                               AS prepared_holding_it;

 horizon | backends_holding_it | slots_holding_it | prepared_holding_it 
---------+---------------------+------------------+---------------------
     755 |                   1 |                0 |                   0
(1 row)

A backend count of one on an otherwise idle server is your own session: the query holds a snapshot while it runs, which is a small demonstration of the thing it is measuring. When a count is higher than you expect, the next step is to join back to pg_stat_activity on backend_xmin and read state, xact_start and query for the offenders.

One thing the query cannot tell you is how long the horizon has been where it is. There is no timestamp on it. The usual proxy is the age of the oldest transaction in the activity view, or the restart time of the process holding it, and on a cluster with a slot the honest answer is that a slot has no memory of when it stopped moving either.

Where to take it next

The full treatment of what to do once you know what is holding the horizon is in the guide on autovacuum and table bloat, which covers the three distinct causes of dead rows piling up and the different fix each one needs. If the horizon has been stuck long enough that the age of the oldest unfrozen id is the worry rather than the disk, the guide on transaction id wraparound is the one to read, because past a certain age the consequence stops being bloat and starts being a shutdown.