Can your Postgres survive a bad query?

Summary

Postgres memory tuning is difficult because work_mem applies per operation, not per query, and parallel workers multiply memory usage without a query-wide cap. Some executor structures, such as those in recursive queries, have no disk-backed fallback and can grow until the backend runs out of memory. A benchmark using a recursive query that exhausts memory tested ClickHouse Managed Postgres, Cloud SQL, PlanetScale, and Amazon RDS on 16 GiB instances. ClickHouse Managed Postgres enforces a memory ceiling at 57% of host RAM, rejecting queries that exceed it, while RDS allows backends to consume nearly all RAM until the host crashes. Cloud SQL and PlanetScale enforce similar caps, but ClickHouse Managed Postgres’s ceiling is lower, a deliberate tradeoff that returns clean query failures instead of forcing every client through crash recovery.

Takeaways

1. 1

   Postgres work_mem applies per operation per process, not per query, and parallel workers multiply memory usage without a query-wide cap, making memory tuning opaque.

2. 2

   Some executor structures (e.g., in recursive queries) have no disk-backed fallback and can grow until the backend runs out of memory, posing a crash risk.

3. 3

   ClickHouse Managed Postgres enforces a lower memory ceiling (57% of host RAM) to reject runaway queries, while RDS allows higher memory usage but risks crashes and cache competition.

FAQ