A report gets faster after you increase work_mem. Then the morning traffic arrives, several reports overlap, and the database starts fighting for memory. The tuning experiment answered a narrow question: can one query avoid a spill? Production asked a different one: can all admitted work fit at the same time?
Treat work_mem as an operator-level allowance within a concurrency budget, not as a per-connection reservation or a server-wide ceiling. A useful tuning decision includes the workload class, its maximum concurrency, and the behavior when that class exhausts its budget.
This is the memory side of database capacity planning. A connection pool limits one source of concurrency; it does not explain how expensive each active statement will be.
Count the work inside a query #
PostgreSQL permits multiple sort and hash operations within a statement. Hash operations can use an allowance based on work_mem × hash_mem_multiplier; multiple sessions compound the demand. Parallel execution can introduce additional consumers. These are implementation details worth checking against your server version, not a reason to multiply every plan node blindly. PostgreSQL resource settings.
For a first review, write a deliberately conservative worksheet:
query workspace estimate
= concurrent executions
× simultaneously active operator/process allowances
available workspace
= host/container memory limit
- shared and baseline process memory
- maintenance and replication allowance
- operating-system and safety headroomThe first expression is a planning model, not an upper bound enforced by PostgreSQL. Operator lifetimes, shared parallel structures, and other allocations matter. Validate the model with the actual plan and observed memory pressure.
For an illustrative non-parallel workload, suppose 20 simultaneous reports each have one active sort and one active hash operation. At 32 MiB base allowance and a hash multiplier of 2, the worksheet gives 20 × (32 + 64) = 1,920 MiB. Raising the base to 128 MiB changes that to 7,680 MiB before baseline memory. Those are hypothetical allowance calculations, not measured resident memory or recommended settings.
The important output is the multiplication. A seemingly small session setting can spend most of the spare memory on a modest server.
Diagnose the spill before removing it #
Start with the slow statement’s plan on representative, non-sensitive data. Look for estimation errors, wide intermediate rows, avoidable sorts, and joins that produce far more rows than the application consumes.
PostgreSQL reports sort method and memory or disk use in execution plans; hash batches help identify spilling. EXPLAIN ANALYZE actually executes the statement, so do not run an expensive production query casually. Its MEMORY option concerns planning memory, not a complete execution-memory profile. Using EXPLAIN, EXPLAIN reference.
Here is a bounded lab experiment. It creates no permanent table and does not assume that either setting must produce a particular plan or speedup:
BEGIN;
SET LOCAL statement_timeout = '10s';
SET LOCAL work_mem = '4MB';
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT n, md5(n::text) AS sort_key
FROM generate_series(1, 100000) AS g(n)
ORDER BY sort_key;
ROLLBACK;Repeat with a larger allowance in a disposable environment, then repeat under representative concurrency. The SQL is a reviewable PostgreSQL 18 example, not a benchmark result; it was not executed against a PostgreSQL server for this article.
Record query latency, throughput, temporary-file activity, and host/container memory pressure together. A lower isolated latency is not a win if tail latency for transactional traffic worsens.
Scope the exception to the workload #
If a reporting query justifies more workspace, prefer a controlled transaction-local change over immediately raising the global default. SET LOCAL ends with the transaction, which helps avoid leaking a report’s setting into the next borrower of a pooled connection. SET documentation.
That still leaves admission control. An application with unrestricted access to SET is not made safe by a team convention. Route reports through a bounded worker pool, establish role and infrastructure limits where supported, and keep interactive requests out of that queue. Reserve operational headroom for maintenance instead of allocating every remaining byte to foreground queries.
Do not add an index solely because a sort exists. Compare its read benefit with its write-amplification cost. Sometimes narrower projections or a better query boundary are cheaper than either more RAM or another index.
Failure policy and the release decision #
| Evidence | Action |
|---|---|
| Occasional spill with acceptable latency | Keep it; spilling is not automatically a defect |
| Persistent spill on one important query | Inspect its plan, then test a scoped allowance |
| Memory pressure when reports overlap | Reduce admitted concurrency before raising limits |
| Timeout after admission | Cancel and report failure; avoid immediate unbounded retries |
| More memory, unchanged latency | Investigate CPU, I/O, cardinality, and contention instead |
A review should name the workload owner and its limit: for example, a report queue with two active workers and a measured maximum input size. The numbers must come from your workload, not this article. State what happens to the third report and how the caller sees its wait time.
The trade-off is straightforward: less spilling can buy speed, while larger allowances reduce the number of expensive operations the host can safely overlap. Spilling itself spends disk bandwidth and latency, so setting everything tiny is not a universal answer either.
My release gate would be a concurrent replay with transactional traffic present, an explicit rollback setting, and alerts that distinguish queue delay from database execution time. The result should be a workload policy, not a magic work_mem value copied between machines.
For the broader decision process, continue with database engineering and queue backpressure.