How to kill the OOM killer

In the world of PostgreSQL optimization, finding the perfect balance for memory allocation is often an elusive challenge. One of the most debated parameters is work_mem, which dictates the amount of memory available for complex sorting operations and hash tables before PostgreSQL is forced to write data to temporary disk files. Set it too low, and your queries will crawl as they swap to disk. Set it too high across an entire instance, and you risk an out-of-memory crash when concurrent connections spike.

At DBtune, we approach work_mem not through guesswork or static heuristics, but through a static and dynamic, mathematically rigorous methodology. This blog post explores how the DBtune agent automatically calculates the upper and lower bounds for work_mem by factoring in the maximum connection limits and the overall system resources, ensuring that your database remains highly performant and stable.

Why setting PostgreSQL work_mem too high is dangerous

To grasp why DBtune sets specific boundaries on memory allocation, we first need to understand how PostgreSQL utilizes work_mem. Unlike shared_buffers, which acts as a global cache for the entire instance, work_mem is assigned on a per-operation basis. If a complex query involves multiple sort nodes and hash joins, it can request multiple blocks of work_mem concurrently.

If you set work_mem service-wide without accounting for maximum concurrency, the math quickly becomes dangerous. Consider a scenario where max_connections is set to 1,000, and work_mem is bumped to 256MB. If all connections execute complex queries simultaneously, the database could attempt to allocate over 256GB of memory just for sorting. On many standard instances, this leads directly to the operating system's out-of-memory killer terminating the PostgreSQL process. On very busy systems, this is a real risk as the default max_connections in Amazon RDS and Aurora for many instance types is 5,000.

Hence, to not risk the safety of the system, setting work_mem at the instance level requires strict guardrails. While isolated query tuning might prefer session-level adjustments, DBtune focuses on instance-wide optimization utilizing Workload Fingerprints to strike the optimal balance across typical concurrent activity.

How DBtune calculates a safe work_mem upper bound

Rather than focusing on a single query, DBtune accounts for overall system resources when establishing the safe boundaries for work_mem. The optimization algorithm treats total RAM and max_connections as hard limits to calculate a safe operating window. Here is the technical breakdown of how we define these bounds.

The calculation starts by determining the available memory for per-connection operations. DBtune reserves memory for the operating system and the global PostgreSQL caches, such as shared_buffers. The remaining RAM forms the pool from which work_mem can be safely drawn.

available_query_memory = total_system_RAM - (shared_buffers + OS_reserved_memory)

Next, the algorithm establishes a conservative upper bound on maximum memory demand. Because a single connection can use multiple work_mem allocations for complex query plans, DBtune factors in an expected operations multiplier based on the complexity of the average workload. A higher multiplier is used for lower values of max_connections, as the risk that every single connection uses up its maximum allocated memory decreases with the number of allowed connections.

work_mem_upper_bound = available_query_memory / (max_connections * expected_operations_multiplier)

By enforcing this upper bound formula, DBtune will not increase work_mem to dangerous levels even at 100% connection utilization. If an instance has an artificially inflated max_connections setting (for example, 5,000 connections when only 50 are typically active), this mathematical upper bound will naturally constrain work_mem to a smaller, safer value. This highlights why DBtune often recommends reducing max_connections if it is unnecessarily high: Lowering the connection ceiling mathematically unlocks a higher safe upper bound for work_mem.

DBtune takes a very conservative approach, that prefers to err on the side of safety, to make sure that our AI-generated actions are trustworthy. This is not a theoretical maximum, but a practical one that is based on years of experience and input from leading PostgreSQL experts.

Real-time memory guardrails during PostgreSQL optimization

While the static formula establishes the fundamental boundaries for the optimization search space, DBtune's commitment to stability extends beyond initial calculations. Complementing this static mechanism, our memory guardrails actively monitor live utilization, providing a responsive layer of protection against excessive consumption. These guardrails are dynamic, adapting to shifting workloads, and operate in real-time to observe environmental changes as they occur. Because the operations complexity multiplier (the expected_operations_multiplier variable in the previous section) is fundamentally unknown and cannot serve as a perfect proxy for memory estimation, runtime guardrails are necessary to complement the conservative formula in a robust, twofold approach. They constitute the technical backbone of DBtune's programmatic safety framework.

PostgreSQL environments are rarely static. A Workload Fingerprint capturing a typical day might not account for an unexpected surge in heavy analytical queries. To protect against unpredictable spikes, the DBtune agent continuously observes system memory consumption during and after the tuning session.

• The agent tracks real-time RAM utilization across the instance.
• If total memory consumption exceeds the safety threshold—typically above 90% of total memory, though this threshold is adjustable—DBtune immediately intervenes.
• The system triggers a partial revert or a safe-state fallback to prevent a crash, ensuring that the tuning process never compromises production stability.

Continuous OOM protection after optimization

DBtune’s commitment to your database’s stability does not end when the tuning session finishes. The DBtune agent is designed to function as a continuous "OOM protector." Even after the optimal configuration has been applied, the agent remains active in the background, continuously monitoring your database's memory consumption.

The guardrails package in the DBtune agent's open-source GitHub repository actively evaluates CriticalityLevel constraints related to specific resource types, such as FreeableMemory and general Memory. If your workload unexpectedly spikes and memory usage crosses the configured safety threshold (typically 90%), the guardrail mechanism is triggered.

Because this occurs post-tuning, the agent operates in a protective capacity—it immediately sends you a notification alerting you of the critical memory threshold, allowing you to proactively intervene before an OOM crash occurs.

Why DBtune tunes work_mem at the instance level

Users often ask if it is possible to tune work_mem purely at the session or table level for highly isolated queries. DBtune's emphasis on instance-wide optimization is a deliberate design choice derived from how the vast majority of our users operate, prioritizing holistic performance tuning that stabilizes the entire database environment rather than optimizing for single sessions.

Tuning a single query in a vacuum is a relatively niche use case — database servers handle hundreds or thousands of concurrent queries. Our users' Workload Fingerprints typically encompass 10 to 50 distinct queries running concurrently, representing the true rhythm of their application. By optimizing at the instance level, DBtune ensures that the configuration benefits the holistic workload rather than over-optimizing one query.

How DBtune prevents PostgreSQL out-of-memory crashes

To recap, DBtune ensures safe, high-performance tuning through a multi-layered approach:

By relying on both mathematical boundaries and active monitoring, DBtune takes the guesswork out of memory allocation. You get the performance boost of an expertly tuned database, with the peace of mind that safety is programmatically guaranteed.

DBtune is a constantly evolving platform with ambitious plans in the roadmap. As our platform evolves, many new features are rolled out and new capabilities emerge. Please see the release notes for platform updates.