Python

MemoryError on an unbounded DataFrame or query result

Written and reviewed by Sahil Srivastav

PythonpandasMemory
Traceback (most recent call last):
  File "etl/load.py", line 71, in load
    frame = pandas.read_sql_query(query, engine)
MemoryError: Unable to allocate 3.20 GiB for an array with shape (430000000,) and data type float64

What this error actually means

A DataFrame is an in-memory column store; `read_sql_query` normally materialises every selected row before returning. The database may stream bytes efficiently while the client allocates Python objects, NumPy buffers, index storage, and temporary copies. The allocation in the traceback is often only the final request that could not fit.

Peak memory is larger than the final frame. Type conversion, joins, sorting, concatenation, and pandas’ copy-on-write transitions can require a second array. A query returning 10 GB of narrow wire data can therefore need far more than 10 GB of process memory.

The safe design is bounded materialisation. Push filtering and aggregation to SQL, select only required columns, process fixed-size chunks, and write each result before reading the next. A larger container hides the cardinality bug and makes concurrent jobs fail together.

Causes, most common first

  1. 1SELECT * with no bounded range. The query materialises historical data indefinitely and its memory requirement grows with every day of operation.
  2. 2Temporary copies during pandas operations. A merge or conversion briefly holds input and output arrays together, multiplying peak usage.
  3. 3Object dtype and Python overhead. Strings and mixed columns are pointers to Python objects, so their memory cost is much higher than the wire representation.
  4. 4Concurrent jobs share one memory limit. Two individually acceptable frames exceed the cgroup or worker limit when scheduled together.

When you see it

  • RSS climbs with row count and does not fall between batches
  • The error occurs during concat, merge, sort, or dtype conversion rather than the query itself
  • The database query succeeds when run with LIMIT
  • Only the largest tenant or date range fails
  • The kernel reports an OOM kill instead of a Python exception

How to diagnose it

Step 1

Measure cardinality before loading

Use the database to estimate rows and bytes; do not discover the count after allocating the frame.

SELECT count(*) FROM (SELECT ... FROM events WHERE created_at >= :start) q;

Step 2

Watch process RSS and cgroup memory

A Python exception and a kernel kill have different owners. Record both the process and container limit while reproducing.

ps -o pid,rss,cmd -p $PID
cat /sys/fs/cgroup/memory.current /sys/fs/cgroup/memory.max

Step 3

Profile columns before transforming

Load a small representative sample and inspect `memory_usage(deep=True)`. Object strings and indexes commonly dominate.

python - <<'PY'
print(frame.memory_usage(index=True, deep=True).sort_values(ascending=False))
PY

The fix

Filter by time or key in SQL and select named columns. Add an index that supports the predicate so the database does not scan an ever-growing table.

Use `chunksize` and consume each chunk fully before requesting another. Write partitioned output or aggregate partial results; do not append every chunk to a list.

Prefer SQL aggregation and joins when the database can perform them within its own resource budget. If pandas is required, use categoricals, nullable numeric dtypes, and a deliberate index.

Bound concurrency with a job semaphore and reserve memory for temporary copies. A worker limit is part of the algorithm, not merely deployment tuning.

For truly large data, use an engine designed for out-of-core processing and define a partitioning plan.

sql = '''SELECT customer_id, sum(amount) AS total FROM payments WHERE paid_at >= %(start)s GROUP BY customer_id'''
for chunk in pandas.read_sql_query(sql, engine, params={'start': start}, chunksize=50_000):
    write_partition(chunk)
    del chunk

How to stop it coming back

  • Require bounded predicates for analytical endpoints
  • Alert on RSS and cgroup headroom before the kernel kills workers
  • Test with production-scale cardinality and concurrent jobs
  • Record per-column memory usage in pipeline benchmarks
  • Reject unbounded exports at the API boundary

Practise production debugging in a real repository

Reading about a failure and reproducing one are different skills. Gronex ships broken backend repositories with failing test suites that encode the real invariant, so you debug from evidence instead of memorising symptoms.

FAQ

Will increasing swap solve it?

Swap may prevent an immediate kill but turns the job into severe I/O thrashing. It does not remove the unbounded allocation or protect latency-sensitive processes.

Does chunksize guarantee constant memory?

No. Each chunk is bounded, but your code can retain chunks, and transforms can create temporary copies. Measure peak RSS and release intermediate objects.

Why is the DataFrame larger than the query result?

Pandas stores indexes, column arrays, and often Python objects for strings. Conversions and joins can briefly hold multiple representations.

Related

Java heap space from full result materialisationPython RecursionError

Other errors engineers hit next to this one

Full error and symptom index →