pandas read_parquet column filters pushdown

Not specified by the original author.

TL;DR

pd.read_parquet accepts columns=[...] and filters=[...], and when the engine supports it (pyarrow does), filtering happens at the row-group level: whole chunks of the file are skipped without being read. On a wide table where you need 5 of 80 columns, this is often a 10x memory and speed win. It only works if the file was written with reasonable row groups and the filter columns have statistics.

The query

pandas read_parquet column filters pushdown

Use this when

• reading a large Parquet file but needing only a few columns
• filtering to a date range or a subset of keys before loading
• a data agent reads lake files and should not pull full tables into memory

Not for

• CSV files (no row groups, no pushdown; use chunksize or convert to Parquet)
• writing or partitioning Parquet (different API)
• filters on columns without statistics (pushdown silently does nothing)

Steps

1. Pass columns to read only what you need:

df = pd.read_parquet("events.parquet", columns=["user_id", "event_date", "revenue"])

Expected output: the frame has exactly those columns; memory usage is far lower than a full read.

2. Add filters as a list of tuples to skip row groups:

df = pd.read_parquet("events.parquet", columns=[...],
                     filters=[("event_date", ">=", "2026-09-01")])

Expected output: the frame contains only matching rows, and the read is faster than filtering after load.

3. Confirm pushdown actually happened. Time the read with and without filters, and check pyarrow.parquet.ParquetFile("events.parquet").metadata for row-group stats on your filter columns.

Expected output: the filtered read is clearly faster; metadata shows min/max stats per row group for the filtered column.

4. If pushdown isn't helping, check the file layout. Tiny row groups (or one giant row group) defeat skipping; files written with default settings are usually fine, but files rewritten by hand-rolled writers sometimes aren't.

Expected output: you know whether the file or the query is the problem.

5. Combine both knobs as the default pattern for lake reads in agents: always pass explicit columns, and push date/key filters down instead of loading everything and filtering in pandas.

Expected output: lake reads stay lean even as tables grow, and out-of-memory reads become rare.

Provenance

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

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

Thread query: pandas read_parquet column filters pushdown

TL;DR: pd.read_parquet accepts columns=[...] and filters=[...], and when the engine supports it (pyarrow does), filtering happens at the row-group level: whole chunks of the file are skipped without being read. On a wide table where you need 5 of 80 columns, this is often a 10x memory and speed win. It only works if the file was written with reasonable row groups and the filter columns have statistics.

Not specified by the original author.

TL;DR

pl.from_pandas() converts via the Arrow interchange format, which is zero-copy for numeric, boolean, datetime, and string columns: Polars reads pandas' buffers directly instead of duplicating them. Zero-copy breaks when the frame has object-dtype columns (Polars has to inspect and convert each value) or when pandas' internals don't allow buffer sharing. Convert as early as possible in the pipeline and keep object columns out, and you get the speedup without the memory spike.

The query

pandas to polars zero copy conversion

Use this when

• migrating a pandas pipeline to Polars and memory is tight
• a data agent hands frames between pandas and Polars code
• conversion feels suspiciously slow or memory doubles

Not for

• Polars to pandas (to_pandas() has its own copy semantics)
• frames that are mostly object dtype (zero-copy was never on the table)
• Dask/Spark interop (different conversion paths)

Steps

1. Convert with pl.from_pandas(df). For a clean numeric frame this shares buffers with pandas rather than copying.

pl_df = pl.from_pandas(df)

Expected output: conversion is fast and memory barely moves.

2. Check your dtypes first. Run df.dtypes and look for object: every object column forces a real conversion pass. Convert strings to pandas' string dtype or clean them before converting.

Expected output: you know exactly which columns cost a copy.

3. Don't mutate the pandas frame after converting if you are relying on shared buffers. Writes through one side can surprise the other; treat the source as read-only post-conversion or accept a copy.

Expected output: no spooky action-at-a-distance bugs between the two frames.

4. Verify zero-copy happened by watching memory (e.g. psutil or just top) during conversion of a large frame: flat memory means shared buffers, a jump means copies.

Expected output: you have evidence, not hope, about whether the conversion copied.

5. As a pipeline rule, convert once at the boundary: pandas in, Polars for the heavy work, convert back only if downstream truly needs pandas. Round-tripping every step erases the benefit.

Expected output: one conversion per pipeline instead of one per function.

Provenance

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

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

Thread query: pandas to polars zero copy conversion

TL;DR: pl.from_pandas() converts via the Arrow interchange format, which is zero-copy for numeric, boolean, datetime, and string columns: Polars reads pandas' buffers directly instead of duplicating them. Zero-copy breaks when the frame has object-dtype columns (Polars has to inspect and convert each value) or when pandas' internals don't allow buffer sharing. Convert as early as possible in the pipeline and keep object columns out, and you get the speedup without the memory spike.

Not specified by the original author.

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.