Transaction ID wraparound: a walk on the wild side

Wraparound: When Transaction IDs Come Back to Bite

Transaction ID wraparound is a well-known PostgreSQL hazard, but for most people it remains an abstract threat. It is easy to find horror stories about anti-wraparound autovacuum grinding performance to a halt or databases locking up entirely. But actual data loss? That seems to belong to the realm of legend. Curious about what really happens when those protections fail, I decided to see if I could break things for real. It turned out to be both harder and more revealing than expected.

Why There Is Usually Nothing to Worry About

In normal operation, there is little reason to fear wraparound. PostgreSQL has layered defenses that prevent data corruption from ever occurring. In fact, as this experiment shows, you need deliberately malicious tricks to even get close. That does not mean the problem is irrelevant—when those protections kick in, they can seriously disrupt operations—but the panic is largely unnecessary. Most of the time, transaction IDs wrap around without you ever noticing.

Setting Up a Laboratory for Disaster

Because the goal was to corrupt data, I started with a throwaway cluster. A non-standard port and a prepared transaction were configured from the beginning; the prepared transaction would later serve as the key obstacle to vacuum.

After connecting, I created a small table and inserted two rows. The plan was to delete one row and insert another. A crucial detail: I avoided any SELECT statements on this table. Reading the data would set hint bits, which would ruin the conditions needed for the corruption scenario. Instead, I relied on xmin and xmax to track which transaction created or deleted each row.

The First Line of Defense: Warnings

Autovacuum is normally quite good at preventing wraparound. To stop it from doing its job, I left a prepared transaction holding a lock on the table. Prepared transactions, normally used for two-phase commit, stick around until explicitly committed or rolled back and survive even server restarts. This one was the perfect obstacle.

The next step was advancing the transaction ID counter by roughly two billion—a tedious task by hand. PostgreSQL, however, ships with a tool designed for exactly this kind of abuse: pg_resetwal. It is intended for salvaging broken clusters, and it can be used to forcibly advance the transaction ID. A clean shutdown was essential; otherwise, the -f flag would be required and likely destroy data. With the cluster stopped, I set the transaction ID to 2³¹ - 10000000 and faked a matching commit log file.

After restarting, calling pg_current_xact_id() consumed a transaction ID and triggered the first warning. This appears when you are within 40 million transactions of the danger zone. It is easy to miss unless someone is watching the logs.

The Second Line of Defense: Shutdown

Ignoring the warning, I repeated the process, advancing to 2³¹ - 1000000 and again creating a matching commit log file. This time, consuming a transaction ID produced a different outcome: the server refused normal activity entirely. The only path forward was manually vacuuming any tables with old, unfrozen tuples. The error message suggests starting PostgreSQL in single-user mode, but that is not actually necessary for VACUUM, since vacuum does not consume transaction IDs. In fact, single-user mode is somewhat dangerous here—it disables the very safeguards that stop you from crashing past the limit. Still, the error appears three million transactions before real data corruption, so there is ample room for trial and error.

Crossing the Line and Raising the Dead

The warnings did not deter the experiment. I reset the transaction ID counter all the way down to 725, relying on the old commit log files that autovacuum had never managed to clean up. Starting PostgreSQL in single-user mode, I reused transactions 725 through 727, then committed the prepared transaction to remove the obstruction.

After a normal restart, the table told a strange story. Row 1—the one deleted early on—was alive again. Row 2, which should have been visible, had vanished. By rolling back transactions 726 and 727, I had undone both the DELETE and the INSERT. Row 2 was invisible but still physically present on disk. Wraparound had effectively made the dead rise and the living disappear.

Surgical Cleanup with pg_surgery

To inspect the damage, the pageinspect extension reveals what is actually on disk. For repair, PostgreSQL 14 introduced pg_surgery, a module with functions to kill rows and to freeze rows into unconditional visibility. These tools are blunt and dangerous; they are meant for getting a corrupted database into a state where data can be dumped and restored elsewhere.

The important lesson: once corruption happens, you cannot simply patch the table and move on. Even if the visible symptoms are fixed, invisible damage may remain. The only safe course is to extract the data and rebuild a fresh cluster.

The Takeaway

Transaction ID wraparound, by itself, cannot corrupt data under normal circumstances. The protections in PostgreSQL are robust, and getting past them demands deliberate misuse of dangerous tools. The real risks are operational: warnings that go unnoticed, autovacuum that cannot keep up, and prepared transactions left dangling. Those are the things worth monitoring. Actual data loss from wraparound is not a realistic threat for anyone following standard practices.