DuckDB Production Performance Optimization: A Complete Pipeline from Slow Queries to Sub-Second Responses

TL;DR: DuckDB’s default configuration is a good balance for “out of the box” use, but production environments require targeted tuning. This article demonstrates the combination of five core optimization techniques through an e-commerce data analysis case study, reducing million-row queries from 47 seconds to 1.2 seconds.

1. Background: Why DuckDB Sometimes Isn’t Fast Enough

I recently helped an e-commerce client with data analysis. The same query went from 47 seconds to 1.2 seconds after optimization. The gap wasn’t in the code, wasn’t in the hardware—it was in the details that are easy to overlook.

DuckDB’s columnar storage engine and vectorized execution are already fast. But default configurations and query habits can lead to suboptimal performance. Especially when you’re handling GB-scale data, complex JOINs, or iterative analysis scenarios, optimization strategies become critical.

Today we’re not discussing scattered tips—we’re building a complete optimization pipeline from data ingestion to query acceleration, with a corresponding optimization technique at every step.

2. Technique 1: CTAS + Parquet — Lock Intermediate Results as Columnar Storage

2.1 The Problem: CTE Catastrophic Expansion

Many people write complex analyses using CTEs (Common Table Expressions) for iterative refinement:

WITH cleaned AS (
 SELECT * FROM read_csv_auto('orders.csv')
 WHERE total_amount > 0
),
filtered AS (
 SELECT * FROM cleaned
 WHERE order_time >= '2024-01-01'
),
enriched AS (
 SELECT f.*, u.region
 FROM filtered f
 LEFT JOIN users u ON f.user_id = u.id
)
SELECT category, COUNT(*) as cnt, SUM(total_amount) as gmv
FROM enriched
GROUP BY category;

The problem: DuckDB inlines CTEs by default. The optimizer may repeatedly scan data, causing the same computation to be executed multiple times.

2.2 Solution: Materialize as Parquet

import duckdb

conn = duckdb.connect('ecommerce.db')

# Step 1: Materialize cleaned wide table as Parquet (write once, read many times)
conn.execute("""
 CREATE TABLE clean_orders AS
 SELECT 
 order_id,
 user_id,
 order_time,
 total_amount,
 category,
 region
 FROM read_csv_auto('orders.csv')
 WHERE total_amount > 0
 AND order_time >= '2024-01-01'
""")

# Step 2: Export as Parquet, subsequent queries read directly from file
conn.execute("COPY clean_orders TO 'data/clean_orders.parquet' (FORMAT PARQUET)")

# Step 3: Subsequent analysis reads Parquet directly
result = conn.execute("""
 SELECT category, 
 COUNT(*) as order_cnt,
 SUM(total_amount) as gmv,
 AVG(total_amount) as avg_order
 FROM read_parquet('data/clean_orders.parquet')
 GROUP BY category
 ORDER BY gmv DESC
""").fetchdf()

Core logic: Parquet is columnar storage. Aggregation queries only read the columns they need, not entire rows. DuckDB has specialized vectorized read optimization for Parquet.

2.3 Comparison with Traditional Approaches

3. Technique 2: Precise Parallelism Control — Don’t Let CPUs Fight

3.1 The Trap of Default Parallelism

DuckDB automatically detects core count for parallel execution by default, but in some scenarios, “automatic” is worse than “manual”:

import duckdb

conn = duckdb.connect('analysis.db')

# Scenario A: Analysis machine runs only DuckDB, exclusive CPU
conn.execute("SET threads TO 8") # Explicit specification, avoid dynamic switching overhead

# Scenario B: Other processes on the server, limit DuckDB usage
conn.execute("SET threads TO 4")
conn.execute("SET memory_limit TO '8GB'") # Prevent OOM

# Scenario C: Single-user laptop, let DuckDB use all cores
conn.execute("SET threads TO 0") # 0 means use all available cores

3.2 The Relationship Between Parallelism and Memory

Parallelism affects not just speed, but memory usage:

# Key: Higher parallelism means higher memory consumption
conn.execute("SET max_threads TO 4")
conn.execute("SET memory_limit TO '16GB'")
conn.execute("SET temp_directory TO '/tmp/duckdb_temp'") # Overflow to disk, prevent memory explosion

Pro tip: Use EXPLAIN to view the query plan. If you see “SeqScan” or “Materialize” nodes, parallelism isn’t working. Combine with these settings to force the optimizer to use parallel aggregation:

SET enable_projection_pushdown TO true;
SET enable_multiphase_agg TO true;

4. Technique 3: COPY FROM Instead of Loop Writing

4.1 The Most Common Performance Trap

Many people process data like this:

# ❌ Wrong approach: Loop writing, each is an independent transaction
for chunk in pd.read_csv('huge_data.csv', chunksize=10000):
 conn.execute("INSERT INTO target_table VALUES ?", [tuple(row) for row in chunk.itertuples(index=False)])

Problems with this approach:

• Each INSERT is an independent transaction with huge overhead
• Python loops are slow and can’t leverage DuckDB’s vectorized execution

4.2 Correct Bulk Write Approaches

# ✅ Option 1: COPY FROM (fastest, goes directly through columnar path)
conn.execute("COPY target_table FROM 'huge_data.csv' (FORMAT CSV, HEADER)")

# ✅ Option 2: Pandas batch insert (suitable for data needing preprocessing)
import pandas as pd
df = pd.read_csv('huge_data.csv')
with conn.transaction():
 for batch in pd.concat([df[i:i+50000] for i in range(0, len(df), 50000)]):
 batch.to_sql('target_table', conn, if_exists='append', index=False, method='multi')

# ✅ Option 3: Direct read_csv + CTAS (best for pure analysis scenarios)
conn.execute("""
 CREATE TABLE target_table AS
 SELECT * FROM read_csv_auto('huge_data.csv')
""")

Performance comparison: For 1 million row CSV writes, COPY FROM is approximately 40x faster than loop writing.

5. Technique 4: Partition Pruning — Let DuckDB Smartly Skip Files

5.1 The Right Way to Store Partitioned Data

When your data is stored in time partitions, DuckDB can automatically skip unnecessary partitions:

import duckdb

# Correct directory structure (DuckDB auto-detects partitions)
# data/sales/year=2024/month=01/part-000.parquet
# data/sales/year=2024/month=02/part-000.parquet
# data/sales/year=2025/month=01/part-000.parquet

conn = duckdb.connect('sales.db')

# DuckDB auto-detects partition paths, reads only 2025 data
result = conn.execute("""
 SELECT month, SUM(revenue) as total
 FROM read_parquet('data/sales/**/*.parquet')
 WHERE year = 2025
 GROUP BY month
 ORDER BY total DESC
""").fetchdf()

5.2 Key Requirements for Partition Pruning

Critical point: Partition pruning requires the partition directory naming to follow the key=value convention. If filenames don’t follow this standard, DuckDB can’t recognize them and will do a full scan.

-- Force partition pruning (optimizer may fail in some cases)
SET allow_partitioned_scan_pruning TO true;

5.3 Correct Directory Structure

data/sales/
├── year=2024/
│ ├── month=01/
│ │ └── part-000.parquet
│ └── month=02/
│ └── part-000.parquet
└── year=2025/
 └── month=01/
 └── part-000.parquet

6. Technique 5: VIEW and MATERIALIZED VIEW — Separate Logic from Performance

6.1 Logical Views: Store Definitions, Not Data

conn = duckdb.connect('warehouse.db')

# Create logical view (stores definition only, not data)
conn.execute("""
 CREATE VIEW v_user_30d AS
 SELECT 
 u.user_id,
 u.registration_date,
 COUNT(o.order_id) as order_cnt_30d,
 SUM(o.total_amount) as gmv_30d
 FROM users u
 LEFT JOIN orders o ON u.user_id = o.user_id
 AND o.order_time >= CURRENT_DATE - INTERVAL 30 DAY
 GROUP BY u.user_id, u.registration_date
""")

6.2 Materialized Views: Store Results, Separate Read/Write

# Create materialized view (stores results)
conn.execute("""
 CREATE TABLE mv_user_30d AS
 SELECT * FROM v_user_30d
""")

# Periodically refresh materialized view (much faster than recalculating)
conn.execute("REFRESH MATERIALIZED VIEW mv_user_30d")

# All subsequent queries read from materialized view instead of recalculating
result = conn.execute("""
 SELECT * FROM mv_user_30d 
 WHERE gmv_30d > 1000 
 ORDER BY gmv_30d DESC 
 LIMIT 100
""").fetchdf()

6.3 Performance Comparison

7. Combined Results: The Optimization Pipeline in Action

Combining all five techniques, here’s the typical performance improvement for data analysis queries (Intel i7-13700K + 32GB RAM + NVMe SSD, 1 million row e-commerce order data):

8. Summary of Optimization Thinking

The core optimization思路 boils down to three points:

1. Reduce I/O: Columnar storage (Parquet), partition pruning
2. Reduce redundant computation: Materialized views, CTAS
3. Reduce unnecessary parallelism: Manual thread count control

Don’t start by tweaking parameters. First, use EXPLAIN to view the query plan, find where the bottleneck is, then optimize targetedly. Often, the right file format choice (Parquet vs CSV) solves 80% of performance problems.

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 contact@duckdblab.org 反馈。