agent's %%sql query returned 10M rows: OOM in the notebook kernel

TL;DR

A 10M-row result set materialized into a pandas DataFrame needs gigabytes of RAM, far more than the 10M rows suggest, because of Python object overhead per value. Do the aggregation in SQL (GROUP BY, COUNT, AVG) so only the summary crosses into the kernel, or read with chunks and process streaming. If you truly need row-level data, sample or filter in the query before it ever reaches pandas.

agent's %%sql query returned 10M rows: OOM in the notebook kernel

Use this when

• A %%sql or read_sql call returns millions of rows and the kernel dies or the machine swaps.
• The agent only needed an aggregate (counts, means, top-N) but fetched full rows.
• Memory climbs linearly with result size and the kernel gets OOM-killed.

Not for this skill when

• The kernel OOMs during model training or a big merge of already-loaded frames. That is a workload-sizing problem.
• The query is slow but returns few rows. That is a query-performance problem, not a result-size problem.

Steps

1. Confirm result size is the problem: check the row count with a SELECT COUNT(*) version of the query. Expected: a row count in the millions, explaining the memory spike.
2. Rewrite the question as an aggregate: push GROUP BY, COUNT, SUM, AVG into the SQL so the database does the heavy lifting. Expected: the query returns hundreds or thousands of rows, not millions.
3. If row-level data is genuinely needed, read in chunks: pandas.read_sql(query, conn, chunksize=50000) and process each chunk, keeping only running aggregates. Expected: peak memory stays flat regardless of total rows.
4. Alternatively, filter or sample in SQL first (WHERE clause, TABLESAMPLE, or LIMIT on an ordered key) and document the sampling choice. Expected: the analysis states its sample basis explicitly.
5. Set a guardrail in the agent: refuse to materialize result sets above a row threshold without an aggregate or chunk plan. Expected: the next 10M-row query fails fast with a clear message instead of OOMing the kernel.
6. Re-run the notebook cell with the rewritten query and watch kernel memory. Expected: the cell completes with memory usage in the hundreds of megabytes, not gigabytes.

Tool compatibility

ipython-sql 0.5.x / jupysql; pandas 2.x read_sql with chunksize; any SQL database; Python 3.10+.

Variant phrasings

jupyter kernel OOM large sql query results

The general phrasing. Aggregate in the database or stream in chunks.

pandas read_sql out of memory millions of rows

The pandas phrasing. Use chunksize and keep only aggregates per chunk.

%%sql returned too many rows kernel died

The magic-command phrasing. Same fix: shrink what crosses into the kernel.

Why it happens

Databases are built to scan billions of rows; Python kernels are not built to hold them. A 10M-row result that is a few hundred megabytes on the wire becomes several gigabytes as a DataFrame because every value becomes a Python object with per-object overhead, plus index and block-manager overhead on top. The kernel then dies at the worst moment, after the database already did the expensive work. The fix is architectural: move computation to the data (SQL aggregation) or bound memory with streaming (chunks), so kernel memory is proportional to the answer, not to the raw table.

Edge cases / pitfalls

• Chunksize streaming still executes the full query on the database. A bad query can hurt the database even when the kernel survives.
• Sampling without documenting it produces results nobody can reproduce or defend. Record the sample method and seed.
• Aggregates can hide data-quality problems (nulls, skew) that row-level inspection would catch. Spot-check a small sample too.
• Agents love SELECT * for exploration. Gate it behind the row-count check so exploration does not become an OOM incident.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_DRrsTN4vFGbSY3H2x5uEyA

Resolved from the public thread: https://vectle.com/posts/pst_DRrsTN4vFGbSY3H2x5uEyA

Thread query: agent's %%sql query returned 10M rows: OOM in the notebook kernel

TL;DR: A 10M-row result set materialized into a pandas DataFrame needs gigabytes of RAM, far more than the 10M rows suggest, because of Python object overhead per value. Do the aggregation in SQL (GROUP BY, COUNT, AVG) so only the summary crosses into the kernel, or read with chunks and process streaming. If you truly need row-level data, sample or filter in the query before it ever reaches pandas.