TopolearnKnowing the engine explains performance surprises and lets you process datasets larger than RAM reliably.
DuckDB executes queries with a vectorized, push-based engine. Operators process vectors of up to 2048 values of one column at a time, which keeps data in CPU caches and lets tight loops run without per-row interpretation overhead. Parallelism is morsel-driven: the input is split into chunks (for Parquet, roughly by row group) that worker threads grab dynamically, so all cores stay busy without fixed partitioning.
EXPLAIN shows the physical plan; EXPLAIN ANALYZE runs the query and annotates each operator with time and row counts. Look for where rows explode (a bad join), which scan dominates, and whether filters and projections were pushed into the scan.
Key settings:
SET threads = 8;defaults to the core countSET memory_limit = '4GB';defaults to 80% of RAMSET temp_directory = '/fast/disk';where spilled data goes
Hash joins, aggregations, sorts and window functions can spill to disk when they exceed the memory limit, so queries larger than RAM usually complete, just slower. preserve_insertion_order = false lets DuckDB skip ordering work and use less memory on big exports.
Performance tips: prefer Parquet over CSV, keep row groups large, avoid many tiny files, and remember that some operators, such as very large list() aggregates, cannot spill.
Example
import duckdb
con = duckdb.connect()
con.sql("SET threads = 4")
con.sql("SET memory_limit = '1GB'")
con.sql("CREATE TABLE t AS SELECT i, i % 100 AS g FROM range(2_000_000) r(i)")
plan = con.sql("""
EXPLAIN ANALYZE SELECT g, count(*) FROM t WHERE i % 2 = 0 GROUP BY g
""").fetchall()[0][1]
print("HASH_GROUP_BY" in plan, "Filters:" in plan) # True True
# the WHERE clause was pushed down into the table scan
print(con.sql("SELECT current_setting('threads')").fetchone()) # (4,)Remember
- Operators work on column vectors of up to 2048 values
- Morsel-driven parallelism spreads work over all threads
EXPLAIN ANALYZEshows per-operator time and row countsmemory_limitandtemp_directorycontrol spilling for larger-than-RAM queries
Watch out for
- Setting memory_limit close to total RAM on a machine that also runs other processes, causing OS out-of-memory kills.