python duckdb vs pandas vs polars: How to Choose

When a Python project needs to process tabular data, pandas is often the default choice, but the python duckdb vs pandas vs polars decision is not a simple three-way race. The three libraries overlap in what they can compute, yet they differ in execution model, memory behavior, and API design. Choosing the wrong one for a workload can mean rewriting the same pipeline later.

What Each Tool Actually Is

pandas is an eager, in-memory DataFrame library built on NumPy arrays. It loads data into memory, applies operations immediately, and returns results as new DataFrames. Its API is the most widely documented and integrated with the rest of the Python data ecosystem.

polars is a DataFrame library backed by Apache Arrow. It supports both eager and lazy execution. In lazy mode, polars builds a query plan, optimizes it, and executes it with multi-threaded, vectorized operations. It can stream data through a pipeline instead of holding everything in memory.

DuckDB is not a DataFrame library. It is an embedded OLAP database engine that runs SQL queries directly on files, Arrow tables, and in-memory DataFrames from pandas or polars. Its columnar, vectorized execution engine is designed for analytical queries over large datasets, and it can spill intermediate results to disk when memory is exhausted.

This distinction matters because the comparison is not "which DataFrame API is better" but "which execution model fits the data size and query pattern."

Execution Model and Data Flow

pandas executes eagerly. Every operation runs immediately and produces a new DataFrame. This makes debugging straightforward, but it also means the entire dataset must fit in memory, and each intermediate result is materialized.

polars defaults to eager execution for simple operations, but lazy() defers work until collect(). The lazy planner can reorder filters, push projections down, and avoid materializing intermediate frames. This is similar to what a SQL optimizer does, but inside a DataFrame API.

DuckDB executes SQL through a vectorized engine. When you query a pandas or polars DataFrame with DuckDB, the engine reads the Arrow or NumPy data directly without copying it into a separate storage layer. The same engine can query Parquet or CSV files directly, which removes the need to load data into Python at all.

import pandas as pd
import polars as pl
import duckdb

# pandas: eager, in-memory
df = pd.read_csv("sales.csv")
monthly = df.groupby("month")["amount"].sum()

# polars: lazy, optimized plan
df = pl.read_csv("sales.csv")
monthly = (
    df.lazy()
    .group_by("month")
    .agg(pl.col("amount").sum())
    .collect()
)

# DuckDB: SQL over the file directly
conn = duckdb.connect()
monthly = conn.execute("""
    SELECT month, SUM(amount) AS total
    FROM 'sales.csv'
    GROUP BY month
""").df()

The pandas version materializes the full DataFrame before grouping. The polars version builds a plan and executes it with multi-threaded operations. The DuckDB version never loads the CSV into Python; the engine reads and aggregates it in one pass.

API and Syntax Compared

pandas uses method chaining with column labels and NumPy-style indexing. Grouping, filtering, and aggregation are expressed through methods like groupby(), merge(), and apply(). The API is familiar but carries some historical inconsistencies, such as the difference between df["col"] and df[["col"]].

polars uses an expression system. Operations are composed with pl.col(), pl.sum(), and similar expressions inside select(), filter(), and with_columns(). The same expression can be reused across different contexts, which reduces duplication.

DuckDB uses SQL. If the team already writes SQL, DuckDB requires no new API surface for query logic. The main Python integration point is duckdb.connect() and the execute() method, which returns results as a pandas DataFrame, a polars DataFrame, or an Arrow table.

# pandas: filter and aggregate
result = (
    df[df["region"] == "EU"]
    .groupby("product")
    .agg(total=("amount", "sum"))
    .reset_index()
)

# polars: same logic with expressions
result = (
    df.lazy()
    .filter(pl.col("region") == "EU")
    .group_by("product")
    .agg(pl.col("amount").sum().alias("total"))
    .collect()
)

# DuckDB: SQL with the same shape
result = conn.execute("""
    SELECT product, SUM(amount) AS total
    FROM df
    WHERE region = 'EU'
    GROUP BY product
""").pl()

The polars and DuckDB versions both push the filter before the aggregation. pandas applies the filter first too, but the cost of loading and materializing the full frame is already paid.

Memory Behavior Under Real Workloads

The most common reason to switch away from pandas is memory pressure. pandas stores data in NumPy arrays, which are efficient for numeric columns but can be wasteful for strings and mixed types. Every operation that creates a new DataFrame allocates additional memory, and intermediate results are not freed until the reference count drops.

polars mitigates this with Arrow-backed data and lazy execution. In streaming mode, collect(streaming=True) processes data in batches, so a dataset larger than RAM can be processed without loading it entirely. This is not a default behavior; it must be requested explicitly.

DuckDB handles large data differently. It is an out-of-core engine: when intermediate results exceed the configured memory limit, it spills to temporary files on disk. This makes DuckDB the most reliable choice for datasets that do not fit in memory, because the engine degrades gracefully instead of raising an allocation error.

# polars: streaming aggregation over a large file
result = (
    pl.scan_csv("huge_sales.csv")
    .group_by("month")
    .agg(pl.col("amount").sum())
    .collect(streaming=True)
)

# DuckDB: the engine decides when to spill
conn.execute("SET memory_limit = '4GB'")
result = conn.execute("""
    SELECT month, SUM(amount) AS total
    FROM 'huge_sales.csv'
    GROUP BY month
""").pl()

The polars streaming path is explicit and works well when the pipeline is a single scan-aggregate. DuckDB applies spill behavior automatically across the whole query plan, including joins and window functions, which polars streaming does not always support.

Performance Characteristics

None of the three libraries is universally faster. The performance difference comes from the execution model, not from raw implementation quality.

pandas is single-threaded for most operations. It relies on vectorized NumPy kernels, which are fast for element-wise work, but groupby and merge operations are not parallelized. For a single-threaded workload on a small DataFrame, pandas is often fast enough that the difference is irrelevant.

polars parallelizes across CPU cores and uses SIMD-friendly Arrow data. Lazy mode also reduces the number of passes over the data by combining filters and projections. The speedup over pandas is most visible on multi-core machines and on operations like groupby and join that benefit from parallelism.

DuckDB uses a vectorized, columnar execution engine with a query optimizer. It parallelizes within a single query and can push filters and projections down to Parquet files, reading only the columns and row groups that are needed. For analytical queries over Parquet, this often beats loading the file into pandas first.

The practical rule is: if the data fits comfortably in memory and the pipeline is simple, pandas is usually sufficient. If the pipeline is complex or the data is large, polars lazy mode or DuckDB will reduce both memory usage and wall-clock time, but the exact gain depends on the hardware, the data shape, and the query.

Choosing Between Them

Use pandas when the project already depends on its ecosystem. Libraries like scikit-learn, matplotlib, and many domain-specific packages expect pandas DataFrames. If the data is small enough to fit in memory and the pipeline is straightforward, migrating to polars or DuckDB adds a dependency without a measurable benefit.

Use polars when you want a DataFrame API with lazy optimization and multi-threaded execution, and when the data is too large for pandas but still fits in memory or can be processed in streaming mode. Polars is also a good choice when the team prefers an expression API over SQL.

Use DuckDB when the workload is analytical SQL, when the data lives in Parquet or CSV files, or when the dataset exceeds available memory. DuckDB is also useful as a query layer over existing pandas or polars DataFrames, because it can run complex joins and aggregations without copying the data.

The decision is not permanent. A common pattern is to keep pandas as the interface for downstream libraries while using DuckDB or polars for the heavy computation.

Combining DuckDB with pandas and polars

DuckDB can register a pandas or polars DataFrame as a virtual table and query it with SQL. This lets you keep the DataFrame API for data preparation and use SQL for the analytical heavy lifting.

import pandas as pd
import duckdb

df = pd.read_parquet("events.parquet")

conn = duckdb.connect()
conn.register("events", df)

result = conn.execute("""
    SELECT user_id, COUNT(*) AS events
    FROM events
    WHERE status = 'completed'
    GROUP BY user_id
    HAVING COUNT(*) > 5
""").df()

The same pattern works with polars DataFrames, and DuckDB can return results as either type. This is the most pragmatic answer to the comparison: instead of replacing one tool with another, use each where its execution model fits. pandas remains the interchange format for the ecosystem, polars handles DataFrame-style processing with parallelism, and DuckDB handles SQL and out-of-core workloads.