Subtransactions can cause severe performance bottlenecks and block read replica availability. Understanding their impact on WAL replay and transaction ID management is vital for engineers managing high-traffic PostgreSQL databases to avoid unexpected downtime during scaling.
A single transaction that accumulates numerous subtransaction IDs can significantly reduce throughput across an entire PostgreSQL cluster. It can also stop a new read replica from accepting queries, even as the replica continues to replay WAL.
If you add read replicas on demand during a load spike, a replica that cannot open for reads provides no extra capacity.
Let’s look at how PostgreSQL keeps a replica in sync and determines when it can safely serve reads.
Read replicas are copies of another PostgreSQL cluster, called the primary. They stay in sync by continuously replaying the primary’s Write-Ahead Log (WAL). With hot standby enabled, they can serve read-only queries while replay continues.
A PostgreSQL read replica is built from a base backup of the primary database. To understand how it stays in sync, it helps to understand how PostgreSQL writes data. Writes are recorded sequentially in the Write-Ahead Log (WAL) before being modified in the database's data files (indexes, table files). The WAL serves as an append-only journal of all database mutations, primarily used for crash recovery to guarantee data integrity and prevent data loss.
To keep a read replica synchronized, it establishes a stream connection to the primary node. The replica continuously receives the raw WAL stream, decodes the records, and applies those exact physical changes to its local dataset.
The WAL is stored in the pg_wal directory inside the PostgreSQL data directory. pg_waldump is a tool for reading those binary WAL records and printing them in a readable form. Because it reads WAL files directly, the following must be run on the host where PostgreSQL is running. The following commands locate the data directory and current WAL segment:
$ PGDATA=$(psql -d postgres -Atqc "SHOW data_directory")
$ WAL_FILE=$(psql -d postgres -Atqc "SELECT pg_walfile_name(pg_current_wal_lsn())")
$ pg_waldump "$PGDATA/pg_wal/$WAL_FILE"
A decoded WAL stream looks like this:
$ pg_waldump pg_wal/000000010000000000000001
[...]
rmgr: Heap len (rec/tot): 207/ 207, tx: 23445, lsn: 0/01BE9AA0, prev 0/01BE9A58, desc: INSERT off: 2, flags: 0x01, blkref #0: rel 1663/16384/1259 blk 0
rmgr: Btree len (rec/tot): 64/ 64, tx: 23445, lsn: 0/01BE9B70, prev 0/01BE9AA0, desc: INSERT_LEAF off: 119, blkref #0: rel 1663/16384/2662 blk 2
rmgr: Btree len (rec/tot): 72/ 72, tx: 23445, lsn: 0/01BE9BB0, prev 0/01BE9B70, desc: INSERT_LEAF off: 111, blkref #0: rel 1663/16384/2663 blk 2
[...]
rmgr: Transaction len (rec/tot): 373/ 373, tx: 23445, lsn: 0/01BEA360, prev 0/01BEA318, desc: COMMIT 2026-06-02 12:16:49.173692 CEST; inval msgs: catcache 82 catcache 81 catcache 82 catcache 81 catcache 57 catcache 56 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 snapshot 2608 relcache 16389
The output shows four decoded WAL records. Each is identified by an LSN (log sequence number), which identifies the position of the record in the WAL. The transaction to which these records belong is also shown.
Every record belongs to a specific Resource Manager (rmgr). When decoding WAL records, this resource manager is responsible for decoding the record and propagating the changes to a particular database subsystem:
Heap resource manager, meaning it modifies a standard data page. The description (desc) shows a new tuple being inserted into block 0 at offset 2 of relation 1663/16384/1259 (representing the tablespace OID, database OID, and relation OID).Btree resource manager, which handles index updates. Their descriptions show that index tuples are being added to leaf nodes (INSERT_LEAF) inside blocks of two separate B-tree index relations (2662 and 2663).Transaction resource manager, showing that transaction 23445 successfully committed at the timestamp provided. This record also contains invalidation messages (inval msgs), which notify the rest of the database cluster to clear their internal metadata caches (catcache and relcache) because a system table (pg_class) was modified.These WAL records are continuously streamed from the primary to its replicas, where they are replayed to keep the replicas in sync. However, replaying physical changes is only one part of serving reads from a replica. PostgreSQL must also know which transactions are active to construct a consistent snapshot. See our post on PostgreSQL MVCC for how snapshots and visibility work. To guarantee transactional atomicity, the engine must ensure that any changes made by concurrent transactions are either entirely visible or entirely invisible during an ongoing scan operation. Consequently, applying new WAL records while a query is actively running on the read replica should not alter the data seen by that ongoing query.
While reading the continuous stream of WAL records, a replica can dynamically track active transactions when a new transaction ID appears in the log, and it learns exactly when they finish by processing their corresponding COMMIT or ABORT records.
However, before serving any queries, the standby needs to know the global state of all actively running transactions to build consistent MVCC snapshots. Because the read replica begins processing the WAL stream starting strictly from the backup redo checkpoint forward, it initially lacks context regarding transactions that started before that checkpoint but haven't committed yet.
To solve this problem, PostgreSQL periodically injects status information about the running transactions into the WAL stream, managed by the Standby resource manager:
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/01C080D0, prev 0/01C08058, desc: RUNNING_XACTS nextXid 23477 latestCompletedXid 23475 oldestRunningXid 767
These RUNNING_XACTS records act as a snapshot of the primary server's active transaction state. They log critical boundaries, including the oldest currently running transaction ID (oldestRunningXid), the next unassigned transaction ID (nextXid), and the most recently resolved transaction ID (latestCompletedXid). Furthermore, these records encapsulate an internal array listing all active top-level transaction IDs and subtransactions. When present, pg_waldump prints these arrays as xacts and subxacts. By consuming this record, the read replica can immediately determine which older data rows are still uncommitted and invisible, allowing it to safely open for read-only queries.
When a read replica is started, it needs to process one of these records and from there on, the regular WAL records are enough to maintain the internal state of the running transactions and to build proper snapshots.
A subtransaction is a transaction nested inside a top-level transaction. Applications create them explicitly with SAVEPOINT. PL/pgSQL also creates them for blocks that contain an EXCEPTION clause.
Each PostgreSQL backend keeps the assigned, non-aborted subtransaction IDs for its current top-level transaction in shared memory. This list is called the subtransaction cache and holds up to PGPROC_MAX_CACHED_SUBXIDS IDs, usually 64.
PostgreSQL stores each subtransaction’s parent separately in pg_subtrans. pg_subtrans is stored on disk and accessed through a simple least-recently-used (SLRU) cache. If subtransactions are nested, PostgreSQL follows the parent links until it reaches the top-level transaction.
When PostgreSQL builds a snapshot, it collects the running top-level transaction IDs and the cached subtransaction IDs from every backend. When a query reads a row, PostgreSQL compares the transaction IDs in the row’s xmin and xmax fields with that snapshot to decide whether the row is visible.
If a backend accumulates more subtransaction IDs than its cache can hold, PostgreSQL marks the cache as overflowed. When this happens, a snapshot or RUNNING_XACTS record built while that transaction is running cannot include every subtransaction ID and is also marked as overflowed.
If a query encounters a subtransaction ID that is not listed due to overflow, PostgreSQL looks it up in pg_subtrans to find its top-level transaction ID, then checks that ID against the snapshot. A single query may perform many pg_subtrans lookups, repeatedly acquiring an SLRU read lock and sometimes reading from disk.
At this point, the subtransaction cache and pg_subtrans fallback may just seem like implementation details, but there is a hidden problem here that can cause serious damage to a high availability cluster. As we’ll see, one overflowing transaction can slow queries across the cluster and prevent a new replica from accepting reads.
To trigger such a subtransaction cache overflow, a transaction needs to create more than PGPROC_MAX_CACHED_SUBXIDS subtransactions. Such a transaction can be simply constructed as follows:
postgres=> CREATE TABLE IF NOT EXISTS subxid_test (id int);
postgres=> BEGIN;
postgres=*> SAVEPOINT s1;
postgres=*> INSERT INTO subxid_test VALUES (1);
postgres=*> SAVEPOINT s2;
postgres=*> INSERT INTO subxid_test VALUES (2);
[...]
postgres=*> SAVEPOINT s70;
postgres=*> INSERT INTO subxid_test VALUES (70);
Explicit savepoints are not the only way to reach the limit. PL/pgSQL uses a subtransaction for every block that contains an EXCEPTION clause. The following loop creates more than PGPROC_MAX_CACHED_SUBXIDS subtransactions without issuing SAVEPOINT directly:
postgres=> ROLLBACK;
ROLLBACK
postgres=> DO $$
BEGIN
FOR i IN 1..70 LOOP
BEGIN
INSERT INTO subxid_test VALUES (i);
EXCEPTION WHEN OTHERS THEN
RAISE;
END;
END LOOP;
END
$$;
DO
When a new snapshot is created by a backend and another backend reports that its subtransaction cache has overflowed, the entire snapshot is marked as overflowed. So, one running transaction influences queries running on other connections even if they touch different tables or run in different databases.
When scanning data with such a snapshot, a more expensive SLRU lookup of pg_subtrans must be performed for every scanned tuple whose transaction ID falls inside the snapshot's xmin/xmax range. This SLRU lookup requires read locking the SLRU, which could lead to lock contention. Furthermore, slow disk access is needed if the SLRU is not cached in memory. This is a cluster-wide performance cliff that many PostgreSQL administrators find surprising.
Warning
A single transaction that accumulates more than PGPROC_MAX_CACHED_SUBXIDS assigned, non-aborted subtransaction IDs can slow queries across the entire PostgreSQL cluster, including sessions that do not use subtransactions themselves.
This slowdown can be clearly reproduced in a benchmark. The following experiment shows that the TPS for a simple INSERT, SELECT, DELETE workload drops immediately when a subtransaction overflow happens.
$ createdb mydb
$ cat prepare.sql
DROP TABLE IF EXISTS bench_mvcc;
CREATE TABLE bench_mvcc (
id bigserial PRIMARY KEY,
grp integer NOT NULL,
val integer NOT NULL
);
psql -f prepare.sql mydb
$ cat bench.sql
\set grp random(1, :ngroups)
\set v random(1, 1000000)
BEGIN;
INSERT INTO bench_mvcc (grp, val) VALUES (:grp, :v);
SELECT count(*) FROM bench_mvcc WHERE grp = :grp;
DELETE FROM bench_mvcc WHERE grp = :grp;
COMMIT;
pgbench -n -f bench.sql -D ngroups=10000 -c 16 -j 4 -T 600 -P 5 mydb
pgbench (18.4)
progress: 90.0 s, 8640.2 tps, lat 1.847 ms stddev 0.504, 0 failed
progress: 95.0 s, 8162.2 tps, lat 1.955 ms stddev 0.461, 0 failed
progress: 100.0 s, 7745.0 tps, lat 2.061 ms stddev 0.654, 0 failed
progress: 105.0 s, 7257.0 tps, lat 2.199 ms stddev 0.752, 0 failed
-- Start of the subtransaction query
progress: 110.0 s, 5504.0 tps, lat 2.898 ms stddev 1.656, 0 failed
progress: 115.0 s, 1376.2 tps, lat 11.609 ms stddev 3.048, 0 failed
progress: 120.0 s, 904.2 tps, lat 17.674 ms stddev 3.370, 0 failed
progress: 125.0 s, 674.6 tps, lat 23.731 ms stddev 7.014, 0 failed
progress: 130.0 s, 379.4 tps, lat 41.964 ms stddev 14.276, 0 failed
progress: 135.0 s, 265.6 tps, lat 60.356 ms stddev 7.362, 0 failed
progress: 140.0 s, 356.6 tps, lat 44.759 ms stddev 19.705, 0 failed
progress: 145.0 s, 242.2 tps, lat 66.121 ms stddev 4.826, 0 failed
progress: 150.0 s, 205.2 tps, lat 77.794 ms stddev 7.277, 0 failed
progress: 155.0 s, 218.0 tps, lat 73.525 ms stddev 5.612, 0 failed
progress: 160.0 s, 218.4 tps, lat 73.434 ms stddev 4.875, 0 failed
progress: 165.0 s, 199.6 tps, lat 79.816 ms stddev 3.554, 0 failed
progress: 170.0 s, 174.8 tps, lat 91.367 ms stddev 9.229, 0 failed
progress: 175.0 s, 157.2 tps, lat 101.702 ms stddev 3.264, 0 failed
progress: 180.0 s, 166.0 tps, lat 96.803 ms stddev 8.565, 0 failed
progress: 185.0 s, 183.6 tps, lat 87.153 ms stddev 3.773, 0 failed
-- End of the subtransaction query
progress: 190.0 s, 5562.4 tps, lat 2.893 ms stddev 9.413, 0 failed
progress: 195.0 s, 8147.0 tps, lat 1.959 ms stddev 0.467, 0 failed
progress: 200.0 s, 7634.2 tps, lat 2.091 ms stddev 0.703, 0 failed
progress: 205.0 s, 8208.2 tps, lat 1.944 ms stddev 0.559, 0 failed
progress: 210.0 s, 8210.0 tps, lat 1.945 ms stddev 1.176, 0 failed
progress: 215.0 s, 8253.0 tps, lat 1.934 ms stddev 0.560, 0 failed
TPS declines during warm-up because this workload scans the growing table through the unindexed grp column and creates dead tuples with deletes. After throughput settles at about 7,200 TPS, opening an overflowing transaction drops it to about 160 TPS. Throughput recovers when the transaction ends.
The following flame graph shows that a significant portion of the query's execution time after the overflow is spent in XidInMVCCSnapshot while acquiring the SLRU lightweight lock through LWLockAcquire. This is shown by the two large XidInMVCCSnapshot boxes near the top of the flame graph.
The overflowed subtransaction cache also causes a second issue. In PostgreSQL, the RUNNING_XACTS WAL records are used to let read replicas know about the running transactions on the primary node. Due to the overflow, these WAL records do not contain all subtransactions, and they are marked as subxid overflowed.
rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/01C0B988, prev 0/01C0B960, desc: RUNNING_XACTS nextXid 766 latestCompletedXid 694 oldestRunningXid 695; 1 xacts: 695; subxid overflowed
When a new read replica starts up, based on the base backup of the primary, and consumes an overflowed RUNNING_XACTS record, the complete list of active primary transactions at this point in time remains unknown. Because the replica cannot create proper snapshots based on this information, PostgreSQL initializes the replica's replication status as STANDBY_SNAPSHOT_PENDING and delays enabling hot standby mode (see below).
To resolve such a pending snapshot, the read replica has to wait until one of the following events occurs. It then knows all running transactions, is able to construct a proper snapshot, and can mark it as STANDBY_SNAPSHOT_READY.
RUNNING_XACTS record arrives: The record is not marked as overflowed, so it contains the complete active transaction state.nextXid from the first overflowed record. Once a later record’s oldestRunningXid reaches that boundary, every transaction that could have been omitted has ended. The replica already tracked all newer transactions through WAL.
pg_subtransYou might wonder why the read replica does not use the same code path as the primary and just read the data from the pg_subtrans SLRU when the snapshot is marked as overflowed. Modifications to pg_subtrans are not WAL-logged, so the information from the primary is not available on the read replica. In addition, PostgreSQL zeroes the currently active pg_subtrans pages during startup. The replica therefore cannot rely on its local pg_subtrans data and must track running transactions through WAL instead.
Warning
Until the replica has reached a consistent recovery point and it is safe to take snapshots, PostgreSQL cannot enable hot standby, the mode that accepts read-only connections during recovery. The replica can be deployed and replaying WAL but still cannot absorb read traffic. Adding it during a load spike provides no extra capacity.
$ psql --port 5434
psql: error: connection to server on socket "/tmp/.s.PGSQL.5434" failed: FATAL: the database system is not yet accepting connections
DETAIL: Recovery snapshot is not yet ready for hot standby.
HINT: To enable hot standby, close write transactions with more than 64 subtransactions on the primary server.
The direct remedy is to close the overflowing write transaction on the primary, as the hint suggests. The transaction may finish on its own, but waiting leaves the replica unavailable. During an incident, an operator may need to identify and terminate it.
PostgreSQL 18 does not provide a built-in way to prevent subtransaction cache overflow. Operators can reduce risk by keeping transactions short and monitoring subtransaction and pg_subtrans activity.
Monitor the age of the oldest running transaction in the cluster. When transactions finish quickly, a newly started replica can clear transaction state missing from an overflowed RUNNING_XACTS record and reach a consistent state sooner.
Keeping transactions short is also good operational practice. Long-running transactions hold back vacuum cleanup and increase transaction ID wraparound risk. You can monitor the oldest open transaction with:
postgres=> SELECT COALESCE(EXTRACT(EPOCH FROM max(now() - xact_start)), 0) AS oldest_tx_seconds
FROM pg_stat_activity
WHERE xact_start IS NOT NULL;
oldest_tx_seconds
-------------------
432.346806
(1 row)
Settings such as transaction_timeout and idle_in_transaction_session_timeout can enforce transaction limits automatically. transaction_timeout limits total transaction duration, while idle_in_transaction_session_timeout terminates sessions that remain idle inside an open transaction.
Furthermore, you can use the PostgreSQL function pg_stat_get_backend_subxact() to get an overview of the currently running subtransactions on your PostgreSQL server, together with information about whether a subtransaction cache overflow happened:
postgres=> SELECT pg_stat_get_backend_pid(id), s.* FROM pg_stat_get_backend_idset() id
JOIN LATERAL pg_stat_get_backend_subxact(id) AS s ON TRUE WHERE s.subxact_count > 0;
pg_stat_get_backend_pid | subxact_count | subxact_overflowed
-------------------------+---------------+--------------------
962964 | 64 | t
(1 row)
pg_subtrans activityWhen a snapshot overflows, PostgreSQL reads the pg_subtrans SLRU. PostgreSQL’s cumulative statistics system tracks these reads. Monitoring the blks_hit and blks_read values in the pg_stat_slru table for the subtransaction SLRU shows the lookup activity. Rapidly increasing values strongly indicate that subtransaction overflows are occurring.
postgres=> SELECT * FROM pg_stat_slru WHERE name = 'subtransaction';
name | blks_zeroed | blks_hit | blks_read | blks_written | blks_exists | flushes | truncates | stats_reset
----------------+-------------+----------+-----------+--------------+-------------+---------+-----------+-------------------------------
subtransaction | 890 | 88474 | 0 | 754 | 0 | 6 | 6 | 2026-06-22 20:27:57.036583+02
(1 row)
What can be fixed on the PostgreSQL side to get rid of the problem? One solution is to recompile PostgreSQL with a larger PGPROC_MAX_CACHED_SUBXIDS constant, which increases the memory usage of each PostgreSQL backend. This lowers the chance that subtransaction IDs overflow. However, some workloads might eventually hit an even larger threshold.
A broader fix would replace active-transaction lists with Commit Sequence Number (CSN) snapshots. The idea has been discussed for more than a decade, and patches for CSN-based hot standby snapshots exist, but none have been merged into PostgreSQL.
Subtransaction cache overflow does not stay confined to the transaction that caused it. On the primary, it can push visibility checks onto pg_subtrans, where SLRU lock contention drives down throughput. On a new replica, incomplete RUNNING_XACTS records can delay hot standby and keep the replica from accepting read connections.
Keeping transactions short and monitoring the subtransaction cache and pg_subtrans activity reduce the risk.
Continue reading on the original blog to support the author
Read full articleNeki solves the scaling limits of single-instance Postgres without sacrificing compatibility or forcing application-level sharding. It provides a managed path to horizontal scaling with online operations, making it easier to handle massive workloads while keeping standard Postgres tools.
Understanding sharded query lifecycles is essential for engineers scaling relational databases. It reveals the trade-offs in query routing and aggregation necessary for high-performance distributed systems.
Neki enables Postgres to scale horizontally by decoupling connection management and query routing from the core engine. It allows developers to use standard drivers while transparently handling sharding, complex distributed queries, and high connection counts.
A single unhandled exception can cascade into full database downtime by blocking migrations and subsequent queries. Understanding lock queues and setting transaction timeouts is critical for maintaining high availability during schema changes.