PostgreSQL DBA Part 14: Transaction ID Wraparound and Freezing

This is Part 14 of the PostgreSQL DBA series. Transaction ID wraparound is what happens when PostgreSQL's 32-bit transaction counter runs out of room: old rows would suddenly look like they were written in the future and vanish from every query. PostgreSQL prevents this by freezing old rows during VACUUM, and if freezing falls too far behind, it stops accepting writes to protect your data.

In Part 13 (table and index bloat) you saw what happens when VACUUM cannot keep up with dead rows. This part covers the other job VACUUM must never fall behind on. By the end you will be able to measure how close every database and table is to wraparound, watch VACUUM freeze rows, recognise the warning and error messages, and recover a database that has stopped accepting writes, in the order the PostgreSQL documentation gives. You will reproduce a real wraparound emergency on a throwaway cluster, so the first time you see these messages is not in production.

What you will learn

• Why a 32-bit transaction ID needs freezing, and what a frozen row is
• How to check wraparound risk with age(datfrozenxid) and age(relfrozenxid)
• The difference between a normal VACUUM, an aggressive VACUUM and VACUUM (FREEZE)
• What an autovacuum "to prevent wraparound" is and why you should not cancel it
• The exact WARNING and ERROR messages near wraparound, and the documented recovery steps
• Which settings control freezing (vacuum_freeze_min_age, autovacuum_freeze_max_age and friends) and their defaults

Prerequisites

• Parts 1 to 13 of this series, especially Part 11 (MVCC, xmin and xmax), Part 12 (VACUUM and autovacuum) and Part 10 (sessions and pg_stat_activity).
• One Ubuntu server (24.04 LTS) with PostgreSQL 18 from the PGDG repository, cluster 18 main running on port 5432, set up as in Part 2. We call this server db1.
• A user with sudo rights. SQL commands are run in psql as the postgres operating system user, which connects as the database superuser postgres.
• About 300 MB of free disk space. The emergency lab writes a large server log on purpose.

How it works

Every transaction that changes data gets a transaction ID (XID). As you saw in Part 11, each row version stores the XID that created it in its hidden xmin column. To decide whether you can see a row, PostgreSQL compares that xmin with your own transaction: older means visible, newer means "in the future" and invisible.

The XID is a 32-bit number, so there are only about 4 billion of them. PostgreSQL does not stop at the end. It treats the XID space as a circle, like the hands of a clock: for any XID, the 2 billion XIDs behind it count as the past and the 2 billion ahead count as the future. That works as long as no row is more than about 2 billion transactions old.

Now picture a row written at XID 1,000 that nobody touches for years. After a little more than 2 billion new transactions, XID 1,000 is no longer "behind" the current XID on the circle. It is ahead. The row suddenly looks like it was written in the future, and every query stops returning it. The documentation calls this catastrophic data loss, adding that the data is still there, which is cold comfort if you cannot get at it.

The fix is freezing. When VACUUM finds a row whose creating transaction is old enough that every current and future transaction must see it, it sets a frozen flag in the row header. A frozen row is treated as older than every normal XID, forever, so it can never jump into the future. Since PostgreSQL 9.4 freezing only sets a flag bit; the original xmin is kept for forensic use.

PostgreSQL tracks how far behind freezing is with two numbers:

The function age(xid) returns the number of transactions between an XID and the current transaction counter. So age(datfrozenxid) answers the key question: how many transactions has this database gone without being fully frozen? The hard limit is about 2.1 billion (231).

VACUUM has three levels of freezing work:

If all of that fails, for example because something prevents VACUUM from freezing old rows, PostgreSQL protects itself in stages. When the oldest database is 40 million transactions from the limit, every new XID produces a WARNING. When it is 3 million away, PostgreSQL refuses to start any transaction that needs a new XID: reads still work, writes fail. You will see both in the lab.

Multixact IDs (MXIDs) are a second 32-bit counter. When more than one transaction locks the same row (for example two sessions running SELECT ... FOR SHARE), PostgreSQL stores a multixact ID in the row's xmax instead of a single XID. Multixacts wrap around the same way and are frozen by the same VACUUM, with their own settings. You check them with mxid_age().

Build a lab table

Step 1: Create the lab database and an orders table

On db1, as your sudo user, create a scratch database:

sudo -u postgres psql -X -c "CREATE DATABASE freezelab;"

CREATE DATABASE

Then open psql in it with sudo -u postgres psql -d freezelab and run the following. The two extensions ship with PostgreSQL 18 (the contrib modules are part of the postgresql-18 package): pg_visibility reads the visibility map, and pageinspect shows raw row headers.

CREATE EXTENSION pg_visibility;
CREATE EXTENSION pageinspect;

CREATE TABLE orders (
    order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id int           NOT NULL,
    amount      numeric(10,2) NOT NULL,
    created_at  timestamptz   NOT NULL DEFAULT now()
);

INSERT INTO orders (customer_id, amount)
SELECT (random() * 999)::int + 1,
       round((random() * 5000)::numeric, 2)
  FROM generate_series(1, 100000);

CREATE EXTENSION
CREATE EXTENSION
CREATE TABLE
INSERT 0 100000

You now have 100,000 rows, all created by a single INSERT, so they all share one transaction ID.

Step 2: Look at the transaction ID stored in each row

In the same psql session, in database freezelab:

SELECT order_id, xmin, age(xmin) AS xmin_age
  FROM orders
 ORDER BY order_id
 LIMIT 3;

 order_id |  xmin  | xmin_age 
----------+--------+----------
        1 | 100776 |        1
        2 | 100776 |        1
        3 | 100776 |        1
(3 rows)

All rows carry the same xmin, the XID of the INSERT. Your number will be different; this test server had already used about 100,000 transactions. xmin_age is 1 because only one transaction has started since. On a busy production server the same rows would be millions of transactions old within days.

Check: SELECT pg_current_xact_id(); returns a number larger than the xmin above.

Check how close you are to wraparound

Step 3: Check every database with age(datfrozenxid)

This is the query to put in your monitoring. Run it in psql as postgres, in any database:

SELECT datname,
       age(datfrozenxid)       AS xid_age,
       mxid_age(datminmxid)    AS mxid_age,
       round(100.0 * age(datfrozenxid)
             / current_setting('autovacuum_freeze_max_age')::int, 2)
                               AS pct_to_forced_vacuum,
       round(100.0 * age(datfrozenxid) / 2147483647, 4)
                               AS pct_to_wraparound
  FROM pg_database
 ORDER BY xid_age DESC;

  datname  | xid_age | mxid_age | pct_to_forced_vacuum | pct_to_wraparound 
-----------+---------+----------+----------------------+-------------------
 postgres  |  100033 |        0 |                 0.05 |            0.0047
 template1 |  100033 |        0 |                 0.05 |            0.0047
 template0 |  100033 |        0 |                 0.05 |            0.0047
 freezelab |  100033 |        0 |                 0.05 |            0.0047
(4 rows)

On a healthy server xid_age sawtooths: it climbs to around 200 million, an anti-wraparound autovacuum freezes the oldest tables, and it drops again. Values steadily climbing past 200 million, and especially past 500 million, mean freezing is being held back and you should find out why (Step 16 shows where to look).

Step 4: Find the oldest tables with age(relfrozenxid)

A database is only as young as its oldest table. In psql, connected to freezelab, use the documentation's query with a size column added. It takes the older of the table and its TOAST table (the side table that stores large values), because both must be frozen:

SELECT c.oid::regclass AS table_name,
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS xid_age,
       mxid_age(c.relminmxid) AS mxid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
  FROM pg_class c
  LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
 WHERE c.relkind IN ('r', 'm')
 ORDER BY xid_age DESC
 LIMIT 5;

      table_name       | xid_age | mxid_age |  size  
-----------------------+---------+----------+--------
 pg_statistic          |  100033 |        0 | 360 kB
 pg_type               |  100033 |        0 | 240 kB
 pg_foreign_table      |  100033 |        0 | 16 kB
 pg_authid             |  100033 |        0 | 72 kB
 pg_statistic_ext_data |  100033 |        0 | 16 kB
(5 rows)

Right now system catalogs top the list because they are as old as the database. On a real server you would see your biggest, least-vacuumed tables here. Run this in each database whose xid_age is high in Step 3; it only sees tables in the current database.

Watch VACUUM freeze rows

Step 5: Run a normal VACUUM and read the freezing lines

In psql, connected to freezelab:

VACUUM (VERBOSE) orders;

SELECT relfrozenxid, age(relfrozenxid) AS xid_age,
       relpages, relallvisible, relallfrozen
  FROM pg_class
 WHERE relname = 'orders';

INFO:  vacuuming "freezelab.public.orders"
INFO:  finished vacuuming "freezelab.public.orders": index scans: 0
pages: 0 removed, 736 remain, 736 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 100000 remain, 0 are dead but not yet removable
removable cutoff: 100777, which was 0 XIDs old when operation ended
new relfrozenxid: 100776, which is 1 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 736 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 4.696 MB/s
buffer usage: 1529 hits, 0 reads, 4 dirtied
WAL usage: 740 records, 4 full page images, 76602 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
 relfrozenxid | xid_age | relpages | relallvisible | relallfrozen 
--------------+---------+----------+---------------+--------------
       100776 |       1 |      736 |           736 |            0
(1 row)

The lines that matter for this part:

• new relfrozenxid: VACUUM scanned every page, so it could advance relfrozenxid to the oldest XID still in the table (the INSERT's XID).
• frozen: 0 pages: nothing was frozen. The rows are only 1 transaction old, far younger than vacuum_freeze_min_age (50 million), so freezing them now would be wasted work if they changed soon.
• visibility map: 736 pages set all-visible, 0 pages set all-frozen: future normal VACUUMs can skip these pages, but an aggressive VACUUM will still have to visit them to freeze them.
• 0 eagerly scanned: eager scanning is new in PostgreSQL 18. A normal VACUUM may now also visit some all-visible but not all-frozen pages to freeze them early, so the next aggressive VACUUM has less to do.

In the second result, relallfrozen is a new column in PostgreSQL 18: the number of pages marked all-frozen. Compare it with relpages to see how much of a table still needs freezing.

Step 6: Freeze everything with VACUUM (FREEZE)

FREEZE is the same as running VACUUM with vacuum_freeze_min_age and vacuum_freeze_table_age set to zero: an aggressive scan that freezes every row it can. In psql, connected to freezelab:

VACUUM (FREEZE, VERBOSE) orders;

SELECT relfrozenxid, age(relfrozenxid) AS xid_age,
       relpages, relallvisible, relallfrozen
  FROM pg_class
 WHERE relname = 'orders';

SELECT * FROM pg_visibility_map_summary('orders');

INFO:  aggressively vacuuming "freezelab.public.orders"
INFO:  finished vacuuming "freezelab.public.orders": index scans: 0
pages: 0 removed, 736 remain, 736 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 100000 remain, 0 are dead but not yet removable
removable cutoff: 100777, which was 0 XIDs old when operation ended
new relfrozenxid: 100777, which is 1 XIDs ahead of previous value
frozen: 736 pages from table (100.00% of total) had 100000 tuples frozen
visibility map: 0 pages set all-visible, 736 pages set all-frozen (736 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 1530 hits, 0 reads, 0 dirtied
WAL usage: 1473 records, 0 full page images, 293730 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
 relfrozenxid | xid_age | relpages | relallvisible | relallfrozen 
--------------+---------+----------+---------------+--------------
       100777 |       0 |      736 |           736 |          736
(1 row)

 all_visible | all_frozen 
-------------+------------
         736 |        736
(1 row)

Now the first line says aggressively vacuuming, all 100,000 tuples were frozen, and all 736 pages are marked all-frozen in the visibility map. relfrozenxid is at the current XID, so the age is 0. From now on every VACUUM can skip these pages completely until a row on them changes.

Notice the cost: 1,473 WAL records for a 6 MB table. Freezing writes to every page it touches, which also produces WAL (the change log that replicas and backups read). That is why PostgreSQL does not freeze young rows by default.

Step 7: See the frozen flag in the row header

In psql, connected to freezelab, read the first two rows of page 0 with pageinspect:

SELECT lp, t_xmin, t_xmax,
       heap_tuple_infomask_flags(t_infomask, t_infomask2) AS flags
  FROM heap_page_items(get_raw_page('orders', 0))
 WHERE lp <= 2;

SELECT order_id, xmin, age(xmin) AS xmin_age
  FROM orders
 ORDER BY order_id
 LIMIT 2;

 lp | t_xmin | t_xmax |                                               flags                                               
----+--------+--------+---------------------------------------------------------------------------------------------------
  1 | 100776 |      0 | ("{HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMIN_INVALID,HEAP_XMAX_INVALID}",{HEAP_XMIN_FROZEN})
  2 | 100776 |      0 | ("{HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMIN_INVALID,HEAP_XMAX_INVALID}",{HEAP_XMIN_FROZEN})
(2 rows)

 order_id |  xmin  | xmin_age 
----------+--------+----------
        1 | 100776 |        1
        2 | 100776 |        1
(2 rows)

The second set of flags contains HEAP_XMIN_FROZEN, which is how frozen is stored (the two bits XMIN_COMMITTED and XMIN_INVALID set together). The t_xmin value is unchanged at 100776, and so is the xmin column you query. So age(xmin) is not a way to check freezing: use relfrozenxid, relallfrozen or pg_visibility_map_summary().

See an autovacuum to prevent wraparound

In production the forced vacuum appears at 200 million transactions. To see one in a minute, lower the limit for one table. The storage parameter autovacuum_freeze_max_age can only be set lower than the server setting, and its minimum is 100,000.

Step 8: Lower the per-table limit and use up 110,000 XIDs

In psql, connected to freezelab. The procedure burn_xids is a lab helper: each loop asks for a transaction ID and commits, so each loop uses one XID. Never create it on a real server.

ALTER TABLE orders SET (autovacuum_freeze_max_age = 100000,
                        log_autovacuum_min_duration = 0);

CREATE PROCEDURE burn_xids(n int)
LANGUAGE plpgsql AS $
BEGIN
  FOR i IN 1..n LOOP
    PERFORM pg_current_xact_id();  -- assigns a new transaction ID
    COMMIT;                        -- ends it, so the next loop gets a new one
  END LOOP;
END $;

CALL burn_xids(110000);

SELECT age(relfrozenxid) AS xid_age
  FROM pg_class
 WHERE relname = 'orders';

ALTER TABLE
CREATE PROCEDURE
CALL
 xid_age 
---------
  110002
(1 row)

The table is now 110,002 transactions old, over its limit of 100,000. Within about a minute (autovacuum_naptime is 60 seconds) the autovacuum launcher notices. Because the table has log_autovacuum_min_duration = 0, the run is logged. On db1, as your sudo user:

sudo grep -A12 "to prevent wraparound" /var/log/postgresql/postgresql-18-main.log

2026-11-06 12:54:38.822 UTC [2950] LOG:  automatic vacuum to prevent wraparound of table "freezelab.public.orders": index scans: 0
	pages: 0 removed, 736 remain, 1 scanned (0.14% of total), 0 eagerly scanned
	tuples: 0 removed, 100000 remain, 0 are dead but not yet removable
	removable cutoff: 210779, which was 0 XIDs old when operation ended
	new relfrozenxid: 210779, which is 110002 XIDs ahead of previous value
	frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
	visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
	index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
	avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
	buffer usage: 57 hits, 0 reads, 0 dirtied
	WAL usage: 1 records, 0 full page images, 351 bytes, 0 buffers full
	system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2026-11-06 12:54:38.869 UTC [2950] LOG:  automatic analyze of table "freezelab.public.orders"

automatic vacuum to prevent wraparound is the anti-wraparound autovacuum. It scanned only 1 of 736 pages because Step 6 had marked every page all-frozen, and still advanced relfrozenxid by 110,002. This is why regular freezing matters: a forced vacuum on a well-frozen table is cheap, and one on a never-frozen 500 GB table reads all of it.

While such a run is in progress, its query in pg_stat_activity ends with (to prevent wraparound). Unlike a normal autovacuum, it is not automatically cancelled when another session needs a conflicting lock, so an ALTER TABLE or a TRUNCATE can queue behind it. Do not terminate it: it will simply start again, from the beginning, a minute later.

Check: in psql, connected to freezelab:

SELECT age(relfrozenxid) AS xid_age, last_autovacuum
  FROM pg_class c
  JOIN pg_stat_user_tables s ON s.relid = c.oid
 WHERE c.relname = 'orders';

 xid_age |        last_autovacuum        
---------+-------------------------------
       1 | 2026-11-06 12:54:38.822574+00
(1 row)

Step 9: Undo the lab settings

In psql, connected to freezelab, remove the two storage parameters so the table uses the server settings again:

ALTER TABLE orders RESET (autovacuum_freeze_max_age, log_autovacuum_min_duration);

ALTER TABLE

Check: SELECT reloptions FROM pg_class WHERE relname = 'orders'; returns NULL.

Lab: reproduce a wraparound emergency safely

Wraparound emergencies are rare, so most DBAs see the messages for the first time during an outage. In this lab you build a second, disposable cluster called wraplab on port 5433, leave a forgotten prepared transaction in it (a classic real-world cause), and move its transaction counter close to the limit with pg_resetwal.

Step 10: Create the disposable wraplab cluster

On db1, as your sudo user. max_prepared_transactions is 0 by default, which disables prepared transactions, and it can only be set at server start, so set it before starting the cluster:

sudo pg_createcluster 18 wraplab --port 5433
echo "max_prepared_transactions = 5" | sudo tee /etc/postgresql/18/wraplab/conf.d/lab.conf
sudo pg_ctlcluster 18 wraplab start
pg_lsclusters

Creating new PostgreSQL cluster 18/wraplab ...
/usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/wraplab --auth-local peer --auth-host scram-sha-256 --no-instructions
...
max_prepared_transactions = 5
Ver Cluster Port Status Owner    Data directory                 Log file
18  main    5432 online postgres /var/lib/postgresql/18/main    /var/log/postgresql/postgresql-18-main.log
18  wraplab 5433 online postgres /var/lib/postgresql/18/wraplab /var/log/postgresql/postgresql-18-wraplab.log

Both clusters are online. The Ubuntu packaging reads every file in conf.d, so lab.conf is picked up.

Step 11: Leave a forgotten prepared transaction

A prepared transaction (PREPARE TRANSACTION) is the first half of a two-phase commit, used by external transaction managers. It survives disconnects and restarts until someone runs COMMIT PREPARED or ROLLBACK PREPARED. While it exists, VACUUM cannot freeze anything newer than its XID. The documentation warns that leaving one for a long time can, in extreme cases, cause the database to stop to prevent wraparound. Connect with sudo -u postgres psql -p 5433 and run:

CREATE TABLE payments (
    payment_id int PRIMARY KEY,
    amount     numeric(10,2) NOT NULL
);

BEGIN;
INSERT INTO payments VALUES (1, 499.00);
PREPARE TRANSACTION 'pay_batch_42';

SELECT gid, transaction, prepared FROM pg_prepared_xacts;

CREATE TABLE
BEGIN
INSERT 0 1
PREPARE TRANSACTION
     gid      | transaction |           prepared           
--------------+-------------+------------------------------
 pay_batch_42 |         753 | 2026-11-06 12:56:12.58082+00
(1 row)

XID 753 is now pinned. Quit psql and stop the cluster, as your sudo user:

sudo pg_ctlcluster 18 wraplab stop

Step 12: Fast-forward the transaction counter (lab only)

On db1, as your sudo user. First a dry run with -n, which prints the current and new values without changing anything:

sudo ls /var/lib/postgresql/18/wraplab/pg_xact
sudo -u postgres /usr/lib/postgresql/18/bin/pg_resetwal -n -x 0x7FD00000 \
    /var/lib/postgresql/18/wraplab | grep -i "NextXID\|oldestXID:\|Values to be changed"

0000
Latest checkpoint's NextXID:          0:754
Latest checkpoint's oldestXID:        744
Values to be changed:
NextXID:                              2144337920

Then do it for real, create the commit status file for the new XID range, and start the cluster:

sudo -u postgres /usr/lib/postgresql/18/bin/pg_resetwal -x 0x7FD00000 /var/lib/postgresql/18/wraplab
sudo -u postgres dd if=/dev/zero of=/var/lib/postgresql/18/wraplab/pg_xact/07FD bs=256k count=1 status=none
sudo pg_ctlcluster 18 wraplab start

Write-ahead log reset

The server log, /var/log/postgresql/postgresql-18-wraplab.log, already shows the first warning at startup:

WARNING:  database with OID 1 must be vacuumed within 3146471 transactions
HINT:  To avoid XID assignment failures, execute a database-wide VACUUM in that database.
	You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
LOG:  recovering prepared transaction 753 from shared memory
LOG:  database system is ready to accept connections

Step 13: See the wraparound WARNING

Connect with sudo -u postgres psql -p 5433 and run:

INSERT INTO payments VALUES (2, 120.00);

SELECT datname, age(datfrozenxid) AS xid_age,
       2147483647 - age(datfrozenxid) AS xids_left
  FROM pg_database
 ORDER BY xid_age DESC;

WARNING:  database "template1" must be vacuumed within 3146471 transactions
HINT:  To avoid transaction ID assignment failures, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
INSERT 0 1
  datname  |  xid_age   | xids_left 
-----------+------------+-----------
 postgres  | 2144337177 |   3146470
 template1 | 2144337177 |   3146470
 template0 | 2144337177 |   3146470
(3 rows)

Every statement that takes a new XID now prints this WARNING, in the client and in the server log. All three databases have the same age because the prepared transaction holds them all back; the message names template1 (OID 1). In production, this is the moment to act. Writes still work.

Step 14: Hit the write stop

Still in psql on port 5433, use up the remaining XIDs. client_min_messages = error hides the 146,000 warnings from your screen, but every one still goes to the server log, which grows to about 80 MB.

CREATE PROCEDURE burn_xids(n int)
LANGUAGE plpgsql AS $
BEGIN
  FOR i IN 1..n LOOP
    PERFORM pg_current_xact_id();
    COMMIT;
  END LOOP;
END $;

SET client_min_messages = error;
CALL burn_xids(150000);
RESET client_min_messages;

INSERT INTO payments VALUES (3, 75.00);
SELECT count(*) FROM payments;

WARNING:  database "template1" must be vacuumed within 3146470 transactions
HINT:  To avoid transaction ID assignment failures, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
CREATE PROCEDURE
SET
ERROR:  database is not accepting commands that assign new transaction IDs to avoid wraparound data loss in database "template1"
HINT:  Execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
CONTEXT:  SQL statement "SELECT pg_current_xact_id()"
PL/pgSQL function burn_xids(integer) line 4 at PERFORM
RESET
ERROR:  database is not accepting commands that assign new transaction IDs to avoid wraparound data loss in database "template1"
HINT:  Execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
 count 
-------
     1
(1 row)

This is the documented error. PostgreSQL stopped 3 million transactions short of wraparound. In this state, transactions already in progress can continue and read-only queries work (the SELECT returned), but anything that needs a new XID fails. Note that the payments count is 1: row 1 belongs to the prepared transaction, which was never committed.

Recover, in the order the documentation gives

The documentation lists four steps: resolve old prepared transactions, end long-running transactions, drop stale replication slots, then VACUUM. The first three remove whatever is stopping VACUUM from freezing; without them, VACUUM runs and nothing improves.

Step 15: Resolve old prepared transactions

On the wraplab cluster, in psql as postgres:

SELECT gid, database, owner, prepared,
       age(transaction) AS xid_age
  FROM pg_prepared_xacts
 ORDER BY age(transaction) DESC;

     gid      | database |  owner   |           prepared           |  xid_age   
--------------+----------+----------+------------------------------+------------
 pay_batch_42 | postgres | postgres | 2026-11-06 12:56:12.58082+00 | 2144483638
(1 row)

A prepared transaction 2.1 billion XIDs old is the blocker. In real life, first ask the team that owns the transaction manager whether it should be committed or rolled back. Here it is abandoned, so roll it back. ROLLBACK PREPARED does not need a new XID, so it works even in this state:

ROLLBACK PREPARED 'pay_batch_42';

ROLLBACK PREPARED

Check: SELECT count(*) FROM pg_prepared_xacts; returns 0.

Step 16: Check long-running sessions and replication slots

In psql on port 5433, look for sessions holding an old XID or snapshot (see Part 10 for terminating sessions) and for replication slots holding back xmin:

SELECT pid, datname, usename, state, xact_start,
       age(backend_xid)  AS xid_age,
       age(backend_xmin) AS xmin_age
  FROM pg_stat_activity
 WHERE pid <> pg_backend_pid()
   AND (backend_xid IS NOT NULL OR backend_xmin IS NOT NULL)
 ORDER BY greatest(age(backend_xid), age(backend_xmin)) DESC;

SELECT slot_name, slot_type, active,
       age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_xmin_age
  FROM pg_replication_slots;

 pid | datname | usename | state | xact_start | xid_age | xmin_age 
-----+---------+---------+-------+------------+---------+----------
(0 rows)

 slot_name | slot_type | active | xmin_age | catalog_xmin_age 
-----------+-----------+--------+----------+------------------
(0 rows)

Both are empty in the lab. In production, commit, roll back or terminate (pg_terminate_backend(pid)) any session with a huge xid_age or xmin_age, and drop slots with a huge xmin_age that belong to replicas that no longer exist (SELECT pg_drop_replication_slot('slot_name');). The documentation warns that if you drop a slot for a replica that still exists, that replica may need to be rebuilt.

Step 17: VACUUM every database

On db1, as your sudo user. vacuumdb runs a plain VACUUM in every database you can connect to. It must run as a superuser, otherwise VACUUM skips system catalogs and cannot advance datfrozenxid:

sudo -u postgres vacuumdb --all --port 5433

vacuumdb: vacuuming database "postgres"
WARNING:  database "template1" must be vacuumed within 3000000 transactions
HINT:  To avoid XID assignment failures, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
vacuumdb: vacuuming database "template1"
WARNING:  bypassing nonessential maintenance of table "template1.pg_catalog.pg_proc" as a failsafe after 0 index scans
DETAIL:  The table's relfrozenxid or relminmxid is too far in the past.
HINT:  Consider increasing configuration parameter "maintenance_work_mem" or "autovacuum_work_mem".
You might also need to consider other ways for VACUUM to keep up with the allocation of transaction IDs.
WARNING:  bypassing nonessential maintenance of table "template1.pg_toast.pg_toast_1255" as a failsafe after 0 index scans
...
WARNING:  database "template0" must be vacuumed within 3000000 transactions
HINT:  To avoid XID assignment failures, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

Two things to read here. First, bypassing nonessential maintenance ... as a failsafe: this is the vacuum failsafe. When a table's age passes vacuum_failsafe_age (default 1.6 billion), VACUUM stops cost-based throttling and skips index cleanup to finish freezing as fast as possible. Second, by the time vacuumdb reached database postgres, autovacuum had already fixed it: anti-wraparound autovacuum is always on, and Step 15 removed its blocker.

The last warning names template0. It does not allow connections, so vacuumdb skips it, but autovacuum processes it for wraparound. Wait a minute and check, in psql on port 5433:

SELECT datname, datallowconn, age(datfrozenxid) AS xid_age
  FROM pg_database
 ORDER BY xid_age DESC;

INSERT INTO payments VALUES (3, 75.00);
SELECT * FROM payments ORDER BY payment_id;

  datname  | datallowconn | xid_age 
-----------+--------------+---------
 postgres  | t            |  146471
 template1 | t            |       0
 template0 | f            |       0
(3 rows)

INSERT 0 1
 payment_id | amount 
------------+--------
          2 | 120.00
          3 |  75.00
(2 rows)

Every database is young again and the INSERT works. Do not use VACUUM FULL here (it needs a new XID, so it fails) or VACUUM FREEZE (it does far more work than needed); the documentation warns against both in this situation. Once writes work, review autovacuum settings so it cannot happen again (Part 12).

Step 18: Remove the lab

On db1, as your sudo user:

sudo pg_dropcluster 18 wraplab --stop
sudo rm -f /var/log/postgresql/postgresql-18-wraplab.log
pg_lsclusters
sudo -u postgres psql -X -c "DROP DATABASE freezelab;"

Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log
DROP DATABASE

Only 18 main remains.

Settings that control freezing

Run this in psql on 18 main to see your current values:

SELECT name, setting, unit, context
  FROM pg_settings
 WHERE name IN ('autovacuum_freeze_max_age', 'autovacuum_multixact_freeze_max_age',
                'vacuum_freeze_min_age', 'vacuum_freeze_table_age',
                'vacuum_multixact_freeze_min_age', 'vacuum_multixact_freeze_table_age',
                'vacuum_failsafe_age', 'vacuum_multixact_failsafe_age',
                'vacuum_max_eager_freeze_failure_rate')
 ORDER BY name;

                 name                 |  setting   | unit |  context   
--------------------------------------+------------+------+------------
 autovacuum_freeze_max_age            | 200000000  |      | postmaster
 autovacuum_multixact_freeze_max_age  | 400000000  |      | postmaster
 vacuum_failsafe_age                  | 1600000000 |      | user
 vacuum_freeze_min_age                | 50000000   |      | user
 vacuum_freeze_table_age              | 150000000  |      | user
 vacuum_max_eager_freeze_failure_rate | 0.03       |      | user
 vacuum_multixact_failsafe_age        | 1600000000 |      | user
 vacuum_multixact_freeze_min_age      | 5000000    |      | user
 vacuum_multixact_freeze_table_age    | 150000000  |      | user
(9 rows)

The context column tells you how to apply a change (Part 5): postmaster needs sudo systemctl restart postgresql@18-main; the others can be set in /etc/postgresql/18/main/postgresql.conf and applied with SELECT pg_reload_conf();.

Should you raise autovacuum_freeze_max_age? The documentation says the only downside of a higher value is disk space: pg_xact keeps commit status back to that horizon, about 50 MB at the default and about half a gigabyte at the maximum of 2 billion (pg_commit_ts can grow to about 2 GB and 20 GB if track_commit_timestamp is on). Raising it means fewer forced vacuums, but each one has more to do, and you get less warning time.

For big tables that only receive INSERTs, the documentation suggests lowering the table's autovacuum_freeze_min_age so the insert-triggered autovacuums freeze rows early, while each page is still in memory. In psql, as an example on a new table:

CREATE TABLE audit_log (
    log_id    bigint GENERATED ALWAYS AS IDENTITY,
    logged_at timestamptz NOT NULL DEFAULT now(),
    message   text
);

ALTER TABLE audit_log SET (autovacuum_freeze_min_age = 1000000);

SELECT relname, reloptions
  FROM pg_class
 WHERE relname = 'audit_log';

CREATE TABLE
ALTER TABLE
  relname  |             reloptions              
-----------+-------------------------------------
 audit_log | {autovacuum_freeze_min_age=1000000}
(1 row)

To undo it: ALTER TABLE audit_log RESET (autovacuum_freeze_min_age);

Common mistakes and how to fix them

WARNING: database "mydb" must be vacuumed within N transactions

This is the 40-million warning. Do not wait for the error. Follow Steps 15 to 17 now: find old prepared transactions, long-running sessions and stale replication slots, remove them, then VACUUM the named database as a superuser. A non-superuser VACUUM skips system catalogs and cannot advance datfrozenxid.

ERROR: database is not accepting commands that assign new transaction IDs to avoid wraparound data loss

Writes are stopped. Same fix, in the same order. Do not restart into single-user mode, and do not run VACUUM FULL or VACUUM FREEZE. If autovacuum is already running "(to prevent wraparound)" on the table, let it finish; it has the failsafe enabled.

Cancelling or killing an autovacuum "(to prevent wraparound)"

It holds a lock that blocks DDL such as ALTER TABLE, so it is tempting to terminate it. It will restart a minute later and scan from the beginning. Let it run, and schedule DDL after it. To make these runs short, keep tables frozen with regular VACUUM so most pages are already all-frozen, as Step 8 showed.

Turning autovacuum off to stop it "using resources"

Anti-wraparound autovacuum still runs, but now on a huge backlog, at a time you did not choose. Keep autovacuum = on and tune it instead (Part 12).

ERROR: prepared transactions are disabled

PostgreSQL adds the hint Set "max_prepared_transactions" to a nonzero value. You see this if you run PREPARE TRANSACTION while max_prepared_transactions = 0 (the default). If your application does not use two-phase commit, leave it at 0: that makes forgotten prepared transactions impossible.

Checking age(xmin) on rows to see if they are frozen

As Step 7 showed, freezing keeps the original xmin, so age(xmin) keeps growing. Check relfrozenxid, relallfrozen or pg_visibility_map_summary() instead.

Practice exercise

1. On 18 main, list the three databases with the highest age(datfrozenxid) and show what percentage of autovacuum_freeze_max_age each has used.
2. Create a table with 10,000 rows, run a normal VACUUM, and use pg_visibility_map_summary() to show that no page is all-frozen. Then make every page all-frozen without using the FREEZE option.

Quick reference

# Age of every database (run on 18 main)
sudo -u postgres psql -c "SELECT datname, age(datfrozenxid), mxid_age(datminmxid) FROM pg_database ORDER BY 2 DESC;"

# Oldest tables in one database (include TOAST)
sudo -u postgres psql -d mydb -c "SELECT c.oid::regclass, greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS xid_age FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind IN ('r','m') ORDER BY 2 DESC LIMIT 10;"

# Freezing details for one table
sudo -u postgres psql -d mydb -c "VACUUM (VERBOSE) orders;"
sudo -u postgres psql -d mydb -c "SELECT relpages, relallvisible, relallfrozen FROM pg_class WHERE relname = 'orders';"

# Freeze a table completely
sudo -u postgres psql -d mydb -c "VACUUM (FREEZE, VERBOSE) orders;"

# Running anti-wraparound autovacuums
sudo -u postgres psql -c "SELECT pid, datname, query FROM pg_stat_activity WHERE query LIKE '%to prevent wraparound%';"

# Emergency step 1: old prepared transactions
sudo -u postgres psql -c "SELECT gid, database, age(transaction) FROM pg_prepared_xacts ORDER BY 3 DESC;"

# Emergency step 2: old transactions and snapshots
sudo -u postgres psql -c "SELECT pid, datname, age(backend_xid), age(backend_xmin) FROM pg_stat_activity WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL;"

# Emergency step 3: replication slots holding xmin
sudo -u postgres psql -c "SELECT slot_name, active, age(xmin), age(catalog_xmin) FROM pg_replication_slots;"

# Emergency step 4: VACUUM every database as superuser
sudo -u postgres vacuumdb --all

Summary and what's next

• Transaction IDs are 32 bits; a row more than about 2 billion transactions old would look like it is in the future.
• VACUUM freezes old rows by setting a flag; the original xmin is kept.
• Monitor age(datfrozenxid) per database and age(relfrozenxid) per table; investigate anything climbing well past 200 million.
• Aggressive VACUUM starts at vacuum_freeze_table_age (150 million); anti-wraparound autovacuum at autovacuum_freeze_max_age (200 million), even with autovacuum off.
• Never cancel an autovacuum "(to prevent wraparound)".
• At 40 million XIDs left you get WARNINGs; at 3 million, writes stop.
• Recover in order: prepared transactions, long-running sessions, stale replication slots, then a superuser VACUUM of each database. No single-user mode, no VACUUM FULL, no VACUUM FREEZE.

In Part 15 we look at WAL: the write-ahead log, wal_level and WAL segments.