/explain/xmin-horizon

xmin horizon

The xmin horizon is the oldest snapshot anyone in the cluster still holds. Vacuum may only remove row versions that died before it. Everything newer must be kept, because a running transaction could still read it. Four things pin the horizon: long-running transactions, stale or unused replication slots, prepared transactions, and standby feedback. While one of them holds it back, no autovacuum setting can remove a single dead row, and n_dead_tup climbs on a perfectly tuned table.

The demo below needs JavaScript. Every definition, every formula and the snapshot query are plain text and are on this page already.

DEMO — toy table, 4 rowsnext xid 4711

ctidxminxmaxpayloadvisibility

(0,1)4702—'checkout'visible

(0,2)4703—'signup'visible

(0,3)4707—'refund'visible

(0,4)4710—'login'visible

LIVE / DEAD TUPLES

4 / 0

HEAP VERSIONS ON DISK

4

OPEN SNAPSHOT

none

UPDATE writes a new version and stamps the old row’s xmax. Open a snapshot first to see vacuum blocked.

SEE ALSO

dead tuple wraparound ← back to start

Open a snapshot in the demo (BEGIN) and vacuum stops removing anything deleted after it: that is the horizon in one page. In production, find the holder in pg_stat_activity, pg_replication_slots, or pg_prepared_xacts before touching any knob.