Memory issue due to query

Hook: Interviewers love this because a query that is “fine” on 1,000 rows can quietly become a memory monster on 10 million rows.

Question: How do you troubleshoot and fix a SQL query that is causing a memory issue?

Answer: Most query memory problems happen because the database has to hold a big sort, hash join, hash aggregate, or huge intermediate result in memory. The fix is usually not “add more RAM”; it is to reduce rows earlier, return fewer columns, and help the optimizer with better indexes or a simpler plan. I would first inspect the execution plan, find the operator that is using memory, and then rewrite the query so the database does less work.

Interview-Ready Answer: “I would first confirm which part of the plan is using memory, usually a sort, hash join, or hash aggregate. Then I would reduce the amount of data flowing through the plan by filtering earlier, selecting only needed columns, and adding or adjusting indexes so the engine can avoid large in-memory work areas. In PostgreSQL, for example, I also watch for settings like work_mem, because it applies per sort or hash node, so one query can use several times that amount.”

What is really happening

A query is not one block of memory. It is a plan made of operators, and each operator may ask for its own working space. The most common memory-hungry operators are Sort, Hash Join, and Hash Aggregate. A Sort needs space to order rows; a hash operator needs space to build a lookup table for fast matching or grouping.

1. The optimizer chooses a plan based on table size, indexes, and estimated rows.
2. During execution, each memory-heavy operator gets a budget from the database engine.
3. If the data fits, the work stays in memory and runs fast.
4. If it does not fit, the engine spills to disk or, in bad cases, the query fails with an out-of-memory type error.
5. The real fix is usually to shrink the data before it reaches that operator.

Important mental model: most “memory issues” are really “too much data too late.”

Operator trade-offs

How to debug it step by step

1. Check the execution plan with the database’s plan tool. In PostgreSQL, EXPLAIN (ANALYZE, BUFFERS) is the usual starting point.
2. Look for big sorts, hash nodes, wide row outputs, or huge row-estimate errors.
3. Compare estimated rows to actual rows. If the estimate is far off, the optimizer may choose a memory-heavy plan.
4. Trim the query: remove unused columns, remove unnecessary DISTINCT, and filter as early as possible.
5. Add or adjust indexes so the engine can seek instead of scanning and sorting everything.
6. If the database supports it, check spill indicators, temp-file growth, or memory grant waits.

When to use what

Use a rewrite when the query shape is the problem, use an index when the access pattern is the problem, and use configuration tuning only after the query itself is sane. In PostgreSQL, the default work_mem is often 4 MB, and it is per sort/hash node, not per query. That means one query with several memory-heavy operators, or several parallel workers, can consume far more than you first expect.

Complexity notes

• Sort: about O(n log n) time; memory grows with the number and width of rows.
• Hash Join / Hash Aggregate: usually O(n) average time, but memory depends on the size of the build side or number of groups.
• Nested Loop: low memory, but worst-case time can be O(n*m).

Edge case: a query can look small but still use a lot of memory if it creates a wide intermediate result, for example after joining many columns or using a window function over a large partition.

Version gotcha: in PostgreSQL, CTEs were treated as optimization fences in older versions, but newer versions can inline them unless marked MATERIALIZED. That difference can change memory use a lot.

Real-World Example: Imagine a checkout service in an e-commerce app that needs to list recent paid orders with customer details. A developer adds DISTINCT plus ORDER BY created_at DESC after joining orders, payments, and customers. It works in staging, but in production the query suddenly spikes memory, spills to disk, and p95 latency jumps from 200 ms to 6 seconds.

The logs show temp file growth, the database node starts swapping, and some app pods get killed because the database and app are fighting for the same machine’s memory. Users see slow page loads and failed order-history requests. The bug was not one line of SQL; it was a plan shape that forced the engine to hold too much data before it could return anything.

Follow-up & Tricky Questions:

• How do you tell whether the issue is memory, CPU, or I/O? Memory issues usually show spills, temp-file growth, or memory-grant waits; CPU issues show high processor time; I/O issues show long waits on reads and writes. The execution plan tells you which operator is the bottleneck.
• Which SQL operators most often consume memory? Sorts, hash joins, hash aggregates, and window functions over large partitions are the usual suspects. They need working space to organize rows before they can finish.
• What is work_mem? In PostgreSQL it is the memory budget for each sort or hash operation, not for the whole query. That means several operators, or several parallel workers, can multiply the total memory use.
• When should you add an index instead of rewriting the query? Add an index when the query is logically fine but the engine must scan or sort too much data. Rewrite when the query shape itself creates a huge intermediate result, such as unnecessary DISTINCT or a broad join.
• Why can a query be fast in staging but fail in production? Small data hides bad plan shapes. Once the row count grows, the same sort or hash can exceed memory and spill or fail.
• Does a CTE always reduce memory? No. Depending on the database and version, a CTE may be inlined or materialized, and materialization can increase memory or temp-space use.
• Does LIMIT always save memory? Not always. If the database must sort the full result before applying the limit, the memory savings may be small. A matching index or a better filter is what really helps.

• Trick: Can more indexes fix every memory problem? No. Extra indexes can help reads, but they do not fix a bad query that builds a huge result set, and they slow writes.
• Trick: If a query spills to disk, is it broken? Not necessarily. A spill is a warning sign, not always a failure, but it usually means the query is slower than it needs to be and should be tuned.
• Trick: Is a hash join always worse than a nested loop? No. A hash join is often much better for large inputs. The right choice depends on row counts and indexes.

Common Mistakes:

• Mistake: blaming the database server first. Fix: inspect the query plan and row counts first; the plan usually reveals the real cause.
• Mistake: using SELECT *. Fix: return only the columns the caller needs so the engine moves less data.
• Mistake: adding LIMIT and thinking the problem is solved. Fix: make the filter selective or add an index so the engine does not sort a giant set first.
• Mistake: assuming a memory issue means a memory leak. Fix: most SQL problems are plan-shape problems, not leaks.

Memory Hook: Think of the query like packing a suitcase: SELECT * adds too many clothes, ORDER BY forces you to arrange everything at once, and the zipper pops. The fix is to pack less and fold earlier.

Cheat Sheet:

• Memory-heavy operators: sort, hash join, hash aggregate.
• Most fixes start with fewer rows and fewer columns.
• Check the execution plan before changing config.
• Indexes help when they reduce scanning or sorting.
• In PostgreSQL, work_mem is per operator, not per query.
• Spills are a sign to tune, not just to add RAM.

Practice Tasks:

• Write a query with ORDER BY and then remove unused columns; compare the plan shape.
• Add an index that matches a filter plus sort, then see whether the sort operator disappears.
• Take a join query and rewrite it with tighter filters or keyset pagination.