DuckDB vs Pandas: Why Your Analytics Script Runs 10x Faster After One Switch

DuckDB vs Pandas: Why Your Analytics Script Runs 10x Faster After One Switch

If you've ever waited minutes for a pandas .groupby() on a 2M row CSV, you know the frustration.

The good news: there's a drop-in alternative that uses SQL syntax, runs in-process with zero setup, and regularly outperforms pandas by 5x to 50x on analytical queries — DuckDB.

This isn't theoretical. Let's run real benchmarks.

---

🧪 The Benchmark Setup

Both libraries tested on the same machine with the same 5M row dataset (simulated e-commerce orders, ~1.2 GB CSV):

• MacBook M2 Pro, 16GB RAM
• Python 3.11
• pandas 2.2, DuckDB 0.10

import pandas as pd
import duckdb
import time

FILE = "orders_5m.csv"

# pandas approach
start = time.time()
df = pd.read_csv(FILE)
result = df.groupby("category")["revenue"].sum().sort_values(ascending=False)
print(f"pandas: {time.time() - start:.2f}s")

# DuckDB approach
start = time.time()
con = duckdb.connect()
result = con.execute(f"""
  SELECT category, SUM(revenue) as total
  FROM '{FILE}'
  GROUP BY category
  ORDER BY total DESC
""").df()
print(f"DuckDB: {time.time() - start:.2f}s")

---

📊 Results

Operation	pandas	DuckDB	Speedup
Read 5M row CSV	18.4s	1.2s	15x
GROUP BY + SUM	4.1s	0.3s	14x
JOIN two 2M row tables	22.7s	1.8s	13x
Filter + Aggregate	6.3s	0.4s	16x
Read Parquet (columnar)	3.2s	0.09s	36x

DuckDB won every single test, with the largest gap on columnar Parquet reads — which is exactly how modern data lakes store data.

💡 Note on Threading & PyArrow:
The timings above measure out-of-the-box defaults: DuckDB automatically parallelizes across all CPU cores (8 worker threads on M2 Pro), whereas default pd.read_csv() runs on a single-threaded C parser. Specifying engine="pyarrow" in pandas enables multi-threaded ingestion and noticeably narrows the CSV read gap, though DuckDB retains a significant advantage during complex joins and in-memory aggregations.

---

🔍 Why Is DuckDB So Much Faster?

1. Columnar Storage Engine

pandas stores data row-by-row in memory. When you do a SUM(revenue), it still reads every column of every row even though only one column matters.

DuckDB uses a columnar vectorized engine — it reads only the columns your query touches, in parallel 64-element chunks using SIMD CPU instructions.

2. Query Compilation + Parallelism

DuckDB compiles each query to an optimized execution plan and automatically parallelizes across all CPU cores. pandas is single-threaded by default.

3. Out-of-Core Query Execution

DuckDB can query files larger than RAM by streaming chunks. pandas read_csv() loads everything into memory first — if your CSV is 32GB, you need 32GB+ RAM.

# This works in DuckDB even if the file is 50GB and you have 8GB RAM
result = duckdb.sql("SELECT * FROM 'huge_file.csv' WHERE amount > 1000 LIMIT 100").df()

# pandas would crash or swap to disk with OOM errors

---

🛠️ Practical Migration: pandas → DuckDB

Before (pandas):

df = pd.read_csv("sales.csv")
monthly = df[df["year"] == 2024].groupby("month")["amount"].sum()

After (DuckDB):

import duckdb
monthly = duckdb.sql("""
  SELECT month, SUM(amount) as total
  FROM 'sales.csv'
  WHERE year = 2024
  GROUP BY month
""").df()

The result is a regular pandas DataFrame — you can still use all your downstream pandas/matplotlib code unchanged.

---

🔒 The Privacy Angle: Why DuckDB Fits Local Analytics

When your analytics process customer data, you don't want it leaving your environment. DuckDB is:

• Zero external calls — pure in-process computation
• No server setup — runs inside your Python or Node.js process
• WASM version — runs directly in the browser with no backend at all

This is exactly the architecture that tools like VeilAnalytics use to power in-browser SQL analytics on sensitive files — your data never leaves the browser tab.

---

🚀 When Should You Stick With pandas?

DuckDB is not always the answer:

Use Case	Better Tool
Complex row-by-row transformations	pandas
ML preprocessing pipelines (sklearn)	pandas
Analytics on 10M+ rows / large joins	DuckDB
Reading Parquet/CSV files	DuckDB
SQL-familiar team doing data analysis	DuckDB
Running in the browser (WASM)	DuckDB

---

🔧 Getting Started

pip install duckdb

That's it. No Docker, no server, no config. Import and query:

import duckdb

# Query any CSV, Parquet, or JSON file directly
duckdb.sql("SELECT * FROM 'data.csv' LIMIT 5").show()

---

Final Verdict

If your analytics code runs on files larger than a few hundred thousand rows, switching the aggregation layer from pandas to DuckDB is the single highest-ROI change you can make — typically a 10–20x speedup for less than 30 minutes of work.

The API is SQL, the output is a DataFrame, and the setup is one pip install.

Try it on your slowest script today.

---

VeilAnalytics — In-browser SQL analytics with zero data uploads. Built on DuckDB-WASM.

Comments

[Raknaos]

The Parquet line is the one that generalises: 36x on a columnar read isn't about the engine being cleverer, it's that the format lets the reader skip the columns the query never touches, which is a property pandas cannot exploit at all once everything is a row-grouped DataFrame in memory. The number that surprises people is the CSV read, because that gap mostly disappears when you point pandas at the same Parquet file.

The trap the benchmark hides is feeding row-oriented work through the columnar path anyway — push a per-row Python transform into con.execute(...).df() and you pay materialisation twice, and the speedup evaporates. The rule I settled on is to decide per step: set operations in SQL, row logic in a vectorised library.

Did you check whether DuckDB is using the same default thread count, and did the CSV timings include parsing types or just reading as strings? A naive SELECT * on a 1.2 GB CSV can look faster than pandas purely because it never parsed the columns, and the gap closes the moment you select the aggregate instead of the table.

[VeilAnalytics]

Good questions on the test details.

On thread count: they weren't matched. DuckDB was using its default thread pool (all 8 cores on the M2), while pandas was using the default single-threaded C parser. So you're right that a meaningful part of the raw CSV read gap is simply parallelism. Using engine="pyarrow" in pandas brings multi-threading into the picture and closes a lot of that gap.

On type parsing: it wasn't just reading raw string buffers. The DuckDB query used read_csv_auto, which sniffed and cast the types; in particular, revenue needed to be numeric for the SUM anyway.

The other distinction is column projection. For queries that only need a subset of columns, DuckDB can avoid parsing columns that aren't needed, whereas pd.read_csv() materializes the whole CSV into memory.

Appreciate you 😇 calling out the thread defaults — I've added a note to the post to make that out-of-the-box threading difference explicit.