PostgreSQL Subtransaction Problem: Transaction ID Growth

Summary: In this article, we will know a subtransaction problem in PostgreSQL: transaction id growth.

Table of Contents

Introduction

One of the basic issues with subtransactions is that they increment XID – the global transaction ID. As with regular transactions, this happens only if there is some modifying work – but if there are multiple nested subtransactions, all of them get their own XID assigned, even if a single modification happened inside the inner-most of them.

Example

Let’s see, in a database where no activity is happening:

CREATE TABLE test1(i int);

BEGIN;

-- call txid_current() in PostgreSQL 12 or older
SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
         1028370056
(1 row)

INSERT INTO test1 SELECT 1;
SAVEPOINT s1;
INSERT INTO test1 SELECT 2;
SAVEPOINT s2;
SAVEPOINT s3;
INSERT INTO test1 SELECT 3;
COMMIT;

SELECT pg_current_xact_id();
 pg_current_xact_id
--------------------
         1028370060
(1 row)

SELECT ctid, xmin, xmax, i FROM test1;
 ctid  |    xmin    | xmax | i
-------+------------+------+---
 (0,1) | 1028370056 |    0 | 1
 (0,2) | 1028370057 |    0 | 2
 (0,3) | 1028370059 |    0 | 3
(3 rows)

In this example, the main transaction had XID = 1028370056, and additional XIDs – 1028370057, 1028370058, 1028370059 – were assigned to subtransactions defined by SAVEPOINTs s1, s2, and s3 respectively. As a result, the tuples in the table have different xmin values (to learn more about xmin, see “5.5 System Columns”), although they all were inserted in a single transaction. Notice that XID 1028370058 was not used in xmin values – no tuples were created between savepoint s2 and savepoint s3. Overall, four XIDs were consumed instead of a single one as it could be in the case of a subtransaction-free transaction.

Conclusion

The above example clearly shows two facts that may be not intuitive:

1. XIDs assigned to subtransactions are used in tuple headers, hence participating in MVCC tuple visibility checks – although results of subtransactions are never visible to other transactions until the main transaction is committed (in PostgreSQL, “minimal” isolation level supported is READ COMMITTED).
2. Subtransactions contribute to the growth of global XID value (32 bit, requiring special automated maintenance usually done by autovacuum). Therefore it implicitly increases risks associated with XID wraparound: if the mentioned maintenance is lagging for some reason and this issue is not resolved, the system may reach a point when the mechanism of transaction ID wraparound protection puts the cluster to the single-user mode causing long-lasting downtime. There are many application systems were down because of that. One may have, say, 1000 writing transactions per second, but if they all use 10 subtransactions, then XID is incremented by 10000 per second. This might not be expected by users – poor autovacuum needs to run in the “transaction ID wraparound prevention” mode more often than it would be if subtransactions had “local” IDs inside each transaction, not “wasting” global XIDs.

Bottom line: there is a trade-off between active use of subtransactions and the XID growth. Understanding this “price” of using subtransactions is essential to avoid issues in heavily-loaded systems.