In Amazon Redshift manual WLM, how much memory does a query get and what happens when it needs more?

## Where a query's memory comes from Amazon Redshift reserves part of each compute node's RAM for query execution. Under manual WLM that pool is carved up statically: each queue declares `memory_percent_to_use`, and within a queue the share is divided **evenly** among the `query_concurrency` slots. A queue with 40% of memory and concurrency 8 gives every running query 5% of the cluster's query memory, whether the query is a single-row lookup or a nine-table join. The allocation is fixed at admission time and does not expand while the query runs. This is the central design constraint of manual WLM. Concurrency and per-query memory are two ends of the same lever: you cannot raise one without lowering the other inside a queue. ## What spilling to disk means Most of the memory a query consumes goes into intermediate structures — hash tables built for hash joins, sort buffers for `ORDER BY` and for merge steps, aggregation state for `GROUP BY`. When one of these outgrows the slot's allocation, Redshift does not abort the query. It writes the overflow to temporary blocks on local disk and reads it back, which turns a memory-speed operation into a storage-speed one. On a large join this is commonly the difference between seconds and many minutes, and the temp blocks also consume disk space that counts toward the cluster's storage. How to confirm it happened: ```sql -- which steps of a query spilled? SELECT query, stm, seg, step, label, is_diskbased, workmem, rows FROM svl_query_summary WHERE query = 12345 ORDER BY stm, seg, step; ``` `is_diskbased = 't'` marks a step that spilled. `STL_ALERT_EVENT_LOG` records a matching alert with a suggested remedy, and the WLM metric `query_temp_blocks_to_disk` (visible in `SVL_QUERY_METRICS_SUMMARY`) quantifies how much was written. On newer clusters the `SYS_QUERY_HISTORY` and `SYS_QUERY_DETAIL` views expose the same picture without needing to know which STL table to open. ## Borrowing more slots for one statement Some statements are legitimately memory-hungry and run rarely — a bulk `VACUUM`, a large `INSERT ... SELECT`, a nightly rebuild. Rather than sizing the whole queue for them, a session can claim several slots at once: ```sql SET wlm_query_slot_count TO 3; VACUUM sales; SET wlm_query_slot_count TO 1; ``` The statement now gets three slots' worth of memory, and while it runs the queue has three fewer slots for everyone else — so this is a deliberate trade of concurrency for headroom, not free memory. It applies to manual WLM; it is not the tool for automatic WLM, which already sizes allocations per query. ## The four real remedies When a workload spills persistently, the options in rough order of preference are: 1. **Make the query need less memory.** A spilling hash join often means the optimizer chose a huge intermediate result: missing or stale statistics (fix with `ANALYZE`), a join that redistributes a large table because the distribution keys do not line up, or a `SELECT *` dragging wide columns through a sort. This is the fix that helps every future run. 2. **Lower the queue's concurrency** so each remaining slot is larger. Fewer queries run at once, each with more room; total throughput frequently improves even though the queue admits fewer queries. 3. **Raise the queue's memory percentage**, taking it from a queue that does not need it. The percentages across all queues must remain within 100%. 4. **Claim extra slots for the one offending statement** with `wlm_query_slot_count`. ## Why automatic WLM exists Every number above is a guess that goes stale as data grows and queries change. Automatic WLM removes the arithmetic: Redshift estimates each query's memory requirement and admits as many queries as actually fit, giving the memory-hungry aggregation a large allocation and running dozens of cheap dashboard queries alongside it. Spill still happens when an estimate is wrong, and the diagnostics are the same, but you no longer maintain the percentages. In an interview, describing the slot arithmetic accurately and then saying "which is exactly why AWS recommends automatic WLM" shows you understand both the mechanism and why it was replaced. ## A common trap Raising queue concurrency to "handle more load" is the instinctive move and is usually wrong under manual WLM: it shrinks every slot, pushes more queries into spilling, and the cluster gets slower under exactly the load you were trying to absorb. Queueing and spilling are opposite failure modes of the same dial, and you have to know which one you are looking at before you turn it.