The dangers of Postgres subtransactions

PostgreSQL read replicas replay the primary’s Write-Ahead Log (WAL) and depend on RUNNING_XACTS records to build consistent MVCC snapshots before accepting read-only connections. A transaction that creates more than the usual 64 cached active subtransactions, through excessive SAVEPOINT use or PL/pgSQL exception blocks, overflows its subtransaction cache, forcing visibility checks to consult the disk-backed pg_subtrans structure. Because any new snapshot can inherit this overflow state, one transaction can impose SLRU lock contention and expensive lookups on unrelated queries throughout the cluster, with a benchmark showing throughput falling from roughly 7,200 to about 160 TPS until the transaction ends. Overflow also affects newly created replicas because their RUNNING_XACTS records may omit active subtransaction IDs; since pg_subtrans changes are not WAL-logged, replicas cannot reconstruct the missing information and remain unable to enable hot standby until a complete record arrives, relevant transactions finish, or a checkpoint establishes a clean state. PostgreSQL 18 offers no built-in prevention mechanism, so recommended mitigations include keeping transactions short, applying transaction timeouts, monitoring backend subtransaction overflow and pg_subtrans activity, and, potentially, increasing the compiled cache limit or adopting future CSN-based snapshot designs.

No tracked trend matches for this post yet.

Use this post, company, and trend context to find content marketing opportunities, perform competitive analysis, or address product feature gaps via the Plushcap MCP server or the Plushcap API.