Postgres Work Mem Calculator

Introduction:

A PostgreSQL server can run comfortably for hours and then exhaust memory when several expensive plans overlap. The danger comes from multiplication: work_mem is available to individual sort or hash operations, not reserved once for the database or once for each connection. A query with several memory-using nodes may receive several allowances, and parallel query can create more processes that need them.

Memory planning therefore begins with a server budget. RAM already committed to shared_buffers, the operating system, background work, and active backends is not available to sorts and hashes. A safety margin protects against estimates that are too neat for a changing production workload. The remainder has to cover every memory operation that may be active at the same time.

Connection limits and active-query concurrency answer different questions. max_connections is the largest admitted population, while observed active sessions are the smaller group likely to execute memory-heavy plans together. Sizing a global default only for the usual active group may be reasonable, but the all-connections case reveals how severe an unexpected concurrency spike could become.

• Sort-heavy work can allocate up to work_mem for each qualifying operation before spilling.
• Hash-heavy work can use work_mem multiplied by hash_mem_multiplier.
• Parallel work can repeat memory demand across workers and the leader.
• Temporary-file evidence shows that a plan spilled, but file size is not an exact measure of the extra memory needed to avoid it.

A conservative global value is usually safer than giving every session the largest setting a reporting query might need. Larger allowances can be tested for one role, session, or transaction after a representative plan shows that extra memory reduces useful disk work. Query plans, data distribution, indexes, statistics, and concurrency all change the real peak, so any calculated setting remains a starting point for measurement.

An undersized allowance can increase temporary I/O and latency. An oversized allowance can cause swapping or an out-of-memory failure across the whole server. The useful choice balances both risks and is checked under the workload that will actually run.

How to Use This Tool:

Build one budget for a server or container boundary, then replace profile assumptions with observed workload data.

1. Choose a Workload profile as a starting point, or use Custom to keep your own concurrency and plan assumptions.
2. Enter Total server RAM, configured shared_buffers, and a conservative OS and background reserve. The two reserves must leave memory for query work.
3. Set Active work_mem sessions, Memory nodes per query, and Parallel workers per query from pooler, activity, and representative plan evidence. Active sessions cannot exceed max_connections.
4. Enter the current work_mem, configured hash_mem_multiplier, estimated hash-node share, other memory per backend, and safety margin. Use Observed temp spill only for a measured query that may justify a scoped override.
5. Compare Recommended work_mem with current demand, remaining margin, and the all-connections cap. Treat a warning as a reason to revise assumptions or narrow the setting's scope before copying configuration.

Interpreting Results:

Recommended work_mem is the active-workload budget divided across modeled memory-operation slots, rounded down to a practical PostgreSQL setting. It is not a promise that every sort or hash will remain in memory.

A Healthy result means current modeled demand fits and the additional caution rules did not fire. Caution can mean the all-connections scenario exceeds physical RAM or the recommendation falls below 4 MiB. Risk means current modeled work_mem demand is greater than the safety budget.

The all-connections cap is a stress-case comparison, not a suggested default. Check the concurrency curve, query plans, temporary-file statistics, swap activity, and host memory pressure before changing a production setting. A spill-based override belongs at the narrowest workable scope and still needs a repeated plan test.

Technical Details:

The model converts GiB inputs to MiB, estimates the number of simultaneous memory-operation slots, and distributes a safety-adjusted memory budget across those slots. Hash weighting and parallel processes affect the denominator, so either can reduce the safe per-operation value even when total RAM is unchanged.

Formula Core

Let h be the hash-heavy share as a decimal and m be hash_mem_multiplier. Their weighted operation multiplier is:

Active operation slots multiply active sessions A, average memory nodes per query n, the leader plus w parallel workers, and the effective node multiplier:

Available query memory removes shared buffers G, the operating-system reserve O, and other active-backend memory Ab from total RAM R. Safety margin s is then applied before division:

The recommendation rounds W_raw down so display-friendly increments never exceed the modeled budget.

Rule Core

The same budget is recalculated with max_connections in place of active sessions to produce the all-connections cap. The one-query cap uses one active session. These comparisons expose different risk boundaries without treating every admitted connection as simultaneously busy.

When an observed spill is greater than zero, the scoped override hint starts at 120% of that value. If at least half of the modeled nodes are hash-heavy, the hint is divided by hash_mem_multiplier. It is rounded up with the same increment ladder and capped at the one-query limit. This is a repository-authored planning heuristic, not a PostgreSQL guarantee that the query will stop spilling.

Accuracy Notes:

The budget depends on estimates for simultaneous sessions, plan nodes, hash share, parallel workers, and other backend memory. PostgreSQL does not reserve the full allowance in advance, and actual plan behavior may use less or more total memory than a simple slot count suggests.

• Use configured values for shared_buffers, work_mem, and hash_mem_multiplier, not generic percentages or version defaults.
• Use representative plans and observed concurrency for the workload class being tuned.
• Validate changes with temporary-file metrics, plan output, resident memory, swap, and out-of-memory risk.
• Keep a larger query-specific setting scoped unless broad workload evidence supports a new global default.

Worked Examples:

References:

• Resource Consumption, PostgreSQL 18 Documentation.
• EXPLAIN, PostgreSQL Documentation.
• How to tune work_mem in PostgreSQL, Simplified Guide.
• How to tune shared_buffers in PostgreSQL, Simplified Guide.