Postgres Memory Leaks: The Silent Killer

The Memory Tightrope

The ClickHouse blog post effectively highlights a significant vulnerability in PostgreSQL's memory management, particularly concerning recursive queries with UNION and the potential for unbounded memory consumption. The detailed breakdown of how work_mem, hash_mem_multiplier, and parallel query execution interact to cause memory exhaustion and disk spilling is highly instructive. The comparison of failure modes across different managed PostgreSQL providers (ClickHouse Managed Postgres, Google Cloud SQL, PlanetScale, RDS) provides valuable, practical insights for users choosing a database service. The article's strength lies in its clear demonstration of how seemingly simple queries can become performance nightmares under load, and how different providers handle these situations. The emphasis on unbounded memory structures like the hash table used in recursive CTEs with UNION is a critical takeaway, as it bypasses work_mem limits and can lead to outright crashes or performance degradation. The benchmarking methodology, while specific to certain providers, is robust enough to illustrate the core problem.

However, a potential limitation is the focus on specific failure modes and providers, which might not generalize perfectly to all PostgreSQL deployments or other database systems. While the article correctly identifies the issue with WITH RECURSIVE ... UNION and its non-spilling hash table, it could delve slightly deeper into alternative query patterns or indexing strategies that might mitigate this specific problem within PostgreSQL itself, rather than solely relying on external management solutions. The benchmark also assumes a specific type of workload; real-world applications might exhibit different memory usage patterns. Despite these minor points, the article serves as an excellent cautionary tale and a call to action for developers and database administrators to understand and proactively manage memory usage in their PostgreSQL instances.

Key Points

• PostgreSQL's work_mem setting is per-operation, not per-query, and its interaction with parallel execution can lead to significant memory multiplication.
• Queries utilizing WITH RECURSIVE and UNION can create hash tables for deduplication that reside in memory contexts not subject to work_mem spilling, leading to unbounded memory growth.
• Managed PostgreSQL providers exhibit different resilience to memory exhaustion, ranging from graceful error handling (ClickHouse Managed Postgres) to cluster-wide crashes (RDS) or proactive query termination (Cloud SQL, PlanetScale).
• Understanding and monitoring memory usage, especially for complex or recursive queries, is crucial for maintaining PostgreSQL reliability.

---

📖 Source: Can your Postgres survive a bad query?