Transaction ID Wraparound in PostgreSQL: What Freezing Actually Does

Transaction ID (XID) wraparound is a PostgreSQL failure mode that frequently catches capable engineering teams off guard. It does not occur because the concept is undocumented; almost every database administrator is familiar with the basic warnings. Instead, it catches people by surprise because the warning signs remain quiet for long periods, and when alerts finally trigger, the instinctive remedies are often the wrong ones.

The typical symptom bringing engineers to this problem is puzzling. Autovacuum is enabled and running. Perhaps someone has even executed a manual VACUUM on the table, which completed without error. Yet, age(relfrozenxid) continues to rise week after week, never returning to baseline.

Under pressure, the immediate instinct is often to execute heavier commands, such as VACUUM FREEZE or, during critical moments, VACUUM FULL. However, neither solves the core issue, and one of them can actively worsen the situation.

• The actual resolution requires running three diagnostic queries and executing one targeted administrative action, which are detailed in Section 0 below.
• If you are dealing with an active production emergency, start there.
• The subsequent sections explain the underlying mechanics: what freezing actually writes to disk, and the specific rules that dictate whether a vacuum operation is allowed to write anything at all.

Understanding these concepts is the key to preventing the issue from returning.

0. Start Here — Triage First, Theory Later

If your database is in trouble right now, you should not have to read a long architectural article to resolve it. Below is the immediate diagnostic path. The rest of this guide explains why these steps work.

The Three Diagnostics

If age(relfrozenxid) is climbing and vacuuming does not bring it down, an active database state is holding a snapshot open and blocking the freeze operation. There are only three places this state can exist. Run all three of these queries to locate it:

-- 1. Long-running or idle-in-transaction sessions
SELECT pid, state, xact_start, backend_xid, backend_xmin, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

-- 2. Replication slots (active = false with a high age indicates a dead slot)
SELECT slot_name, active, xmin, catalog_xmin,
       age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots
ORDER BY greatest(age(xmin), age(catalog_xmin)) DESC NULLS LAST;

-- 3. Prepared transactions (these are invisible in pg_stat_activity)
SELECT gid, prepared, owner, database, age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;

Identifying the Culprit

Review the query output for any entry with an unusually high age (such as tens or hundreds of millions of transactions). The most common culprit is an inactive replication slot — often created for a replica or consumer that was decommissioned months ago but never dropped from the primary database.

Resolution Sequence

1. Remove the Blocker:
   • For a lingering session, execute: SELECT pg_terminate_backend(pid);
   • For an orphaned replication slot, execute: SELECT pg_drop_replication_slot('slot_name');
   • For an abandoned two-phase commit transaction, execute: ROLLBACK PREPARED 'gid'; (or COMMIT PREPARED 'gid' if appropriate).
2. Execute Vacuum: Run a standard VACUUM on the affected table or database. You do not need complex parameters.
3. Verify Age Reduction: Re-check the transaction age. It should drop, often by hundreds of millions of transactions in a single pass.

⚠️ The order of these steps is critical. Running a vacuum before clearing the blocker achieves nothing. The vacuum will complete without error and report success, but it will freeze zero tuples. Section 3.5 explains why this happens.

⚠️ Do not use VACUUM FULL. It does not resolve the issue, it requests an ACCESS EXCLUSIVE lock, and if transaction ID allocation has already stopped, it cannot run because it requires a new transaction ID. Running it as a superuser consumes one of your few remaining available transaction IDs. See Section 7.

1. The Transaction Counter is Circular, Not Linear

Every database write operation is assigned a transaction ID. PostgreSQL writes this XID into the tuple header as the t_xmin value, which acts as the row's creation stamp. This counter increases monotonically. PostgreSQL does not recycle these IDs or return them to a pool, and vacuuming does not decrease the global counter.

The structural limitation is that t_xmin is stored in a 32-bit field, which provides roughly 4.2 billion unique values before wrapping around. To handle this, PostgreSQL uses modular arithmetic. From the database's current counter position, approximately two billion older transaction IDs represent the past, and approximately two billion IDs represent the future.

This design introduces the potential for wraparound issues. A row created years ago remains at a fixed point on this numerical circle. As the active pointer continues to advance, it eventually moves more than halfway around the circle and laps the old row. At that moment, the comparison logic that previously evaluated the row as being in the past (making it visible) now evaluates it as being in the future (making it not yet committed).

⚠️ Clarifying a Common Misconception: Transaction ID wraparound does not cause physical data corruption. The bytes on disk remain intact. Instead, the rows simply disappear from query results because committed data becomes invisible to the database engine. This is highly problematic because the system continues to run without immediate errors. Additionally, the two-billion limit refers to the comparative comparison window, not to the number of concurrent active transactions.

2. Visibility Mechanics — How Two Crucial Questions Expire

To understand what freezing does, we must define what visibility means. The operational realities of wraparound — such as vacuums completing successfully without freezing rows, or write prevention behavior — stem from these basic principles.

In PostgreSQL, visibility is distinct from physical existence on disk. A page on disk can hold live rows, rows from active transactions, aborted rows, and old versions of rows modified by updates. The physical bytes look identical. For every row accessed by a query, PostgreSQL must evaluate whether that row is visible to that specific session's snapshot.

To make this decision, the engine evaluates two questions:

1. Did the transaction that created this row successfully commit? If it aborted, the row is treated as garbage and ignored.
2. Did it commit before my current query snapshot was taken? When a query begins, PostgreSQL establishes a consistent view of the database. Anything committed after that moment is hidden to keep data consistent during query execution.

Both of these answers eventually expire.

+-------------------------------------------------------------------------+
| QUESTION 1: Did the transaction that created this row commit?          |
|-------------------------------------------------------------------------|
| Answered by: A lookup in pg_xact (the commit log).                     |
| How it expires: pg_xact is truncated. Old segments are physically       |
| deleted to save space. Once deleted, the lookup record no longer exists.|
+-------------------------------------------------------------------------+

+-------------------------------------------------------------------------+
| QUESTION 2: Did it commit before my snapshot was taken?                 |
|-------------------------------------------------------------------------|
| Answered by: Comparing the two XIDs on the circular number line.        |
| How it expires: The global counter wraps. If the counter moves too far, |
| the ancient row appears to have been created in the future.             |
+-------------------------------------------------------------------------+

Wraparound occurs when the second question yields the wrong answer. The row is undamaged, but the comparison logic assumes the transaction is in the future and hides it which means not visible — and a SELECT silently stops returning committed data. Hold onto this: the failure mode of wraparound is invisibility, not corruption.

And now the whole of freezing can be stated in a single line. Hold onto it, because everything that follows is just the mechanics of it:

What Freezing Achieves

‍Freezing removes both of these questions permanently, before either answer can expire.

Rather than resolving the questions faster, the database removes them from the evaluation path. A row that does not require these checks is safe from visibility loss. The next section details how PostgreSQL implements this mechanism on disk.

3. What Freezing Actually Writes to Disk

To understand how freezing operates in production, we must examine the tuple header and the PostgreSQL source code. This explains why certain vacuums fail to freeze rows and why freezing sometimes generates substantial write volume.

3.1 The Three Reserved Transaction IDs

PostgreSQL reserves specific values at the bottom of the transaction ID space (defined in src/include/access/transam.h):

#define InvalidTransactionId        ((TransactionId) 0)
#define BootstrapTransactionId      ((TransactionId) 1)
#define FrozenTransactionId         ((TransactionId) 2)
#define FirstNormalTransactionId    ((TransactionId) 3)

Real user transactions begin at ID 3. Transaction IDs 0, 1, and 2 function as internal indicators rather than actual transaction numbers, exempting them from standard XID rules.

The relevant identifier is FrozenTransactionId (value 2). This ID is exempt from modular circular comparisons and is treated as unconditionally older than any standard XID. (XID 1 marks system catalog rows written during initial bootstrap, and XID 0 represents a null or uncommitted transaction state).

Two logic blocks in transam.c implement this behavior. The first bypasses the pg_xact lookup:

TransactionLogFetch(TransactionId transactionId)
{
    /* Also, check to see if the transaction ID is a permanent one. */
    if (!TransactionIdIsNormal(transactionId))
    {
        if (TransactionIdEquals(transactionId, BootstrapTransactionId))
            return TRANSACTION_STATUS_COMMITTED;
        if (TransactionIdEquals(transactionId, FrozenTransactionId))
            return TRANSACTION_STATUS_COMMITTED;
        return TRANSACTION_STATUS_ABORTED;
    }
    /* ... normal XIDs fall through and actually read pg_xact ... */
}

For reserved XIDs 1 and 2, the engine skips the check against pg_xact and immediately returns COMMITTED. This eliminates the need for the first visibility question.

The second logic block handles the circular comparison:

TransactionIdPrecedes(TransactionId id1, TransactionId id2)
{
    /*
     * If either ID is a permanent XID then we can just do unsigned
     * comparison.  If both are normal, do a modulo-2^32 comparison.
     */
    int32       diff;

    if (!TransactionIdIsNormal(id1) || !TransactionIdIsNormal(id2))
        return (id1 < id2);        /* PLAIN — a straight line */

    diff = (int32) (id1 - id2);    /* CIRCULAR — this is what wraps */
    return (diff < 0);
}

If either value is a reserved identifier, the engine switches from circular comparison to a linear comparison. Because 2 is mathematically smaller than any normal XID starting at 3, a frozen row is treated as unconditionally older than any active transaction. This prevents it from being lapped on the circular timeline.

3.2 The Commit Log (pg_xact) and Hint Bits

To understand freezing, we must examine how transaction statuses are stored. A tuple's t_xmin records which transaction created it, but it does not record whether that transaction committed. That commit status resides in the commit log, stored in $PGDATA/pg_xact/ (previously known as pg_clog).

According to clog.c and clog.h, the status is encoded using two bits per transaction:

#define CLOG_BITS_PER_XACT   2
#define CLOG_XACTS_PER_BYTE  4

#define TRANSACTION_STATUS_IN_PROGRESS     0x00
#define TRANSACTION_STATUS_COMMITTED       0x01
#define TRANSACTION_STATUS_ABORTED         0x02
#define TRANSACTION_STATUS_SUB_COMMITTED   0x03

This 2-bit structure makes pg_xact very compact. One million transactions require only 256 KB of storage, and the entire two-billion XID window fits into roughly 512 MB.

This architecture has two notable properties:

• Commit is a Constant-Time Operation: The database writes commit statuses once per transaction, not once per row. All rows created by a transaction reference the same transaction ID. When you commit, PostgreSQL modifies the two bits representing that transaction in pg_xact and returns immediately. It does not update the individual rows.
• Uncommitted Data is Written Directly to Disk: PostgreSQL writes inserted rows to data pages and Write-Ahead Logs (WAL) before a commit occurs. If a transaction aborts, the rows are not deleted; instead, the status in pg_xact is updated to ABORTED, and standard vacuuming reclaims the space later.

Why Freezing is Mandatory for Truncation

The database cannot grow pg_xact indefinitely. It truncates older segments once the rows depending on those transactions have been frozen:

If an ancient row is never frozen, but its commit record in pg_xact is truncated, its visibility can no longer be determined. To prevent this unresolvable state, PostgreSQL stops accepting writes before you cross the horizon.

⚠ Freezing is what gives PostgreSQL permission to forget. Now put the two facts together. A normal row's visibility requires a pg_xact lookup. And pg_xact entries are deleted once freezing has advanced past them. So a row that is never frozen, whose commit record is truncated anyway, becomes unanswerable. The row says "ask about transaction 4,500,000,300" and the record is gone. There is no way left to determine whether it is real data or garbage. That is the real reason PostgreSQL refuses writes rather than let you cross the line — it is not being cautious, it is refusing to create rows whose visibility cannot be determined.

Hint Bits

To avoid querying pg_xact for every row visibility check, PostgreSQL caches the commit status on the tuple itself in the t_infomask field using hint bits:

#define HEAP_XMIN_COMMITTED     0x0100  /* t_xmin committed */
#define HEAP_XMIN_INVALID       0x0200  /* t_xmin invalid/aborted */
#define HEAP_XMAX_COMMITTED     0x0400  /* t_xmax committed */
#define HEAP_XMAX_INVALID       0x0800  /* t_xmax invalid/aborted */

The first read operation that retrieves a row queries pg_xact, updates these bits in the row header, and writes the change back to disk. Subsequent reads use these cached bits and skip the pg_xact lookup. (This is why a read-only SELECT query can sometimes generate disk write I/O on fresh data).

Row Lifecycle Summary

The table below illustrates the changes to a row's header fields during its lifecycle:

One caveat on "visible forever". Freezing settles the question of the row's birth — who created it, and whether they committed. It says nothing about its death. A frozen row can still be deleted: it gets an xmax, becomes a dead tuple, and vacuum reclaims it like any other. Freezing does not make a row immortal. It makes a row's origin unquestionable.

3.3 The Overlapping Bit-Pair Sentinel

To represent a frozen state without using extra storage, PostgreSQL combines two opposing flags:

#define HEAP_XMIN_FROZEN        (HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID)

#define HeapTupleHeaderXminFrozen(tup) \
(   ((tup)->t_infomask & (HEAP_XMIN_FROZEN)) == HEAP_XMIN_FROZEN \
)

Because a transaction cannot be both committed and aborted, setting both HEAP_XMIN_COMMITTED and HEAP_XMIN_INVALID is a logical impossibility. PostgreSQL uses this overlapping state as a sentinel meaning frozen.

This allows the engine to freeze a row by updating the flags in t_infomask to 0x0300 while keeping the original t_xmin on disk.

Look at the bottom two rows and compare them against the two questions from §2. That is the entire point: a frozen tuple does not answer the questions faster — it stops being asked them. And a question that is never asked can never be answered wrongly.

ℹ️ Version Change: Prior to version 9.4, freezing physically replaced the t_xmin value with 2. Since 9.4, it sets the flag bits instead, preserving the original XID for auditing. If you see an xmin of 2 on a modern cluster, it is likely the result of an upgrade from a pre-9.4 database.

⚠ THE TRAP THIS SETS FOR YOU

You cannot find frozen rows by looking for xmin = 2. On any modern cluster a frozen row still displays its original XID. Write a monitoring query that hunts for 2 and it will find almost nothing — and you will wrongly conclude the table is not frozen. The real test is the flag bit.

To verify this on a test table, you can inspect the raw page flags using the pageinspect extension:

CREATE EXTENSION IF NOT EXISTS pageinspect;

SELECT lp,
       t_xmin,
       to_hex(t_infomask)       AS infomask_hex,
       (t_infomask & 256) <> 0  AS xmin_committed,  -- 0x0100
       (t_infomask & 512) <> 0  AS xmin_invalid,    -- 0x0200
       (t_infomask & 768) = 768 AS is_frozen        -- 0x0300, both bits
FROM heap_page_items(get_raw_page('your_table', 0));

If you run this query, execute VACUUM (FREEZE) your_table;, and query it again, you will see is_frozen transition to true while the physical t_xmin remains unchanged.

3.4 Freezing both xmin and xmax

A live tuple can also contain an xmax value if a transaction attempted to delete it but aborted, or if it was locked. These XIDs also age and must be processed. The freeze logic in heapam.c handles this:

static inline void
heap_execute_freeze_tuple(HeapTupleHeader tuple, HeapTupleFreeze *frz)
{
    HeapTupleHeaderSetXmax(tuple, frz->xmax);

    if (frz->frzflags & XLH_FREEZE_XVAC)
        HeapTupleHeaderSetXvac(tuple, FrozenTransactionId);

    if (frz->frzflags & XLH_INVALID_XVAC)
        HeapTupleHeaderSetXvac(tuple, InvalidTransactionId);

    tuple->t_infomask  = frz->t_infomask;
    tuple->t_infomask2 = frz->t_infomask2;
}

The planning phase (heap_prepare_freeze_tuple) evaluates four possible conditions for a freeze: freeze_xmin, replace_xvac, replace_xmax, or freeze_xmax. This is why MultiXact age has its own counter and can trigger its own wraparound issues even if the standard relfrozenxid looks healthy.

3.5 The Two Cutoffs: FreezeLimit vs. OldestXmin

PostgreSQL uses two distinct cutoffs to manage freezing:

/* vacuum.c -- computing the cutoffs */
cutoffs->OldestXmin  = GetOldestNonRemovableTransactionId(rel);
...
freeze_min_age       = Min(freeze_min_age, autovacuum_freeze_max_age / 2);
cutoffs->FreezeLimit = nextXID - freeze_min_age;

/* FreezeLimit must always be <= OldestXmin */
if (TransactionIdPrecedes(cutoffs->OldestXmin, cutoffs->FreezeLimit))
    cutoffs->FreezeLimit = cutoffs->OldestXmin;

/* heapam.c -- deciding whether to freeze this tuple's xmin */
freeze_xmin = TransactionIdPrecedes(xid, cutoffs->OldestXmin);

These values serve different purposes:

• FreezeLimit: Calculated using vacuum_freeze_min_age from your configuration. It is a target deadline representing how far back vacuuming should aim to freeze rows.
• OldestXmin: A dynamic value representing the oldest transaction ID still required by any active database session, replication slot, or transaction.

As shown in the code, the engine clamps FreezeLimit so it never exceeds OldestXmin. The actual decision to freeze a tuple is evaluated against OldestXmin.

If an active state (such as an idle transaction or a replication slot) holds OldestXmin in the past, any row created after that horizon is ineligible for freezing. In this state, changing configuration parameters or running VACUUM FREEZE will not lower the table's age.

3.6 Freezing is a Write Operation (WAL Impact)

Because freezing modifies data pages, it writes to the WAL. This has three practical consequences:

• Write Volume Spikes: Freezing a large, inactive table requires writing to every page containing an unfrozen transaction ID, which can cause significant disk write and checkpoint activity.
• Replication Overhead: These freeze operations generate WAL records that replicate to all standbys, which can cause replication lag during intense vacuuming.
• Full-Page Images: If a page is modified for the first time after a checkpoint, PostgreSQL writes the entire page image to the WAL, increasing its size.

In PostgreSQL 17, the separate XLOG_HEAP2_FREEZE_PAGE WAL record type was replaced by the pruning record XLOG_HEAP2_PRUNE_VACUUM_SCAN. If you are analyzing WAL output on version 17, look for these pruning records instead.

3.7 Visibility Map Optimization

Once all tuples on a page are frozen, the page is flagged as all-frozen in the visibility map. Subsequent aggressive vacuum operations can skip these pages entirely. This optimization makes subsequent vacuum runs much faster, and is why maintaining an up-to-date visibility map is important.

Once all pages in a table are confirmed as frozen, the database can advance pg_class.relfrozenxid for that table. The database-level age pg_database.datfrozenxid is then updated to match the lowest relfrozenxid among its tables.

4. Reclaiming Dead Tuples vs. Freezing Live Tuples

It is important to distinguish between the two primary functions of a vacuum operation:

A standard vacuum run performs both tasks simultaneously, but they address different problems. A database table can be completely free of dead tuple bloat and still be at risk of transaction ID wraparound, or vice versa.

5. The PostgreSQL Escalation Ladder

PostgreSQL escalates its warning and safety behaviors as transaction IDs age, providing ample opportunity to address the issue before writes are blocked:

• vacuum_freeze_min_age (Default: 50M): Rows older than this are frozen when vacuuming visits their page.
• vacuum_freeze_table_age (Default: 150M): Vacuum transitions to an aggressive scan, scanning all-visible pages rather than skipping them.
• autovacuum_freeze_max_age (Default: 200M): An anti-wraparound autovacuum is triggered even if autovacuum is disabled for that table. This process will not yield to other lock requests.
• vacuum_failsafe_age (Default: 1.6B, introduced in PG 14): The engine prioritizes speed over non-essential operations: cost delays are disabled, index cleanup is bypassed, and the system focuses entirely on freezing tuples.
• ~2B minus 40M: The server log begins writing warnings that writes will be disabled within a specific number of transactions.
• ~2B minus 3M: PostgreSQL stops assigning new transaction IDs. Writes are disabled, but read operations continue to function.

⚠ A precision point worth having. That final rung is often described as "PostgreSQL goes read-only." It is more specific than that. PostgreSQL refuses to hand out new transaction IDs. Reads still work. It is not a default_transaction_read_only flip, and on modern versions you do not need single-user mode to dig out — that piece of folklore is mostly obsolete.

6. The Real Cause: A Pinned Horizon

When vacuum runs successfully but the transaction age does not decrease, it is usually because the OldestXmin horizon is being pinned.

6.1 OldestXmin is a Live Measurement

The database computes OldestXmin during each vacuum run to answer a single question:

What is the oldest transaction ID that an active query or session might still need to see?

To maintain Multi-Version Concurrency Control (MVCC) consistency, vacuum cannot freeze or clean up any data newer than this horizon. If a transaction opened at XID 1000 is still active, it must be able to view the database state as of XID 1000. If vacuum modified or removed data newer than that, it would break transaction isolation guarantees like Repeatable Read.

6.2 The Running Minimum Loop

The horizon computation occurs in ComputeXidHorizons() inside procarray.c. The engine evaluates every active session, replication slot, and standby database, keeping the oldest active transaction ID:

/* start with the replication slots */
h->slot_xmin = procArray->replication_slot_xmin;

/* then walk every backend (every session) in the procarray */
xmin = UINT32_ACCESS_ONCE(proc->xmin);
h->data_oldest_nonremovable =
    TransactionIdOlder(h->data_oldest_nonremovable, xmin);

/* then fold the slots back in */
h->data_oldest_nonremovable =
    TransactionIdOlder(h->data_oldest_nonremovable, h->slot_xmin);

6.3 Database Scope and Blast Radius

The impact of a pinned horizon depends on the type of resources involved. PostgreSQL maintains separate horizons for different classes of tables:

switch (GlobalVisHorizonKindForRel(rel))
{
    case VISHORIZON_SHARED:   return horizons.shared_oldest_nonremovable;
    case VISHORIZON_CATALOG:  return horizons.catalog_oldest_nonremovable;
    case VISHORIZON_DATA:     return horizons.data_oldest_nonremovable;
    case VISHORIZON_TEMP:     return horizons.temp_oldest_nonremovable;
}

This separation has practical benefits:

/*
 * Normally sessions in other databases are ignored for anything
 * but the shared horizon.
 */
if (proc->databaseId == MyDatabaseId ||
    (statusFlags & PROC_AFFECTS_ALL_HORIZONS) ||
    in_recovery)
{
    h->data_oldest_nonremovable =
        TransactionIdOlder(h->data_oldest_nonremovable, xmin);
}

A standard active session only pins the horizon for its own database. An idle transaction in an analytics database does not prevent vacuum from freezing tables in your primary database.

However, certain global resources cross database boundaries and affect the entire cluster:

Replication slots have a wide impact, as they can block freezing across every database on the database instance.

6.4 The Four Main Blocker Types

1. Long-Running or Idle Transactions: A common cause is an application that runs BEGIN, performs some operations, and fails to execute COMMIT or ROLLBACK. These connections consume minimal resources but hold a snapshot open indefinitely. To mitigate this, configure idle_in_transaction_session_timeout PostgreSQL Connection States Guide.
2. Inactive Replication Slots: A replication slot guarantees that the primary database will not delete WAL segments or freeze tuples before the replica has read them. If a replica is removed but its slot is left on the primary, it will pin the horizon indefinitely.
3. Prepared Transactions: If a coordinator crashes during a two-phase commit, a transaction can remain in a prepared state. These do not show up as active database connections, are not visible in pg_stat_activity, and survive database restarts. You must query pg_prepared_xacts to find them.
4. Hot Standby Feedback: When hot_standby_feedback = on is enabled, replicas report their oldest active snapshot to the primary database to prevent query cancellation. As a result, a long-running reporting query on a replica can temporarily block freezing on the primary database.

6.5 Diagnostic Query Interpretations

These are the same three queries from §0. Now that you know what OldestXmin is, you can read them properly — because each one is interrogating a different way of registering an interest in an old snapshot.

-- 1. Running / idle-in-transaction sessions
SELECT pid, state, xact_start, backend_xid, backend_xmin, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

-- 2. Replication slots  (active = false with a large age is a smoking gun)
SELECT slot_name, active, xmin, catalog_xmin,
       age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots
ORDER BY greatest(age(xmin), age(catalog_xmin)) DESC NULLS LAST;

-- 3. Prepared transactions  (invisible in pg_stat_activity)
SELECT gid, prepared, owner, database, age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;

Use the table below to map diagnostic findings to resolutions:

⚠️ A critical warning regarding active replication slots: An active slot (active = true) with a high age represents a live replica that is struggling to keep pace, not an orphaned object [1]. Dropping it will instantly break replication and disrupt down-stream services. Confirm the subscriber is actually defunct before executing any drop commands

6.6 WHY THIS IS SO HARD TO SPOT

The root cause is difficult to detect because nothing is broken. Every component is behaving exactly as designed. The idle transaction is preserving snapshot isolation; the replication slot is protecting the replica; the prepared transaction is waiting for its coordinator; hot standby feedback is preventing query cancellations.

The system operates without throwing errors, CPU metrics remain normal, and autovacuum continues to run and report success because it is successfully reclaiming dead tuples. It simply cannot freeze anything. Meanwhile, the transaction counter continues to climb, and relfrozenxid does not follow.

The Ratchet Principle

Think of the freeze age as a mechanical ratchet with a physical stop block. Vacuum tries to push relfrozenxid forward, but it can only go as far as OldestXmin. If a blocker is holding the horizon back, the ratchet is jammed. Tuning parameters or forcing vacuums will not move that stop block:

vacuum_freeze_min_age = 0   ---> Cannot move OldestXmin
autovacuum_freeze_max_age   ---> Cannot move OldestXmin 
VACUUM FREEZE               ---> Cannot move OldestXmin
VACUUM FULL                 ---> Cannot move OldestXmin
autovacuum_max_workers      ---> Cannot move OldestXmin
vacuum_cost_limit           ---> Cannot move OldestXmin

Once you remove the stop block, the next standard VACUUM run will advance the horizon and reduce the age by hundreds of millions of transactions in a single pass. Clearing the blocker is the primary task; the subsequent vacuum is a formality.

Two Failures That Are Not Pinning — But Look Identical from the Dashboard

• Temporary Tables: Autovacuum cannot access temporary tables because they are private to their creating session. Since there is no automated process to clean them up, they must be vacuumed by manual SQL inside that session, or the session must be closed to drop them automatically.
• Lock Starvation: Autovacuum requests a weak SHARE UPDATE EXCLUSIVE lock. If a scheduled job (such as a regular ANALYZE or ALTER TABLE) requests a stronger conflicting lock, autovacuum will gracefully yield and cancel itself. If this happens repeatedly, the table will never complete a full vacuum pass.

If Your Database is Healthy (Age is climbing, but writes are still accepted)

The sequencing of recovery actions is simple: clear the blocker first, then vacuum. Terminate the session, drop the inactive replication slot, or roll back the prepared transaction. Running a vacuum before doing this is wasted I/O; vacuum will scan, report success, and advance relfrozenxid by nothing. Once the horizon moves, a standard VACUUM is usually sufficient; reach for VACUUM FREEZE only if you want to force the work immediately rather than wait on the age thresholds.

If XID Assignment Has Already Failed (Writes have stopped)

Once PostgreSQL is refusing to assign new transaction IDs, the recovery path must be followed precisely:

1. Resolve outstanding blockers: Terminate long sessions, drop inactive replication slots, and rollback prepared transactions.
2. Execute a standard vacuum: Run a database-wide VACUUM as a superuser. This is necessary to clean up the system catalogs and advance datfrozenxid.

⚠️ Do not use VACUUM FULL in this state. VACUUM FULL requires a transaction ID to run. In a database that has already stopped assigning them, it will fail. Attempting to force it through in superuser mode consumes a transaction ID and moves the database closer to the absolute limit.

⚠️ Do not use VACUUM FREEZE under extreme emergency. It performs more work than necessary to restore writes. A standard VACUUM is the most efficient way to get the database accepting writes again. You can tune autovacuum parameters once the system is stable.

ℹ️ You almost certainly do not need single-user mode. Modern PostgreSQL releases reserve a margin of 3 million transaction IDs specifically for administrators to resolve wraparound issues online. Single-user mode should be a last resort, as it takes the cluster offline and disables built-in safeguards. The one legitimate use is if you need to TRUNCATE or DROP tables to avoid vacuuming them at all.

7. Selecting the Correct Vacuum Command

The table below compares the behavior of different vacuum commands:

For detailed strategies on managing table bloat and configuring autovacuum, see our guide on PostgreSQL AutoVacuum Configuration.

8. Setting Up Effective Metrics and Monitoring

To monitor transaction aging, add these two queries to your database dashboards:

-- Database-level transaction age
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

-- Table-level transaction and MultiXact age
SELECT n.nspname AS schema, c.relname AS table,
       age(c.relfrozenxid) AS xid_age,
       mxid_age(c.relminmxid) AS mxid_age
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','m','t')
ORDER BY xid_age DESC
LIMIT 10;

Monitoring Best Practices

• Track MultiXact Age (mxid_age): MultiXact IDs wrap on an independent counter with similar mechanics. In foreign-key-heavy or SELECT ... FOR SHARE workloads, they are often the actual cause of wraparound issues while standard relfrozenxid metrics look healthy. Set up alerts on both.
• Alert on Climbing Trends: Do not set alerts solely at absolute values like 200M (where aggressive autovacuum starts). The safety net is already active by then, and you have not identified the cause. Instead, configure alerts to trigger when transaction age climbs steadily and fails to return to baseline. This pattern indicates a pinned horizon and provides warning weeks or months before any operational impact occurs.
• Managed Platforms: On managed cloud services, this metric is exposed under different names, such as AWS CloudWatch's MaximumUsedTransactionIDs. Cloud SQL, AlloyDB, and Azure Flexible Server all surface an equivalent metric. The underlying engine behavior remains identical, so ensure these metrics are monitored.

For advice on integrating these metrics into your monitoring setup, read our guide on Full-Stack Database Observability with Grafana.

9. Common Implementation Mistakes

• Disabling Autovacuum on Large Tables "For Performance": This does not prevent vacuuming; it simply delays it until the autovacuum_freeze_max_age threshold is reached. At that point, an aggressive, un-throttled autovacuum runs anyway, typically at the least convenient moment, and it will not yield to your lock requests.
• Aggressive Autovacuum Throttling: Setting a conservative autovacuum_vacuum_cost_delay on high-write systems can prevent vacuuming from keeping up with database write rates. It runs continuously and finishes nothing. On modern storage systems, the default cost settings are often too conservative.
• Using VACUUM FULL Under Wraparound Pressure: This is a common but incorrect recovery attempt. It requires a transaction ID to run, meaning it will fail if the database has already reached its write limit. Forcing it through as a superuser consumes a transaction ID and moves you closer to the write limit.
• Lock Starvation from Scheduled Tasks: Scheduled jobs (like routine ANALYZE or ALTER TABLE runs) take strong locks that conflict with autovacuum's weak lock. This causes autovacuum to cancel itself mid-run. If this happens repeatedly, the table will never complete a full vacuum pass, and the issue will remain invisible in standard bloat metrics.
• Watching Bloat but Not Age: These are different problems solved by the same command. A table can be completely free of dead tuple bloat and still be at risk of transaction ID wraparound if a blocker is pinning the horizon.
• Ignoring MultiXact Age: This has its own counter and can trigger similar write-prevention blocks.
• Ignoring hot_standby_feedback Across Machines: A long-running query on a standby replica can pin OldestXmin on the primary database if hot_standby_feedback is enabled. When the primary stops freezing, investigation must extend to the replica.

Closing Thoughts

Transaction ID wraparound is ultimately a horizon-tracking challenge rather than a simple vacuum problem. The counter's progression is normal database behavior. Issues arise only when a resource—such as an idle session, a lingering replication slot, or an unresolved prepared transaction—pins the visibility horizon, and the age metric climbs unnoticed.

Setting up basic alerts on your transaction age metrics is an effective way to protect your database from these issues.

Need Assistance with PostgreSQL Maintenance or Tuning?

If you are managing rising transaction ages, database bloat, or lock conflicts, our team of PostgreSQL specialists can help. We assist with configuration tuning, resolving replication issues, and optimizing autovacuum processes to keep your databases performing reliably.

Learn More About Our Managed PostgreSQL Services