Postgres Work Mem Calculator

Introduction:

PostgreSQL memory tuning becomes risky when a single setting is treated like a single allocation. The setting named work_mem is not a shared pool for the whole server. It is a per-operation allowance used by sorts, hash joins, hash aggregates, memoize nodes, some IN processing, and related plan work before those operations start writing temporary data to disk.

That per-operation behavior is useful because small OLTP queries do not reserve large memory blocks just by connecting. The same behavior can surprise teams during reporting jobs, migrations, or analytical queries. One query can use several memory-consuming plan nodes, parallel workers can multiply the number of processes doing that work, and many active sessions can reach the same high-memory shape at once.

Good sizing starts by separating the global default from role-level and session-level exceptions. A small global setting keeps ordinary pooled traffic from overcommitting memory. A reporting role, maintenance session, or one reviewed query can then receive a larger temporary allowance when the plan and temp-file evidence justify it.

Hash-heavy plans need special attention because PostgreSQL lets hash operations use work_mem multiplied by hash_mem_multiplier. A setting that looks modest for pure sort work can become much larger when many hash aggregates or hash joins run together. Parallel query adds another multiplier because workers and the leader can each allocate memory for plan nodes.

The safest answer is a starting point, not a permanent truth. Query plans change with statistics, indexes, parameters, data skew, and PostgreSQL versions. The setting should be checked against observed active sessions, temp blocks, temporary file logs, and EXPLAIN (ANALYZE, BUFFERS) output before it becomes a production default.

How to Use This Tool:

Use the calculator as a memory-budget worksheet for one cluster, role, or workload class.

1. Select a Workload profile such as Web / OLTP, Mixed workload, Reporting queries, or Warehouse / batch. Then edit the numbers to match real telemetry.
2. Enter Total server RAM, shared_buffers, and OS and background reserve. These values define the memory that is not available for per-query work.
3. Set max_connections, Concurrent active sessions, Sort and hash nodes/query, and Parallel workers. Use active query counts from pooler or pg_stat_activity data rather than assuming every connection is busy.
4. Enter the current work_mem and hash_mem_multiplier. The result compares current demand with the calculated recommendation and the all-connections cap.
5. Open Advanced for Safety margin, Other backend memory, Hash-heavy node share, an observed Temp spill target, and a cluster label.
6. Review Memory Budget, Tuning Review, and Scenario Ladder before copying the Config Snippet. Use the chart tabs to see memory stack and concurrency sensitivity.

If the active-session assumption exceeds max_connections, or if the current setting can exceed the modeled safety budget, fix the inputs or lower the default before treating the snippet as a usable starting point.

Interpreting Results:

Recommended global work_mem is the rounded-down setting that fits the modeled active workload after shared buffers, operating-system reserve, other backend memory, and safety margin are removed. It is intentionally conservative because a global default applies to many queries that do not need more memory.

• Memory Budget shows the arithmetic behind total RAM, reserves, active work slots, recommended demand, current demand, current margin, and the all-connections cap.
• Tuning Review turns the budget into action cues, including whether the current setting is safe, whether parallel workers are multiplying memory, and whether a spill target suggests a SET LOCAL override.
• Scenario Ladder compares the selected workload with conservative, reporting, warehouse, and all-connections scenarios.
• Memory Budget Stack and Concurrency Curve show where memory goes and how quickly the safe setting falls as active sessions rise.

A positive margin does not prove that every query will avoid temp files. It only says the modeled active workload fits the chosen memory budget. A low or negative margin means the global default, concurrency assumption, hash weighting, or reserve values need review before production use.

Use the Temp spill override only as a review hint for one measured query. It should be validated with an actual plan run and applied with a role, session, or transaction scope rather than copied blindly into the global configuration.

Technical Details:

PostgreSQL subtracts shared buffers and background needs from host memory before per-query work memory should be considered. The remaining budget is then divided by weighted work slots. A work slot is not a connection. It is a modeled combination of active sessions, simultaneous sort or hash nodes per query, leader and worker processes, and the extra allowance granted to hash-heavy operations.

The model treats hash work as a weighted multiplier rather than assuming every plan node is a sort. If 40 percent of work nodes are hash-heavy and hash_mem_multiplier is 2, the effective node multiplier is 1.4. That makes the global recommendation lower than a pure-sort estimate while still explaining why hash-heavy workloads often spill or overcommit sooner than expected.

Formula Core:

The core budget divides usable work memory by weighted active work slots, then rounds down to a practical PostgreSQL setting.

With 64 GB RAM, 16 GB shared buffers, 12 GB reserved for the OS and background work, 18 active sessions, 2.5 nodes per query, one parallel worker, a 25 percent safety margin, 16 MB other backend memory per active session, 40 percent hash-heavy nodes, and hash_mem_multiplier = 2, the weighted slot count is 126. The usable safety budget is about 27,432 MB, so the raw per-slot allowance is about 218 MB before practical rounding.

Temporary spill estimates are not exact memory requirements. Temporary files can be more compact than in-memory data structures, and a plan may spill in more than one node. The override hint adds headroom for one reviewed query, then caps it against the single-query budget so one fix does not create a wider memory incident.

Accuracy Notes:

This calculator models memory pressure; it does not inspect a live PostgreSQL server. Treat the output as a planning estimate that must be tested against real plan output and production concurrency.

• Use observed active sessions rather than max_connections when a pooler limits real concurrency.
• Check temporary blocks, temporary file logs, and plan nodes before raising a global default.
• Remember that extensions, maintenance work, autovacuum, the operating system cache, and non-Postgres processes still need memory.
• Recheck after major schema changes, PostgreSQL upgrades, reporting-role changes, and workload shifts.

Worked Examples:

Mixed production cluster. A 64 GB cluster with 16 GB shared buffers, 12 GB reserve, 18 active sessions, 2.5 memory nodes per query, one parallel worker, 40 percent hash-heavy work, and a 25 percent margin lands near a 216 MB rounded recommendation. If the current value is 64 MB, Current margin should stay positive, and the Scenario Ladder can show what happens when reporting concurrency grows.

Reporting role with spills. A reporting query spills around 2 GB while the global default remains modest. Entering the spill target can produce a Temp spill override for SET LOCAL work_mem. That override belongs in a tested transaction, session, or role path, not as a global setting for every login.

Connection-limit trap. A service with max_connections set to 500 but only 25 active pooled sessions may look safe under the active model and unsafe under the all-connections cap. That split is useful. It shows why pooling policy and role limits matter as much as the memory value itself.

FAQ:

Glossary:

work_mem

The base memory allowance for one sort, hash table, or similar query operation before temporary files are used.

hash_mem_multiplier

The multiplier that lets hash-based operations use more memory than the base work_mem value.

Active session

A session likely to run memory-consuming plan work at the same time as other sessions.

Work slot

A modeled sort or hash operation after accounting for active sessions, plan nodes, parallel workers, and hash weighting.

Temp spill

A plan operation writing temporary data to disk because its memory allowance is not enough for the work.

SET LOCAL

A transaction-scoped way to test a higher setting for one reviewed query without changing the global default.

References:

• Resource Consumption, PostgreSQL Global Development Group.
• EXPLAIN, PostgreSQL Global Development Group.