Performance Optimization & Best Practices

DuckDB is fast by default, but understanding why helps you diagnose the rare query that isn’t, and configure it correctly for the machine it’s running on.

Why DuckDB Is Fast

• Vectorized execution processes ~2,000-row batches per operator call instead of one row at a time, amortizing per-row overhead and staying cache-friendly.
• Columnar storage means a query touching 3 of a table’s 50 columns only reads those 3 columns from disk.
• Automatic parallelism — DuckDB uses all available CPU cores for a single query without any configuration.
• Out-of-core execution — joins, sorts, and aggregations that don’t fit in memory spill to disk automatically instead of crashing, so a dataset larger than RAM still completes (slower, but correctly).

Memory & Thread Configuration

SET memory_limit = '8GB';
SET threads = 4;
PRAGMA database_size;   -- current on-disk size of the open database

By default DuckDB uses ~80% of system RAM and all available cores — usually the right call, but worth capping explicitly in shared/containerized environments (CI runners, Docker with a memory cgroup limit) to avoid the OOM killer stepping in.

Reading Query Plans

EXPLAIN SELECT customer, SUM(amount) FROM orders GROUP BY customer;

EXPLAIN ANALYZE
SELECT customer, SUM(amount) FROM orders GROUP BY customer;

EXPLAIN ANALYZE runs the query and annotates each operator with actual rows processed and time spent — the fastest way to confirm whether a filter is actually being pushed down into a Parquet scan (look for Filters: on the PARQUET_SCAN node) rather than applied after reading everything.

Practical Tips for Large Data

• Prefer Parquet over CSV for anything you’ll query more than once — Parquet’s columnar layout and embedded statistics let DuckDB skip data CSV forces it to read in full.
• Partition large file sets (e.g. sales/year=2026/month=06/*.parquet) so filters on the partition column skip whole files, not just rows.
• Avoid SELECT * on wide tables when you only need a few columns — columnar storage only pays off if you actually project fewer columns.
• Persist to a .duckdb file instead of re-reading raw CSV/Parquet on every run of a repeated analysis — DuckDB’s native storage format is compressed and indexed for its own engine.
• Use SAMPLE while iterating on a query against a huge table, then remove it for the final run.

Concurrency Notes

A single DuckDB file supports one write connection at a time but multiple concurrent read-only connections (duckdb.connect('file.duckdb', read_only=True)). For genuinely concurrent multi-writer workloads, either coordinate writes at the application layer or reach for MotherDuck / a traditional client-server warehouse.