Is Pandas Still Viable in the Era of Massive Data?

Scaling Pandas in modern data pipelines

Data volumes have grown from dozens‑of‑thousands‑row CSV files to multi‑gigabyte, multi‑table pipelines containing logs, transaction records, model outputs and third‑party data. A Pandas script that once ran in seconds now crashes or requires manual memory tricks when file sizes reach several gigabytes.

Why Pandas alone struggles

Pandas is optimized for interactive, in‑memory analysis. Its core operations create intermediate copies, and functions such as merge() or groupby() on multi‑gigabyte data can push peak memory beyond physical RAM, causing the Python kernel to die. Chunked reads help for independent tasks, but any operation that needs to coordinate across chunks (global sort, large join, high‑cardinality aggregation) quickly becomes infeasible.

DuckDB and Polars as complementary engines

DuckDB is a file‑proximate SQL engine. It can scan CSV, Parquet and Arrow files directly, push column‑level filters and predicates to the storage layer, and perform joins, aggregations and window functions without loading the full dataset into Python. Large intermediate results can be spilled to disk, giving predictable memory usage.

Polars provides a columnar expression API with a lazy optimizer. The optimizer performs projection push‑down, predicate push‑down, common‑subexpression elimination and, for operators that support it, streaming execution. Polars excels at complex column transformations, window calculations and feature engineering.

The recommended workflow is:

Use DuckDB for early‑stage heavy lifting (file scanning, filtering, joins, aggregation).

Pass the reduced result to Polars for column‑wise transformations.

Convert the final small result to Pandas for notebook‑style exploration, visualization and downstream consumption.

Benchmark insights (140 GB Parquet)

DuckDB peak memory ≈ 1.3 GB; runtime only ~1 s faster than Polars.

Polars default mode peak memory ≈ 17 GB; setting POLARS_FORCE_ASYNC=1 drops peak to ≈ 750 MB but adds a few seconds to runtime.

Splitting a single 140 GB file into 72 × 2 GB files reduces DuckDB memory to ≈ 160 MB and Polars memory to ≈ 4.3 GB, showing that file layout dramatically changes engine performance.

These numbers demonstrate that raw memory usage does not tell the whole story; workload characteristics and file organization are equally important.

Practical migration steps

Identify the most memory‑intensive stages (large CSV reads, massive merge(), full‑table scans, global sorts, high‑cardinality joins).

Replace those stages with DuckDB SQL that filters, joins and aggregates directly on the files.

Introduce Polars only when the middle layer requires extensive column expressions, window functions or parallel columnar computation.

Convert to Pandas only for the final, small result set that feeds notebooks or downstream tools.

When to prefer each tool

Quick exploration of < 1 M rows – Pandas (low mental overhead, rich ecosystem).

Scanning large CSV/Parquet with column/row pruning – DuckDB (file‑native scanning, push‑down filters).

Multi‑table SQL joins that may exceed memory – DuckDB (robust optimizer, spilling support).

Heavy columnar transformations, window operations – Polars (expression API, lazy optimization, multithreading).

Data > memory but operators support streaming – DuckDB or Polars streaming (depends on operator capabilities).

Existing Pandas code with isolated bottlenecks – DuckDB + Pandas (replace only the slow segment).

Final results for plotting or legacy code – Pandas (most compatible output format).

Arrow as the zero‑copy bridge

DuckDB, Polars and Pandas share Apache Arrow as the columnar in‑memory format. DuckDB can read Polars DataFrames and output Arrow‑backed results; Polars can produce Arrow batches that Pandas can consume with .to_pandas(use_pyarrow_extension_array=True). Without the Arrow extension array, .to_pandas() copies data, potentially doubling peak memory.

Intermediate‑result blow‑up and join safety

Out‑of‑memory errors often occur not at read_parquet() but when a join expands the row count (e.g., a 1 × 10⁸ order table joined to a 5 × 10⁶ customer table can produce 3‑5 × 10⁸ rows if the join key is not unique). Engines must maintain hash tables, partitions and buffers before materializing the final result.

Before blaming a library, ask four questions:

Is the join key unique on both sides?

Are filters and column pruning applied before the join?

What is the estimated row count of the intermediate result?

Can the operator stream or spill to disk?

Answering these questions often points to a single bottleneck line that can be replaced.

File layout matters

In the 140 GB benchmark, a single Parquet file caused DuckDB to use ~1.3 GB and Polars ~17 GB. Splitting the file into 72 × 2 GB parts reduced DuckDB to ~160 MB and Polars to ~4.3 GB. Thus, row‑group size, compression and partitioning affect memory usage as much as the engine itself.

Concrete collaborative pipeline example

import duckdb
import polars as pl

# Step 1 – DuckDB scans files, filters, joins, aggregates
monthly = duckdb.sql("""
    SELECT
        date_trunc('month', o.order_date) AS month,
        c.region,
        SUM(oi.quantity * oi.unit_price) AS revenue,
        COUNT(DISTINCT o.order_id) AS order_count
    FROM read_parquet('data/orders/*.parquet') AS o
    JOIN read_parquet('data/customers/*.parquet') AS c ON o.customer_id = c.customer_id
    JOIN read_parquet('data/order_items/*.parquet') AS oi ON o.order_id = oi.order_id
    WHERE o.order_date >= DATE '2025-01-01'
    GROUP BY 1, 2
""").pl()

# Step 2 – Polars performs column‑wise calculations
result = (
    monthly
    .sort(["region", "month"])
    .with_columns(
        avg_order_value = pl.col("revenue") / pl.col("order_count"),
        revenue_growth = pl.col("revenue").pct_change().over("region")
    )
)

# Step 3 – Convert to Pandas only for final plotting
plot_df = result.to_pandas(use_pyarrow_extension_array=True)

The crucial point is that the raw order‑line tables never enter Pandas; DuckDB reduces them to a month × region summary, Polars adds derived columns, and Pandas receives a tiny DataFrame suitable for visualization.

Key takeaways

Arrow enables near‑zero‑copy data exchange, but conversions that omit Arrow‑backed extension arrays still copy memory.

Before blaming a DataFrame library, examine file size, row‑group design, join cardinality and intermediate result growth.

Replacing a handful of critical lines (often < 20) can yield far larger performance gains than rewriting an entire Pandas codebase.