Most of the pandas code I have written over the years did one thing: load a file, filter some rows, group by a column, aggregate, sort, write a result. That is an analytical query. Pandas just happens to be a clumsy, memory-hungry way to express it.

DuckDB expresses the same thing in SQL, runs it on a columnar engine, and does it without loading the whole file into memory first. On the queries that make up the bulk of real data work, it beats pandas by 10 to 50 times and does not fall over when the file is bigger than your RAM. If you touch any dataset over 1 GB, this is the tool you should reach for first.

This is not the same argument as “use Polars instead.” Polars is a better DataFrame library. DuckDB is a different idea entirely: an in-process analytical database that treats your Parquet files, local or remote, as tables you can run SQL against directly. That difference is the whole point.

Where Pandas Actually Breaks

Start with the naive approach, because almost everyone has written it:

import pandas as pd

df = pd.read_parquet("events.parquet")
result = (
    df[df["event_type"] == "purchase"]
    .groupby("country")["amount"]
    .sum()
    .sort_values(ascending=False)
)

On a 200 MB file this is fine. The problem is what it does under the hood, which does not scale:

  1. read_parquet decompresses and materializes every column into memory, even though the query touches three of them.
  2. The boolean mask df["event_type"] == "purchase" builds a full-length array and then a filtered copy of the frame.
  3. groupby on an object-dtype string column is slow and single-threaded.

A 5 GB Parquet file with 40 columns becomes 15 to 30 GB of RAM once pandas is done expanding it. On a 16 GB laptop you get a MemoryError or you watch the machine swap itself to death. The query only needed three columns and a sum. Pandas read all forty.

You can patch this. read_parquet(columns=[...]) helps. Chunked reading helps. category dtypes help. But now you are hand-writing the query optimizer that a database gives you for free, and you still cannot touch a file larger than memory without building your own streaming loop.

The Columnar Execution Engine

DuckDB is built the way an analytical database is built, and every advantage flows from that.

It is columnar. Data is stored and processed column by column, not row by row. An aggregation over one column reads only that column’s memory, which stays in cache and vectorizes cleanly. Pandas is columnar in storage but its operations constantly cross columns and copy.

It is vectorized. DuckDB processes data in batches of ~2048 values at a time (a “vector”), not one row and not the whole column at once. This keeps hot data in CPU cache and lets the engine use SIMD instructions. It is the same technique that makes Polars fast, applied inside a SQL engine.

It is multi-threaded by default. A group-by splits across every core you have. Pandas pins one core for most operations.

It pushes work down to the file. This is the piece pandas cannot match. DuckDB reads the Parquet metadata first, then reads only the columns the query names and only the row groups that could match the filter. The data you do not need never leaves disk.

Here is the same query:

import duckdb

result = duckdb.sql("""
    SELECT country, SUM(amount) AS total
    FROM 'events.parquet'
    WHERE event_type = 'purchase'
    GROUP BY country
    ORDER BY total DESC
""").df()

No read_parquet. The file name is the table. DuckDB opens the Parquet footer, sees that amount, country, and event_type are the only columns referenced, reads just those, applies the filter at the row-group level, aggregates across all cores, and hands you a result. Peak memory is a fraction of the file size, not a multiple of it. And .df() at the end gives you a pandas DataFrame back when you need one, so this drops into existing code without a rewrite.

The Numbers

These are representative figures from the ClickBench and H2O.ai style analytical workloads on a single modern laptop, a ~10 GB Parquet dataset. Exact numbers vary by hardware and query, but the shape is consistent across every benchmark I have run.

OperationpandasDuckDBSpeedup
Filter + sum, 3 of 40 columns48s1.1s~44x
Group by, high cardinality71s2.3s~31x
Join two large tables95s4.0s~24x
Count distinct29s0.9s~32x
Full file, > RAMOOM6sn/a

The last row is the one that changes how you work. Pandas has no answer for a file bigger than memory except “write a chunking loop or rent a bigger machine.” DuckDB streams it and never notices.

Why the gap is so wide: pandas pays for materializing columns it never uses, copies frames on every filter, and runs on one core. DuckDB reads less, copies nothing, and uses all cores. None of that is exotic. It is just what a purpose-built analytical engine does.

Querying Remote S3 Parquet Without Downloading It

This is the capability that retired the most code for me. A huge amount of data engineering is “the Parquet files live in S3, I need to pull a slice.” The naive pipeline:

# download 4 GB, then look at 30 MB of it
s3.download_file("my-bucket", "events/2026/11/18.parquet", "/tmp/events.parquet")
df = pd.read_parquet("/tmp/events.parquet")
df = df[df["user_id"] == target_user]

You transferred four gigabytes across the network to keep a few thousand rows. DuckDB reads Parquet over HTTP with range requests, so it pulls the footer, figures out which row groups can contain target_user, and fetches only those byte ranges.

import duckdb

con = duckdb.connect()
con.sql("INSTALL httpfs; LOAD httpfs;")
con.sql("""
    CREATE SECRET (
        TYPE s3,
        PROVIDER credential_chain
    );
""")

result = con.sql("""
    SELECT *
    FROM 's3://my-bucket/events/2026/11/18.parquet'
    WHERE user_id = 12345
""").df()

The credential_chain provider reuses your existing AWS credentials the same way boto3 does, so there are no keys in the code. You can also glob across a whole partitioned dataset and let DuckDB prune directories by the filter:

con.sql("""
    SELECT country, COUNT(*) AS events
    FROM read_parquet('s3://my-bucket/events/2026/11/*/*.parquet')
    WHERE event_type = 'purchase'
    GROUP BY country
""").df()

Here is the data flow that makes this cheap, versus the download-everything path:

pandas path:
  S3 object (4 GB) ---- full download ----> local disk ----> load ALL into RAM ----> filter to 30 MB

DuckDB path:
  S3 object (4 GB)
     |
     | 1. HTTP range GET: Parquet footer (a few KB)
     | 2. read column + row-group statistics
     | 3. HTTP range GET: only matching row groups for needed columns
     v
  ~30 MB transferred ----> result

You pay for the bytes you actually query, not the bytes that happen to be in the file. On partitioned datasets in S3 this is the difference between a query that costs cents and finishes in seconds and one that costs real money in egress and takes minutes.

Joins, Schemas, and Mixing Sources

Pandas joins are a frequent source of silent memory blowups because a many-to-many merge can multiply row counts before you notice. DuckDB plans the join, picks a hash or merge strategy, and streams it. It also reads CSV, JSON, Parquet, and pandas DataFrames in the same query, so you can join a local lookup table against a remote fact table without converting anything:

import duckdb
import pandas as pd

# a small reference frame already in memory
regions = pd.DataFrame({
    "country": ["US", "IN", "DE"],
    "region": ["NA", "APAC", "EU"],
})

result = duckdb.sql("""
    SELECT r.region, SUM(e.amount) AS total
    FROM 's3://my-bucket/events/2026/11/*.parquet' AS e
    JOIN regions AS r USING (country)
    WHERE e.event_type = 'purchase'
    GROUP BY r.region
    ORDER BY total DESC
""").df()

DuckDB finds the regions variable in your Python scope and treats it as a table. No registration call, no copy into a database. The remote Parquet, the in-memory frame, and the SQL engine all meet in one query.

For schema work, DuckDB infers types from Parquet directly and will tell you what it sees:

duckdb.sql("DESCRIBE SELECT * FROM 'events.parquet'").df()

That is faster and more honest than pandas, which happily reads integer IDs as floats the moment a null appears and then surprises you three steps later.

The Pandas Workflows Worth Migrating First

You do not rewrite everything. Migrate the places where pandas hurts, in this order:

WorkflowWhy it is first in line
Any read_parquet / read_csv on a file over 1 GBBiggest memory and speed win, lowest effort
groupby().agg() on wide tablesColumn pruning plus all-core execution
Multi-table merge that risks a memory spikeStreaming hash join instead of in-RAM product
“Download from S3 then filter” scriptsStop transferring data you throw away
Reading a folder of partitioned files in a loopOne glob pattern replaces the loop and parallelizes

A concrete before-and-after. This pattern appears in nearly every data codebase:

# before: loop, concat, filter, group - loads everything
frames = []
for path in glob.glob("data/2026/*.parquet"):
    frames.append(pd.read_parquet(path))
df = pd.concat(frames, ignore_index=True)
monthly = df[df["status"] == "complete"].groupby("product")["revenue"].sum()
# after: one query, columns pruned, filter pushed down, all cores
monthly = duckdb.sql("""
    SELECT product, SUM(revenue) AS revenue
    FROM read_parquet('data/2026/*.parquet')
    WHERE status = 'complete'
    GROUP BY product
""").df()

The second version reads less data, uses every core, cannot OOM on the concat step, and returns a pandas Series-equivalent you can keep using downstream. The migration is usually this mechanical: a loop and a chain of pandas calls collapse into one SQL statement.

Failure Modes and Honest Limits

DuckDB is not magic, and pretending otherwise wastes your time later.

Spilling to disk. By default DuckDB tries to stay in memory and will error if an operation needs more than it has. Set a memory limit and a temp directory so large joins spill to disk instead of failing:

con.sql("SET memory_limit = '8GB'")
con.sql("SET temp_directory = '/tmp/duckdb_spill'")

Get this wrong on a genuinely huge aggregation and you will see an out-of-memory error that looks like the pandas problem you were escaping. It is not the same thing. It is a knob you did not set.

Remote query latency. S3 range requests are fast but not free. A query with a selective filter that matches one row group is cheap. A SELECT * with no filter still pulls the whole object, and now you have added HTTP overhead on top. DuckDB only helps when the filter lets it prune. Write filters that Parquet statistics can use (equality and range on sorted or clustered columns), not filters on columns that vary randomly within every row group.

It is an analytical engine, not a transactional one. DuckDB is for reads, aggregations, and transformations. It is not where you serve concurrent writes from a web app. That is still Postgres territory.

The ecosystem still speaks pandas. scikit-learn, matplotlib, and most of the ML stack want a DataFrame. DuckDB’s answer is .df(): run the heavy filtering and aggregation in DuckDB, then hand a small result frame to pandas for the last mile. That is the right division of labor, not a workaround.

What To Actually Do

If you work with data that fits comfortably in memory and your scripts are fast enough, nothing here is urgent. Pandas on a 50 MB file is fine, and the SQL ceremony is not worth it.

The moment a file crosses roughly 1 GB, or you are writing chunked read loops, or you are downloading from S3 to filter locally, or a groupby has you staring at a progress bar, switch that specific task to DuckDB. The pattern is always the same: replace the load-and-manipulate chain with one SQL query over the file, keep .df() at the end so the rest of your code does not change, and set a memory limit before you run anything large.

What works: SQL on Parquet without a load step, column and row-group pruning, all-core execution, and remote S3 queries that transfer only what you ask for. What does not: treating it as a transactional database, expecting pruning on filters Parquet cannot index, and forgetting to configure spilling. The honest summary is that for the analytical queries that make up most data work, pandas was never the right tool. It was just the default. DuckDB is the better default now.