Lætitia AVROT: Work_mem: It's a Trap!

Why It Matters

The incident shows that mis‑understanding PostgreSQL’s memory model can cripple even massive hardware, making query design and monitoring critical for reliable operations.

Key Takeaways

• work_mem limits per hash/sort, not total query memory
• ExecutorState holds all operation chunks until query ends
• Many work_mem chunks can accumulate inside one context
• Use pg_log_backend_memory_contexts to diagnose memory spikes
• Fix stats, rewrite queries, set statement_timeout to limit

Pulse Analysis

PostgreSQL’s memory architecture relies on hierarchical memory contexts rather than per‑allocation frees. The work_mem setting caps the memory a single hash or sort operation may use, but each operation creates its own allocation inside the ExecutorState context, which is only released when the entire query finishes. When a query spawns thousands of such operations—especially through PL/pgSQL functions or complex joins—the cumulative memory can balloon far beyond the nominal work_mem limit, as the context retains every chunk until completion. This design choice favors performance and simplicity but can become a liability if a query’s execution plan is sub‑optimal.

Detecting these hidden memory drains requires visibility into the backend’s memory contexts. The pg_log_backend_memory_contexts function, introduced in PostgreSQL 14, dumps a detailed tree of allocations for any backend process, revealing which contexts dominate RAM usage. By examining the ExecutorState and its children, DBAs can pinpoint runaway hash tables or sorts before the operating system’s OOM killer intervenes. Coupled with accurate statistics—regular ANALYZE, custom CREATE STATISTICS, and appropriate column statistics targets—PostgreSQL’s planner can make better decisions about when to spill to disk, reducing the risk of massive in‑memory structures.

To safeguard production environments, teams should adopt a layered defense: enforce realistic statement_timeout values, monitor memory‑context logs, and refactor queries that force large in‑memory joins or functions. While adding more RAM may delay failures, it does not address the underlying inefficiency. Instead, focusing on query optimization, proper statistics, and proactive monitoring delivers sustainable performance and prevents costly outages. Organizations that embed these practices into their DevOps pipelines will see fewer surprise OOM events and more predictable scaling as workloads grow.