DuckDB Performance Tuning That Actually Works

Been using DuckDB with big datasets for a while now. Most optimization guides focus on stuff that doesn't actually help. There are 3 settings that actually fix slow queries.

Memory Limit: Just Give It More RAM

DuckDB's being conservative with your RAM. It only grabs 80% by default, which is stupid when you have 64GB sitting there doing nothing:

SET memory_limit = '90%';

This lets DuckDB use more memory before spilling to disk. Makes queries way faster in most cases.

Watch out: Don't set it higher than 95%. I crashed a server once setting it to 98% - everything locked up when the OS needed memory for something else. Spent an hour figuring out what happened.

Thread Count: Your CPU Probably Sucks at Hyperthreading

DuckDB detects your CPU cores automatically but hyperthreading usually hurts performance. Your CPU probably has hyperthreading and DuckDB will use all logical cores by default:

SET threads = 32;  -- Physical cores only

But if you're reading from S3 or remote files, use more threads since you're waiting on network anyway.

Temp Directory: Put It on Your Fastest Drive

When DuckDB runs out of memory, it spills to disk. Put the temp files on your fastest drive:

SET temp_directory = '/fast-storage/duckdb-temp';

Spinning drives make spills really slow. SSD helps. NVMe is best but still slower than keeping everything in memory.

Here's What Nobody Tells You About Memory Monitoring

Use these commands to see what's actually happening:

FROM duckdb_memory();  -- Shows memory breakdown
FROM duckdb_temporary_files();  -- Lists temp files being created

If you see temp files being created, either give DuckDB more memory or fix your query. Usually the query is doing something inefficient.

Why preserve_insertion_order = false Sometimes Helps

This one's weird. DuckDB tries to keep your data in the same order, but that uses extra memory:

SET preserve_insertion_order = false;

Use this for ETL jobs where order doesn't matter. Saves memory on big imports.

The basic memory and thread settings fix most performance issues. If you're still having problems with complex queries or huge datasets, here's what else helps.

Using EXPLAIN ANALYZE to Find What's Actually Slow

Stop guessing why your query is slow. Use EXPLAIN ANALYZE to see what's actually happening:

EXPLAIN ANALYZE SELECT region, COUNT(*), AVG(sales)
FROM huge_sales_table 
WHERE date_col >= '2024-01-01'
GROUP BY region;

Look for:

• Cardinality way off: If it estimates 1000 rows but gets 10M, the query planner has no clue what it's doing
• Nested loop joins: Usually means your join conditions suck
• No filter pushdown: Filters should happen early, not at the end

DuckDB 1.1 Features That Actually Help

Automatic CTE Caching

DuckDB 1.1 got smart about repeated CTEs. This query used to run the expensive part twice:

WITH expensive_stuff AS (
  SELECT customer_id, SUM(complex_calculation) as total
  FROM massive_table
  GROUP BY customer_id
)
SELECT e1.total, e2.total  
FROM expensive_stuff e1
JOIN expensive_stuff e2 ON e1.customer_id = e2.customer_id;

Now it automatically caches the CTE. Makes some analytical queries much faster. Would have saved me 3 hours of manual optimization bullshit if I'd known about this earlier.

S3 and Remote Files

Caching Settings That Work

If you're stuck reading from S3, these help:

SET enable_external_file_cache = true;  -- DuckDB 1.3+ only
SET parquet_metadata_cache = true;
SET threads = 32;  -- Way more than your CPU cores

Note: enable_external_file_cache breaks on some versions of 1.3.0 - if you get weird S3 errors, comment that line out.

Partition Your Queries Properly

This matters way more than I thought for partition elimination:

-- Good: Skips whole files
SELECT * FROM 's3://bucket/year=2024/month=09/*.parquet'
WHERE year = 2024 AND month = 09;

-- Bad: Scans everything
SELECT * FROM 's3://bucket/*/*.parquet'
WHERE some_random_column = 'value';

The first query skips files it doesn't need. The second reads everything.

Connection Reuse

Common mistake:

## Don't do this - creates connection overhead every time
for query in my_queries:
    conn = duckdb.connect(\"mydb.duckdb\")
    result = conn.execute(query)
    conn.close()

Just reuse the damn connection:

## Much faster
conn = duckdb.connect(\"mydb.duckdb\") 
for query in my_queries:
    result = conn.execute(query)
conn.close()

Connection overhead adds up when you're running lots of queries. Our ETL job went from 2 hours to 15 minutes just by not being stupid about connections.

File Formats

If you must use CSV, compress it. gzip helps a lot over network.

Window Functions: The Right Way

DuckDB 1.1 made window functions way faster if you use the right syntax:

-- This streams efficiently
SELECT customer_id,
       SUM(amount) OVER (
           PARTITION BY customer_id 
           ORDER BY order_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) as running_total
FROM orders;

Use ROWS BETWEEN when possible instead of RANGE BETWEEN.

Monitoring That Actually Matters

Skip the fancy monitoring. Just check these:

-- Memory pressure
FROM duckdb_memory();

-- Temp file usage  
FROM duckdb_temporary_files();

If memory is consistently >90% or you see big temp files, you need more RAM or better queries.

What Doesn't Actually Help

These settings usually don't help much:

• streaming_buffer_size: Only matters in specific network cases
• Perfect thread calculations: Use your core count, adjust if needed
• Micro-optimizing everything: Focus on memory, threads, storage first

Memory limit and thread count solve most performance problems. Everything else is edge cases unless your setup is completely fucked.