Skip to content
Jason on Twitter Jason on GitHub

What's New in 2024 with PostgreSQL's "Deadly" Transaction Wraparound Problem?

The risk of encountering the infamous Transaction Wraparound problem in PostgreSQL has never been lower. Thanks to new testing tools and improved performance in PostgreSQL 17, DBAs managing the highest throughput PostgreSQL databases out there have less to worry about than ever.

I have a personal vendetta with this problem. During my tenure at a previous company, transaction wraparound caused significant inefficiencies in our query-based change data capture (CDC) pipeline. To fully grasp the issue's impact, I dove deep into the PostgreSQL source code and ultimately developed a custom fork. The fork modified the hard-coded "MaxTransactionId" from its default of ~4 billion to ~1 million, enabling our development team to simulate wraparound scenarios in seconds rather than minutes—dramatically streamlining our testing process [1]. While I had lots of fun working on that project, the new xid_wraparound extension covered below fortunately makes it unnecessary to go to such lengths!

In this article, we'll cover:

  1. The xid_wraparound extension to simulate transaction wraparound scenarios in seconds, making manual testing much easier and automated testing more feasible.
  2. PostgreSQL 17 introduces performance enhancements to vacuum operations. These enhancements reduce the cost of vacuum and thereby mitigate the risk of wraparound.
  3. The potential elimination of wraparound concerns in PostgreSQL is on the horizon with the introduction of 64-bit XIDs. While substantial progress has been made on this patch, the timeline for its completion and integration remains uncertain.

Table of Contents

  1. Simulate Wraparound in Seconds with the xid_wraparound Extension
  2. Performance Improvements to Vacuum in Postgres 17
  3. Potential 64-bit XIDs Patch

Simulate Wraparound in Seconds with the `xid_wraparound` Extension

In November 2023, Masahiko Sawada created a new extension to enable automated tests for transaction wraparound.

The extension consume XIDs both incrementally (similar to past approaches [2]) and introduces a shortcut method that can increment the transaction ID counter in large steps as long as no "interesting" values are skipped. [3]

While intended for testing purposes, we can easily install the extension in a self-hosted Postgres 17 instance and use as needed in system tests.

For convenience, the author has published a Docker image that includes the xid_wraparound extension pre-installed. You can find it at https://hub.docker.com/r/hundredwatt/postgres-xid_wraparound.

If you'd like to build PostgreSQL with the extension from source, follow these instructions:

  1. Download Postgres from source (I use the Github mirror):

    git clone https://github.com/postgres/postgres.git
cd postgres
git checkout REL_17_STABLE

  2. Install Postgres from source (instructions), taking special note of setting the --prefix flag to the target installation directory:

    ./configure --prefix=/opt/postgres-17-custom/
# Follow further instructions from [https://www.postgresql.org/docs/17/install-make.html#INSTALL-SHORT-MAKE](https://www.postgresql.org/docs/17/install-make.html#INSTALL-SHORT-MAKE)

  3. Install the xid_wraparound extension in your recently installed Postgres instance:

    cd src/test/modules/xid_wraparound
make
sudo make install

  4. Use the xid_wraparound extension

    which psql # if this doesn't show the correct Postgres install, update your $PATH
psql <database>

    CREATE EXTENSION xid_wraparound;
SELECT consume_xids((2 ^ 31)::bigint); // consume all available Transaction IDs

On my MacBook Air M2, it takes 7 seconds to consume 2 billion Transaction IDs to encounter the deadly "database is not accepting commands that assign new XIDs to avoid wraparound data loss" error.

Performance Improvements to Vacuum in Postgres 17

The pending launch of Postgres 17 will bring two notable improvements to Vacuum performance:

  1. Allow vacuum to more efficiently store tuple references
  2. Vacuum is no longer silently limited to one gigabyte of memory when maintenance_work_mem or autovacuum_work_mem are higher.

For the first item, allow vacuum to more efficiently store tuple references, the author, Masahiko Sawada, added a new data structure to PostgreSQL called TIDStore based on a cutting-edge data structure called an Adaptive Radix Tree that tightly-packs long lists of Tuple IDs in memory.

Masahiko Sawada discussed the implementation in detail in Episode 109 of the "5mins of Postgres" podcast. Check out the podcast or accompanying blog post to learn more: Waiting for Postgres 17: Faster VACUUM with Adaptive Radix Trees.

The benchmarks accompanying the podcast demonstrate rewriting 100m rows on PostgreSQL 16 (without the enhancement) and PostgreSQL 17 (with the enhancement). Compared to PostgreSQL 16, the VACUUM operation on PostgreSQL 17 used half the memory and completed 25% faster (773 seconds on 16 vs 619 seconds on 17). Quite the improvement!

--

The second item, vacuum is no longer silently limited to one gigabyte of memory, means that vacuum can use even more memory when desired by the DBA. In combination with the TIDStore enhancement, it now seems possible to complete most VACUUM operations without having to break the operation into multiple phases due to memory limits.

Potential 64-bit XIDs Patch

PostgreSQL's use of 32-bit Transaction IDs (XIDs) in the Tuple header of its database page layout causes the Transaction Wraparound problem. Increasing the size of the two XID fields required for its MVCC implementation to 64-bit would make the number of XIDs effectively infinite instead of having a wrap around every 4 billion transactions.

Unfortunately, this is not such a simple change to make. PostgreSQL core contributor Andres Freund said in an HN comment[4] :

There's no chance we go for 64bit transaction ids on the tuples themselves - the space increase would be far too big. The overhead of tuple headers is already a problem, and xmin/xmax are a significant portion of that.

Even though using 64-bit XIDs with each tuple is considered too costly, PostgreSQL hackers are actively pursuing a patch to add a new field to each page, xid_base, that would act as an offset for the existing XMIN/XMAX values and effectively introducing 64-bit XIDs.

With this change, the 64-bit XID for each tuple in a page can be easily computed and XID wraparound will never occur!

The patch has been discussed since 2021 and requires non-trivial changes to PostgreSQL to accommodate the new page layout. Fortunately though, the page layout can be introduced lazily without a costly transition once the patch lands in a future PostgreSQL version.

Conclusion

The PostgreSQL community has made significant strides in addressing the long-standing Transaction Wraparound problem. The developments discussed in this article showcase a multi-faceted approach to mitigating and potentially eliminating this issue:

  1. The xid_wraparound extension introduces a powerful tool for developers and DBAs to simulate wraparound scenarios quickly and easily. This capability enhances testing procedures and allows for more robust system designs.
  2. Performance improvements in PostgreSQL 17, particularly the introduction of the TIDStore data structure and the removal of silent memory limitations for vacuum operations, contribute to more efficient database maintenance. These enhancements reduce the likelihood of encountering wraparound issues in high-throughput environments.
  3. The ongoing work on 64-bit XIDs, while challenging, presents a promising path towards permanently resolving the wraparound problem. Although the timeline for completion remains uncertain, the progress made so far is encouraging.

These advancements collectively represent a significant leap forward in PostgreSQL's ability to handle high-volume transaction processing without the looming threat of wraparound.

If you're working with PostgreSQL, keep an eye on these and other new developments. Tools like the xid_wraparound extension can make your life easier, and the future looks bright for solving the wraparound problem once and for all.

--

Footnotes

[1] Though its not open source, feel free to contact me if you want to learn more about the changes required to make this fork.

[2] Prashant Dixit's 2021 blog post "How to simulate the deadly, infamous, misunderstood & complex ‘Transaction Wraparound Problem’ in PostgreSQL …" provides a detailed walkthrough of the original method I used to simulate wraparound in about 1 hour by running many parallel processes that continuously call SELECT txid_current(); to create an empty transaction.

[3] "All the interesting action in GetNewTransactionId happens when we extend the SLRUs, or at the uint32 wraparound. If the nextXid counter is not close to any of those interesting values, take a shortcut and bump nextXID directly, close to the next "interesting" value." -Masahiko Sawada (link)

[4] Comment from anarazel on Feb 5, 2019 on "How long will a 64 bit Transaction-ID last in PostgreSQL?": https://news.ycombinator.com/item?id=19083745