The dangers of Postgres subtransactions — PlanetScale📹 The future of AI infrastructure: optimize and shard your database with agents.Watch the talk
Navigation<br>Blog|Engineering<br>Table of contents «Close »Table of contents<br>How replicas stay in syncDecoding WAL records<br>Tracking running transactions using WAL records
The subtransaction cache limit<br>Cluster-wide performance issuesBenchmark<br>SLRU locking in PostgreSQL
Why new replicas refuse connectionsDealing with overflowed snapshots on read replicas<br>Why replicas cannot use pg_subtrans<br>The HA blind spot
Detecting and preventing overflowKeep transactions short<br>Detect subtransaction cache overflow<br>Monitor pg_subtrans activity<br>Changes to PostgreSQL itself
PlanetScale Postgres is the fastest way to run Postgres in the cloud. Plans start at just $5 per month.<br>Learn more
Get the RSS feed
The dangers of Postgres subtransactions<br>Jan Nidzwetzki, Etienne Berube | August 11, 2026<br>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.<br>If you add read replicas on demand during a load spike, a replica that cannot open for reads provides no extra capacity.<br>Let’s look at how PostgreSQL keeps a replica in sync and determines when it can safely serve reads.<br>How replicas stay in sync<br>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.<br>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.<br>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.<br>Decoding WAL records<br>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:<br>$ PGDATA=$(psql -d postgres -Atqc "SHOW data_directory")<br>$ WAL_FILE=$(psql -d postgres -Atqc "SELECT pg_walfile_name(pg_current_wal_lsn())")<br>$ pg_waldump "$PGDATA/pg_wal/$WAL_FILE"
A decoded WAL stream looks like this:<br>$ pg_waldump pg_wal/000000010000000000000001<br>[...]<br>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<br>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<br>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<br>[...]<br>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.<br>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:<br>The first record belongs to the 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).<br>The second and third records belong to the 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).<br>The fourth record belongs to the Transaction resource manager, showing that transaction 23445 successfully committed at the timestamp provided. This record also contains invalidation messages (inval msgs), which notify the...