DATABASES Signal 61
Postgres large tables trigger cascade deletes and autovacuum delays causing outages
↗
Postgres performance degrades predictably with large tables due to cascade deletes and delayed autovacuum, risking outages under load
Large tables in Postgres are a common but often overlooked source of production outages. The problems compound as data grows, making scaling decisions urgent for teams running high-write workloads. Partitioning or sharding may be necessary to avoid cascading failures
Written by elseif from the cluster below · every claim links back to a sourceThe three things worth knowing
Cascade deletes on large unpartitioned tables generate excessive WAL traffic that saturates replicas
Autovacuum triggers late on large tables, allowing dead tuples to accumulate and degrade query performance
Partitioning reduces autovacuum delays and WAL pressure by splitting large tables into smaller chunks
THE READ
What the cluster adds up to.
Large tables in Postgres create predictable failure modes that emerge under routine operations. The example of cascade deletes shows how a single delete operation on a parent table can trigger thousands of dependent row deletions. This generates enough WAL traffic to saturate network links between primary and replicas, causing replica lag that redirects all read traffic to the primary. The primary then faces compounded load from both writes and reads, leading to outages from what appears to be a simple background job
Autovacuum presents a separate but related scaling challenge. The default configuration triggers vacuum only after 20% of a table's rows become dead tuples. On a 500 million row table, this means 100 million dead rows accumulate before cleanup begins. Vacuum operations on tables larger than RAM compete with user queries for disk and shared_buffers, creating performance degradation during the cleanup itself. The problem worsens over time as each vacuum cycle leaves more dead tuples behind
Partitioning offers a practical mitigation by splitting large tables into smaller logical units. Each partition triggers autovacuum independently at 20% of its smaller row count, reducing the dead tuple threshold from 100 million to 8 million in the example of monthly partitions. This also enables parallel vacuum workers to operate on different partitions simultaneously. However, partitioning requires a suitable key and doesn't eliminate the fundamental resource contention between vacuum and user queries
Sharding represents a more comprehensive solution by distributing data across multiple database clusters. This isolates the performance impact of large tables to individual shards, preventing cascading failures across the entire system. The tradeoff comes in operational complexity, as sharding requires maintaining multiple clusters and a routing layer. Teams must weigh this complexity against the predictable outages that large tables create in single-cluster deployments
Written by elseif from the cluster below · checked for specifics the sources never containedTHE CLUSTER