You own a database serving both interactive OLTP traffic and heavy batch reporting on the same instance. How do you decide the per-operation memory budget for sorts and hash aggregates (PostgreSQL's work_mem, SQL Server's memory grants, Oracle's PGA target), and what policy keeps one workload from starving the other?

## Frame it as an allocation problem The question is not "what number should this setting be" — it is "how do I divide a fixed amount of RAM among competing consumers with different latency requirements". A machine's memory goes to: the OS and filesystem cache; the database buffer pool (cached data/index pages); per-connection overhead; and per-operation working memory for sorts, hash joins, hash aggregates, and distinct. Growing any one shrinks another. Handing every query enough memory to never spill would starve the buffer pool, and a cold buffer pool hurts *every* query, including the ones you were trying to protect. ## The multiplication that catches people The per-operation budget is charged **per operator instance**, not per query. A reporting plan with two sorts and two hash operators claims four budgets simultaneously. Add parallelism and each worker may hold its own. Now multiply by concurrent sessions. The honest exposure is: `peak_working_memory ≈ concurrent_heavy_queries × operators_per_plan × parallel_workers × budget` A "safe-looking" 64 MB with 20 concurrent reports, 3 stateful operators each, and 4 workers is 15 GB. This is why the naive fix — raise the global setting until the slow report stops spilling — is the standard route to an out-of-memory kill or heavy swapping, which converts a slow query into an outage. ## Sizing method 1. **Reserve from the top.** Leave the OS its share, then size the buffer pool for the hot working set (guided by cache hit ratio and physical read rates). What remains is the working pool. 2. **Estimate the multiplier.** Measure, don't guess: how many sessions actually run stateful plans at peak, and how many such operators a typical heavy plan carries. 3. **Divide conservatively** to get the default, accepting that the default will make large sorts spill. That is intentional. 4. **Differentiate.** Give batch roles or specific jobs a much higher session-level value, justified by their low concurrency. A nightly job running alone can safely claim a large multiple of the OLTP default. 5. **Bound concurrency.** The variable you control most reliably is not the per-operator size but how many heavy operators exist at once. A connection pool or job scheduler that admits N heavy jobs turns an unbounded product into a computed one. Engines with explicit memory grants make waiters queue for memory; where the engine will not do that, the pool must. ## Policy levers beyond the number - **Workload isolation.** Reporting on a read replica removes the contention entirely and is often cheaper than tuning. The cost is replication lag semantics and an extra instance. - **Resource governors / resource manager.** Where the engine supports classifying sessions into pools with caps (SQL Server Resource Governor, Oracle Resource Manager), express the policy declaratively instead of relying on discipline. - **Plan-shaping over memory-buying.** Every index that supplies an interesting order converts a memory-hungry hash aggregate into a streaming grouped aggregate. Every predicate pushed down shrinks the operator's input. Structural fixes free memory permanently; setting changes only redistribute it. - **Pre-aggregation.** If the same heavy grouping runs nightly, a maintained summary removes the operator from the system rather than funding it. - **Container limits.** In a cgroup/container, exceeding the limit is an OOM kill, not swapping. Budgets must be set against the *container's* limit with real headroom, because the failure is abrupt. ## Deciding what "good" looks like Define the objective explicitly, because OLTP and batch want different things: - **OLTP:** predictable p99 latency. Sorts should be small or absent; a spill on this path is a design smell to fix with an index, not with memory. - **Batch:** throughput within a window. Spilling is acceptable; what matters is that it completes and does not damage the OLTP path while it runs. That asymmetry is the whole policy: **protect latency, let throughput degrade gracefully.** A batch job that runs 30% slower because it spilled is a success; an OLTP p99 that doubled because a batch job took the memory is a failure. ## Instrumentation that makes the policy real Track, at minimum: temp/spill bytes per workload class; count of queries spilling per hour; memory-grant wait time (where the engine exposes it); host and container memory headroom; and buffer cache hit ratio, so you notice when raising working memory quietly starved the pool. The leading indicator is spill *volume* by class, which moves weeks before anyone files a ticket about duration. ## The trap answer Being asked for "the right value" invites a number. There isn't one — it depends on RAM, workload mix, plan shapes, parallelism, and concurrency. The credible answer states the budget equation, names the multiplication factors, differentiates by workload class, bounds concurrency as the real control, prefers structural fixes that remove memory demand, and explicitly accepts spilling as designed degradation for the batch tail.