CertGrid CertGrid
Hands-on Lab·PostgreSQL

PostgreSQL Transaction ID Wraparound

The failure that takes a database offline to protect it. This cluster is 899 transactions old, so the exercise is the arithmetic and the monitoring query - and why freezing one table moved nothing.

Troubleshooting Guide 46 of 47 Advanced

Written against the versions above. `vacuum_failsafe_age` arrived in PostgreSQL 14 and skips index cleanup to finish faster in an emergency. PostgreSQL 18 reports `eagerly scanned` pages in `VACUUM VERBOSE`, which earlier versions do not.

Every command on this page ran on db-a01.
Server NameIP AddressOSRolesCPURAMHDD
db-a01192.168.0.81Ubuntu 26.04 LTSPrimary / Source / Replica Set Member 12 Core4 GB50 GB

Before you start

  1. Measure how much of the budget is gone

    PostgreSQL stamps every row with the id of the transaction that created it. Transaction ids are 32-bit and circular, so "older than" is decided over a 2-billion window. If a row's stamp fell more than 2 billion behind, it would start looking like it came from the future and become invisible.

    PostgreSQL will not allow that. It refuses new transactions first - the database goes read-only rather than corrupting.

    age(datfrozenxid) is how far behind the oldest unfrozen row is. Every database here is at 155, which is 0.0000% of the budget. pg_current_xact_id() is 899 - this cluster has run 899 transactions in its entire life.

    Nothing in a lab can reach wraparound, and simulating it would take days of burning transactions. What is worth having is the query, the arithmetic, and knowing which numbers mean trouble.

    bash Example session 
    sudo -u postgres psql -c "SELECT datname, age(datfrozenxid) AS xid_age, round(100.0 * age(datfrozenxid) / 2147483648, 4) AS pct_of_budget FROM pg_database ORDER BY xid_age DESC"    datname    | xid_age | pct_of_budget---------------+---------+--------------- postgres      |     155 |        0.0000 template1     |     155 |        0.0000 template0     |     155 |        0.0000 appdb_partial |     155 |        0.0000 appdb         |     155 |        0.0000(5 rows)sudo -u postgres psql -c "SELECT pg_current_xact_id() AS current_xid, txid_current() AS legacy_view" current_xid | legacy_view-------------+-------------         899 |         899(1 row)

    Expected resultAge 155 across every database; current xid 899.

    Success conditionYou can measure wraparound distance on any cluster in one query.

  2. The thresholds that act before you have to

    Four numbers do the work:

    autovacuum_freeze_max_age 200,000,000 - at this age autovacuum vacuums the table whether or not autovacuum is switched on. It is the safety net, and at 200 million it fires with 90% of the budget still unused.

    vacuum_freeze_min_age 50,000,000 - rows younger than this are left alone, because freezing a row that is about to be updated again is wasted work.

    vacuum_freeze_table_age 150,000,000 - past this, an ordinary VACUUM upgrades itself to a whole-table scan instead of using the visibility map.

    vacuum_failsafe_age 1,600,000,000 - the emergency. Vacuum abandons index cleanup and cost delays and does nothing but freeze.

    The gap between 200 million and 2 billion is the margin. Wraparound emergencies are not the result of a missing setting; they are the result of something blocking vacuum for a very long time - an abandoned prepared transaction, an orphaned replication slot, or a session that opened a transaction in March.

    bash Example session 
    sudo -u postgres psql -c "SELECT name, setting FROM pg_settings WHERE name IN ('autovacuum_freeze_max_age','vacuum_freeze_min_age','vacuum_freeze_table_age','vacuum_failsafe_age','autovacuum_multixact_freeze_max_age','autovacuum')"                name                 |  setting-------------------------------------+------------ autovacuum                          | on autovacuum_freeze_max_age           | 200000000 autovacuum_multixact_freeze_max_age | 400000000 vacuum_failsafe_age                 | 1600000000 vacuum_freeze_min_age               | 50000000 vacuum_freeze_table_age             | 150000000(6 rows)

    Expected result200M, 50M, 150M and 1.6B, with autovacuum on.

    Success conditionYou know which threshold acts at which age.

  3. Find the table that is furthest behind

    age(relfrozenxid) per table is where the number actually comes from. ts_demo 102, customers 90, events 51 - all trivially small here, but the shape is what you would run on a real cluster.

    The second query is the useful form: 199,999,949 transactions before events gets an anti-wraparound vacuum whether anyone wants one or not.

    On a busy system that number is the warning. Divide it by your transaction rate and you have the time until a forced vacuum on a table that may be very large - which is a thing to schedule rather than be surprised by.

    bash Example session 
    psql -d appdb -c "SELECT relname, age(relfrozenxid) AS xid_age, pg_size_pretty(pg_relation_size(oid)) AS size FROM pg_class WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace ORDER BY xid_age DESC LIMIT 6"  relname  | xid_age |    size-----------+---------+------------ ts_demo   |     102 | 8192 bytes customers |      90 | 688 kB events    |      51 | 35 MB(3 rows)psql -d appdb -c "SELECT relname, age(relfrozenxid) AS xid_age, current_setting('autovacuum_freeze_max_age')::bigint - age(relfrozenxid) AS xids_until_forced_vacuum FROM pg_class WHERE relname = 'events'" relname | xid_age | xids_until_forced_vacuum---------+---------+-------------------------- events  |      51 |                199999949(1 row)

    Expected resultPer-table ages, and about 200 million transactions of headroom.

    Success conditionYou can rank tables by wraparound risk.

  4. Freeze a table and read the VERBOSE output

    VACUUM (FREEZE, VERBOSE) freezes everything it can rather than respecting vacuum_freeze_min_age. The age goes 51 to 0.

    The output is worth reading line by line:

    aggressively vacuuming - FREEZE forces the whole-table scan.

    new relfrozenxid: 900, which is 51 XIDs ahead of previous value - the table's horizon moved forward by exactly the age it had.

    frozen: 2 pages from table (0.04% of total) had 3 tuples frozen - almost nothing needed freezing, because almost everything already was.

    visibility map: 4540 pages set all-visible, 4540 pages set all-frozen - this is the real product. All-frozen pages are skipped entirely by future vacuums, which is what stops freezing cost from growing with table size.

    WAL usage: 4555 records, 4554 full page images, 37344570 bytes - 37 MB of WAL to freeze a 35 MB table. Freezing is not free, and on a large table it is a significant write burst that has to be archived and replicated too.

    bash Example session 
    psql -d appdb -c "SELECT age(relfrozenxid) AS before FROM pg_class WHERE relname = 'events'" before--------     51(1 row)psql -d appdb -c "VACUUM (FREEZE, VERBOSE) events"INFO:  aggressively vacuuming "appdb.public.events"INFO:  launched 2 parallel vacuum workers for index cleanup (planned: 2)INFO:  finished vacuuming "appdb.public.events": index scans: 0pages: 0 removed, 4543 remain, 4543 scanned (100.00% of total), 0 eagerly scannedtuples: 0 removed, 400000 remain, 0 are dead but not yet removableremovable cutoff: 900, which was 0 XIDs old when operation endednew relfrozenxid: 900, which is 51 XIDs ahead of previous valuefrozen: 2 pages from table (0.04% of total) had 3 tuples frozenvisibility map: 4540 pages set all-visible, 4540 pages set all-frozen (0 were all-visible)index scan bypassed: 3 pages from table (0.07% of total) have 5 dead item identifiersindex "idx_events_payload": pages: 114 in total, 0 newly deleted, 1 currently deleted, 1 reusableavg read rate: 318.770 MB/s, avg write rate: 311.589 MB/sbuffer usage: 4663 hits, 4661 reads, 4556 dirtiedWAL usage: 4555 records, 4554 full page images, 37344570 bytes, 0 buffers fullsystem usage: CPU: user: 0.01 s, system: 0.01 s, elapsed: 0.11 sINFO:  aggressively vacuuming "appdb.pg_toast.pg_toast_16513"INFO:  finished vacuuming "appdb.pg_toast.pg_toast_16513": index scans: 0pages: 0 removed, 0 remain, 0 scanned (100.00% of total), 0 eagerly scannedtuples: 0 removed, 0 remain, 0 are dead but not yet removableremovable cutoff: 900, which was 0 XIDs old when operation endednew relfrozenxid: 900, which is 51 XIDs ahead of previous valuefrozen: 0 pages from table (100.00% of total) had 0 tuples frozenvisibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removedavg read rate: 104.167 MB/s, avg write rate: 0.000 MB/sbuffer usage: 32 hits, 1 reads, 0 dirtiedWAL usage: 1 records, 0 full page images, 258 bytes, 0 buffers fullsystem usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 sVACUUMpsql -d appdb -c "SELECT age(relfrozenxid) AS after FROM pg_class WHERE relname = 'events'" after-------     0(1 row)sudo -u postgres psql -c "SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database WHERE datname = 'appdb'" datname | xid_age---------+--------- appdb   |     156(1 row)

    Expected resultevents age 0, and the database age unchanged at 156.

    Success conditionYou can read what a freeze actually did from VERBOSE output.

  5. Why the database age did not move

    events is at 0 and appdb is still at 156.

    datfrozenxid is the minimum across every relation in the database, and listing them shows what is holding it: pg_type, pg_toast_2604, pg_toast_1255 - system catalogues, not user tables.

    This is the trap. Someone watching a wraparound warning freezes the obvious big table, sees the database age refuse to move, and concludes freezing does not work. The database is only as young as its oldest relation, and that is frequently a catalogue or a TOAST table nobody thought about.

    vacuumdb --freeze --dbname=appdb covers everything, and the database age drops to 0 immediately.

    In a real emergency this is the command, run per database - and the databases that get forgotten are template1 and postgres, which have their own ages and can trigger a cluster-wide shutdown on their own.

    bash Example session 
    sudo -u postgres psql -d appdb -c "SELECT relname, age(relfrozenxid) AS xid_age FROM pg_class WHERE relkind IN ('r','t','m') AND age(relfrozenxid) > 0 ORDER BY xid_age DESC LIMIT 5"    relname    | xid_age---------------+--------- pg_toast_2604 |     156 pg_toast_1255 |     156 pg_type       |     156 pg_toast_1247 |     156 pg_toast_2606 |     156(5 rows)sudo -u postgres vacuumdb --freeze --quiet --dbname=appdbsudo -u postgres psql -c "SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database WHERE datname = 'appdb'" datname | xid_age---------+--------- appdb   |       0(1 row)

    Expected resultCatalogues at 156, then the database at 0 after vacuumdb --freeze.

    Success conditionYou will freeze the whole database rather than one table.

  6. The query to put on a dashboard

    Three columns: current age, transactions until a forced anti-wraparound vacuum, and transactions until wraparound itself.

    appdb at 0 has the full 2,147,483,648. The others sit at 156 - including template0 and template1, which nobody writes to and which still age, because age is measured against the cluster's transaction counter rather than against their own activity.

    Alert on until_forced falling, not on age rising - it is the same signal expressed as headroom, and it does not need a threshold updated whenever autovacuum_freeze_max_age changes.

    If it ever does start climbing, the cause is almost never vacuum being too slow. Look for the thing blocking it: pg_stat_activity ordered by xact_start, pg_prepared_xacts, and pg_replication_slots where active = false.

    bash Example session 
    sudo -u postgres psql -c "SELECT datname, age(datfrozenxid) AS xid_age, current_setting('autovacuum_freeze_max_age')::bigint - age(datfrozenxid) AS until_forced, 2147483648 - age(datfrozenxid) AS until_wraparound FROM pg_database ORDER BY xid_age DESC"    datname    | xid_age | until_forced | until_wraparound---------------+---------+--------------+------------------ postgres      |     156 |    199999844 |       2147483492 template1     |     156 |    199999844 |       2147483492 template0     |     156 |    199999844 |       2147483492 appdb_partial |     156 |    199999844 |       2147483492 appdb         |       0 |    200000000 |       2147483648(5 rows)

    Expected resultHeadroom per database, appdb with the full budget.

    Success conditionYou have a wraparound check that needs no interpretation.

Troubleshooting

Official sources

← All PostgreSQL guides