The dangers of Postgres subtransactions

This post details how a large number of subtransactions within a single PostgreSQL transaction can lead to cluster-wide performance degradation and prevent read replicas from accepting queries. It explains PostgreSQL's WAL replay mechanism for replicas, how transaction states are tracked via RUNNING_XACTS records, and the limitations of the subtransaction cache (PGPROC_MAX_CACHED_SUBXIDS). When the cache overflows, PostgreSQL falls back to slower pg_subtrans lookups, impacting query performance. The post provides examples of how to trigger this overflow using explicit SAVEPOINTs or PL/pgSQL exception blocks and includes a benchmark demonstrating the TPS drop. It emphasizes that a single overflowing transaction can affect unrelated queries across the cluster.