kodeabc.xyz logo
The Local Data Stack Is Fractured: Stop Misusing Pandas, Polars, and DuckDB

The Local Data Stack Is Fractured: Stop Misusing Pandas, Polars, and DuckDB

By Reggi, 25 May 2026

Most Out-Of-Memory (OOM) crashes on local workstations are completely self-inflicted. We routinely see engineers load gigabytes of uncompressed tabular data into an eager DataFrame runtime, execute three chained operations, materialize multiple redundant memory allocations, and wonder why the kernel killed their process. Choosing between Pandas, Polars, and DuckDB is not an aesthetic preference. It is a strict systems design decision governed by memory allocation models, query planning, and execution mechanics.

Pandas remains the bedrock for notebooks, exploratory data analysis (EDA), visualization, and machine learning pipelines. Polars brings a laser-focused, high-performance columnar engine engineered for raw throughput and low memory consumption. DuckDB flips the entire workflow toward an embedded, SQL-first analytical engine capable of querying local files directly.

Each engine dominates an explicit layer of the modern data stack. Understanding their low-level mechanics prevents pipeline failures before code ever hits production.

The Core Technical Scorecard

To evaluate these tools objectively, we must examine how they handle state, execution lifecycles, and underlying data structures:

Technical VectorPandasPolarsDuckDB
Primary FocusPython Data Science CompatibilityBlazing Fast DataFrame ProcessingSQL-First Local Analytics
ArchitectureDataFrame-centric, Eager, Step-by-stepColumnar Engine, Lazy Execution, OptimizedSQL-First, Embedded Local DB Engine
Memory & PerformanceEager, High Memory Overhead on Large DataMemory Efficient, Multi-core, FastEfficient for Heavy Analytical Workloads, Disk Spilling
Sweet SpotNotebooks, EDA, ML, VisualizationETL, Feature Engineering, Fast PipelinesJoins, Aggregations, Direct File Queries (Parquet)
PersistenceNone (Native)None (Native)Local DB File Persistence

Pandas captures the edge when tight integration with the broader PyData ecosystem is non-negotiable. Polars takes the lead when DataFrame transformation throughput and memory saturation are the critical bottlenecks. DuckDB rules when your query model relies on SQL patterns and requires robust local analytical execution.

Architectural Internals: How Memory and Compute Diverge

The mental models of these three engines dictate their compute efficiency and memory footprints.

PANDAS (Eager Execution)
[Load All Data] -> [Alloc Interim Object] -> [Filter Object] -> [Alloc Joined Object]

POLARS (Lazy Execution DAG)
[Scan Metadata] -> [Build Logical Plan] -> [Predicate/Projection Pushdown] -> [Execute Stream]

DUCKDB (Vectorized Engine)
[Direct File Scan (Parquet/CSV)] -> [Morsel-Driven Parallel Execution] -> [Disk Spill if RAM Exceeded]

Pandas: Imperative and Eager

Pandas organizes computation around the Python DataFrame object. It operates strictly eagerly: each line of code executes immediately, processing data step-by-step. While this provides instantaneous feedback inside interactive notebooks, it carries massive memory overhead. Intermediate steps materialize whole new objects in RAM, making complex pipelines on medium-to-large datasets prone to bloat and memory exhaustion.

Polars: Declarative and Lazy

Polars looks like a DataFrame library on the surface, but its core operates as a compiled columnar query engine. When running in lazy mode, Polars constructs a Directed Acyclic Graph (DAG) representing your entire transformation. It evaluates the plan, applies optimizations like predicate pushdown (filtering rows at the I/O read boundary) and projection pushdown (loading only the requested columns), and executes the graph across all available CPU cores using a work-stealing scheduler. Intermediate allocations are minimized automatically.

DuckDB: Embedded Vectorized SQL

DuckDB is an embedded analytical database built specifically for OLAP workloads. It avoids the DataFrame abstraction at the root layer and evaluates queries through a vectorized engine designed around morsel-driven parallelism. DuckDB queries files like Parquet and CSV directly using zero-copy mechanisms over Apache Arrow. More importantly, DuckDB includes a native disk-spilling subsystem: when a heavy join or aggregation exceeds available RAM, the engine degrades gracefully by swapping intermediate blocks to disk instead of crashing with an OOM error. It also allows saving state directly into a persistent .duckdb local database file.

Performance and Memory Mechanics Under Pressure

When datasets fit trivially in memory, execution speed differences rarely matter. The operational reality changes as inputs scale.

  • Pandas: Eager execution coupled with intermediate allocations drives significant memory bloat. Scans, complex hash joins, and heavy groupby calculations quickly become slow and CPU-inefficient.
  • Polars: The query planner inspects the entire operation graph before executing. By pruning unnecessary columns, reordering operations, pushing filters into the file reader, and driving multi-core parallelism, Polars delivers consistently low memory consumption and maximum execution speed.
  • DuckDB: Built like a full database engine, DuckDB processes data in vectorized chunks. Its direct file scanning bypasses loading overheads, while its disk-spilling capabilities allow execution on larger-than-memory datasets where standard DataFrame libraries fail.

Always profile your exact data types, file formats, and execution plans on target hardware.

Mapping Engine Strengths to Real-World Workloads

Selecting the correct tool requires matching runtime characteristics to your operational requirements.

             ┌──────────────────────────────────────────────┐
             │       Evaluate Workload Constraints          │
             └──────────────────────┬───────────────────────┘
                                    │
         ┌──────────────────────────┼──────────────────────────┐
         ▼                          ▼                          ▼
┌─────────────────┐       ┌────────────────────┐     ┌───────────────────┐
│ ML Ecosystem &  │       │ High-Throughput    │     │ SQL-First, OLAP,  │
│ Interactive EDA │       │ ETL Transformations│     │ Disk Spilling     │
└────────┬────────┘       └─────────┬──────────┘     └─────────┬─────────┘
         ▼                          ▼                          ▼
    Use PANDAS                 Use POLARS                 Use DUCKDB

Deploy Pandas When:

  • Your workflow lives inside Jupyter notebooks, rapid exploratory data analysis, and statistical evaluation.
  • You need native, zero-friction handoffs to tools like scikit-learn, statsmodels, matplotlib, or seaborn.
  • Downstream libraries strictly require a native pandas.DataFrame.

Deploy Polars When:

  • You run high-performance, single-node ETL pipelines, data preprocessing, or feature engineering tasks.
  • Execution throughput and strict memory ceilings are primary engineering concerns.
  • You want declarative query planning using an expression-based API (pl.col(...)) that compiles down to parallel native code.

Deploy DuckDB When:

  • Your data analysis logic is best expressed in expressive SQL (complex multi-table joins, aggregations, window functions).
  • You need to query massive raw Parquet, CSV, or JSON files directly without an upfront ingestion step.
  • Your transformations might exceed physical RAM limits, requiring automatic disk-spilling to guarantee job completion.
  • You need local database persistence rather than managing disconnected directories of Parquet files.

Interoperability: Constructing a Polyglot Pipeline

These engines are not mutually exclusive silos. Modern high-efficiency systems compose them dynamically using Apache Arrow as the shared foundation.

  • Pandas as the Lingua Franca: The broad Python data ecosystem defaults to pd.DataFrame as its universal ingestion type. It remains the ideal terminal format for the final mile of model fitting and data visualization.
  • Polars as the High-Throughput Engine: Polars is Arrow-native. It executes conversions to and from Pandas, NumPy, and PyArrow Tables with zero-copy efficiency using methods like to_pandas(), to_numpy(), and to_arrow(). It acts as high-speed middleware for compute-heavy transformations.
  • DuckDB as the Universal Query Layer: DuckDB accepts Pandas DataFrames, Polars DataFrames, PyArrow Tables, and raw disk files directly within standard SQL statements (FROM df, FROM 'file.parquet').

A robust hybrid architecture lets each engine do what it was built for:

[Raw Files: Parquet / CSV]
           │
           ▼
[DuckDB: Vectorized SQL Ingestion, Multi-Table Joins, Disk-Spill Layer]
           │
           ▼ (Zero-Copy Arrow Handoff)
[Polars: Lazy Columnar Feature Transforms & Complex Expressions]
           │
           ▼ (Zero-Copy Export via .to_pandas())
[Pandas: Visualization, Scikit-Learn Model Training, Reporting]

Comparative Pipeline Implementations

To observe how these paradigms handle the exact same data pipeline, let us build an end-to-end scenario across all three tools.

Generating the Test Fixtures

First, we generate deterministic data sources: orders.parquet and customers.csv.

python
import pandas as pd
import numpy as np

# Seed for reproducibility
np.random.seed(42)

orders_data = {
    'order_id': range(1, 11),
    'customer_id': np.random.randint(1, 5, 10),
    'order_date': pd.to_datetime([
        '2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02', 
        '2023-01-03', '2023-01-03', '2023-01-04', '2023-01-04', 
        '2023-01-05', '2023-01-05'
    ]),
    'status': ['completed', 'pending', 'completed', 'completed', 'pending', 
               'completed', 'completed', 'completed', 'completed', 'completed'],
    'revenue': np.random.randint(50, 200, 10)
}
orders_df = pd.DataFrame(orders_data)
orders_df.to_parquet('orders.parquet', index=False)

customers_data = {
    'customer_id': range(1, 5),
    'segment': ['retail', 'enterprise', 'retail', 'small_business']
}
customers_df = pd.DataFrame(customers_data)
customers_df.to_csv('customers.csv', index=False)

print("Fixtures created: orders.parquet, customers.csv")

1. The Pandas Pipeline: Imperative and Eager

The standard Pandas pattern reads entire files into memory, materializing every intermediate filtering and merge operation.

python
import pandas as pd

# 1. Load eagerly into RAM
orders_pd = pd.read_parquet('orders.parquet')
customers_pd = pd.read_csv('customers.csv')

# 2. Filter (creates intermediate copy)
completed_pd = orders_pd[orders_pd['status'] == 'completed']

# 3. Join (hash join in memory)
joined_pd = completed_pd.merge(customers_pd, on='customer_id', how='inner')

# 4. Transform & Aggregate
joined_pd['order_date'] = pd.to_datetime(joined_pd['order_date']).dt.date
daily_rev_pd = joined_pd.groupby(['order_date', 'segment'], as_index=False)['revenue'].sum()

# 5. Persist
daily_rev_pd.to_parquet('daily_revenue_pandas.parquet', index=False)
print("Pandas: Pipeline execution complete.")

2. The Polars Pipeline: Declarative and Lazy

Polars sets up lazy scans over disk files. No records load into memory until the terminal collect() call compiles the DAG, applies pushdowns, and computes the stream.

python
import polars as pl

# 1. Lazy Scans (I/O deferred)
orders_lf = pl.scan_parquet('orders.parquet')
customers_lf = pl.scan_csv('customers.csv')

# 2. Build the Query Plan (LazyFrame)
# Predicate pushdown & projection pushdown execute automatically
q = (
    orders_lf
    .filter(pl.col('status') == 'completed')
    .join(customers_lf, on='customer_id', how='inner')
    .with_columns(pl.col('order_date').cast(pl.Date))
    .group_by(['order_date', 'segment'])
    .agg(pl.col('revenue').sum())
    .sort(['order_date', 'segment'])
)

# 3. Execute & Sink
q.collect().write_parquet('daily_revenue_polars.parquet')
print("Polars: Pipeline execution complete.")

3. The DuckDB Pipeline: Vectorized Direct-File SQL

DuckDB executes the entire ingestion, join, grouping, and export sequence inside a single SQL query without intermediate runtime glue code.

python
import duckdb

# Connect to an in-memory instance (or a persistent .duckdb file)
con = duckdb.connect(database=':memory:')

# Scan, Filter, Join, Aggregate, and Create Table directly from disk files
con.execute("""
    CREATE TABLE daily_revenue_duckdb AS
    SELECT
        CAST(o.order_date AS DATE) AS order_date,
        c.segment,
        SUM(o.revenue) AS revenue
    FROM 'orders.parquet' AS o
    JOIN 'customers.csv' AS c
        ON o.customer_id = c.customer_id
    WHERE o.status = 'completed'
    GROUP BY 1, 2
    ORDER BY 1, 2;
""")

# Export result to Parquet
con.execute("COPY daily_revenue_duckdb TO 'daily_revenue_duckdb.parquet' (FORMAT PARQUET);")
con.close()
print("DuckDB: Pipeline execution complete.")

Verifying Result Parity

We read back each output artifact and assert structural equality across all three pipelines:

python
import pandas as pd

pandas_out = pd.read_parquet('daily_revenue_pandas.parquet').sort_values(['order_date', 'segment']).reset_index(drop=True)
polars_out = pd.read_parquet('daily_revenue_polars.parquet').sort_values(['order_date', 'segment']).reset_index(drop=True)
duckdb_out = pd.read_parquet('daily_revenue_duckdb.parquet').sort_values(['order_date', 'segment']).reset_index(drop=True)

print("Pandas:\n", pandas_out)
print("\nPolars:\n", polars_out)
print("\nDuckDB:\n", duckdb_out)

print("\nStructural Assertions:")
print("Pandas equals Polars:", pandas_out.equals(polars_out))
print("Pandas equals DuckDB:", pandas_out.equals(duckdb_out))

All three paradigms produce identical output data, but their execution profiles, memory utilization, and resource scaling properties are entirely distinct.

The Production Selection Matrix

Select your engine by auditing the specific bottleneck in your data flow:

If Your Bottleneck Is...Select EngineArchitectural Reason
Ecosystem CompatibilityPandasDirect drop-in support for sklearn, statsmodels, matplotlib, and seaborn.
DataFrame Execution SpeedPolarsLazy graph compilation, multi-core work-stealing, and Arrow columnar format.
SQL Paradigms / Complex JoinsDuckDBFull SQL compliance, window operations, and direct file queries on Parquet and CSV.
Memory Constraints (> RAM Data)DuckDBOut-of-core streaming and automatic disk-spilling engine design.
Complex Columnar LogicPolarsHighly expressive, composable pl.col(...) expression algebra.
Interactive Step-by-Step EDAPandasImperative, eager execution with immediate visual REPL inspection.

The Systems Engineering Perspective

Engineers often waste time trying to crown a single winner among these tools. That approach fundamentally misunderstands modern data systems.

Treat Pandas as your Universal Adapter for final-mile modeling and downstream interfaces. Treat Polars as your High-Performance Compute Engine for complex, multi-core transformations and feature engineering. Treat DuckDB as your Local Analytical Database for zero-copy file querying, relational joins, and larger-than-memory processing.

Compose them intentionally across your pipelines. That is how you build reliable, high-performance data architectures.

References

https://www.analyticsvidhya.com/blog/2026/05/pandas-vs-polars-vs-duckdb/

Popular Reads