Introduction: A Real Performance Disaster

A cross-border e-commerce team needed to run a daily clickstream analytics report. The data was simple — 320 million rows per day, ~48GB of Parquet-formatted logs. The query was straightforward: aggregate PV, UV, and bounce rate by user_id over a 30-day window.

The result? The query took 47 minutes and 23 seconds.

What stung more was that the machine wasn’t bad: 8 CPU cores, 32GB RAM, SSD. The problem was entirely in the defaults — DuckDB’s out-of-box configuration is far from optimal for analytical batch processing.

This article documents the complete optimization journey from 47 minutes to 18 seconds. Every step comes with reproducible SQL, configuration commands, and real performance numbers.

Step 1: Parallelism and Memory — The Biggest Win

The Problem

DuckDB defaults to threads = CPU cores (8 here) and memory_limit = 80% of system memory (~25GB). But during execution, DuckDB allocates approximately memory_limit / threads per thread for hash join and aggregate work memory.

This means: 8 threads × 3.1GB = 24.8GB. Sounds fine, right? Wrong.

In practice, operators don’t distribute memory evenly. Hash join build phases and aggregate hash table expansions request bursts of extra memory. When that exceeds the per-thread budget, the result is spill to disk.

You can check for spills easily:

EXPLAIN ANALYZE SELECT ...;
-- Look for lines containing "Spilled"

The pre-tuning EXPLAIN ANALYZE output showed heavy spill in both HASH_JOIN and HASH_GROUP_BY operators. Disk I/O became the bottleneck, and query time grew exponentially.

The Solution

-- Before (default)
SET threads = 8;
SET memory_limit = '6GB';

-- After (tuned)
SET threads = 4;               -- Halved!
SET memory_limit = '24GB';     -- Give DuckDB room to breathe
SET temp_directory = '/mnt/ssd/tmp';  -- Must be on SSD

Why fewer threads?

This counter-intuitive optimization is about memory allocation logic:

With 8 threads fighting over 6GB of usable memory, each gets less than 1GB. Even raising memory_limit to 24GB still gives only 3GB/thread. For hash joins building hash tables with hundreds of millions of rows, 3GB is nowhere near enough.

Cutting to 4 threads gives each thread 6GB. Memory hit rate jumped from 23% to 91%.

Result: 47 minutes → 9 minutes 12 seconds ✅

The Temp Directory Must Be SSD

temp_directory is often overlooked. DuckDB spill writes are typically 2-3x the intermediate data size. HDD random write latency directly doubles query time.

-- Check current temp directory
SELECT current_setting('temp_directory');

-- Force to SSD if needed
SET temp_directory = '/mnt/ssd/duckdb_tmp';

Step 2: Parquet Read Parameters — A Free 40% Speedup

The Problem

Most people don’t realize DuckDB’s default Parquet reading behavior causes significant I/O amplification. The default parquet_file_reader_count = 8 means 8 file readers competing for page cache, causing cache thrashing — frequent page eviction and reloading.

Before tuning, Parquet scan throughput was only 780 MB/s, far below the SSD’s typical 2-3 GB/s capability.

The Solution

-- The magic three
SET parquet_file_reader_count = 2;  -- Reduce reader contention
SET parquet_prefetch_mode = 'true'; -- Prefetch next row group
SET force_compression = 'zstd';     -- Compress intermediate results

Why these three parameters work:

1. parquet_file_reader_count = 2: Fewer readers means the OS concentrates page cache on fewer file handles. Page cache hit rate rose from 34% to 78%.

2. parquet_prefetch_mode = true: DuckDB asynchronously prefetches the next row group before the current one finishes. This is especially effective for sequential scans on columnar storage.

3. force_compression = 'zstd': Intermediate results use zstd compression. zstd offers 2-3x better compression ratio than snappy while maintaining 500+ MB/s decompression speed. For repeatedly-scanned intermediate results, this reduces memory bandwidth pressure.

Result: 9 min 12s → 5 min 38s ✅ (Pure config changes, zero code cost)

Verify the Results

SET parquet_file_reader_count = 2;
SET parquet_prefetch_mode = 'true';
SET force_compression = 'zstd';

EXPLAIN ANALYZE SELECT count(*) FROM read_parquet('clicks/*.parquet');
-- Result: Parquet Scan throughput: 1.12 GB/s

Step 3: Multi-Stage Aggregation — The Cleverest Optimization

The Problem

The bottleneck was count(DISTINCT session_id):

-- ❌ Original query
SELECT user_id, 
       count(*) AS pv,
       count(DISTINCT session_id) AS sessions,
       count(*) FILTER (WHERE page_depth = 1) AS bounces
FROM clicks
WHERE ts >= current_date - 30
GROUP BY user_id;

count(DISTINCT session_id) triggers DuckDB’s ApproxCountDistinct fallback. With tens of millions of distinct session IDs, the hash table rehashing cost is enormous and cannot be effectively partitioned.

More fundamentally, the query aggregates directly from events to users, ignoring the natural hierarchical structure of clickstream data.

The Solution: Three-Level Aggregation

Clickstream data has three natural granularity levels:

1. Event level (320M rows): individual page clicks
2. Session level (28M rows): browsing sessions
3. User level (millions): individual users

The correct approach is to aggregate layer by layer:

-- ✅ Step 1: Event → Session aggregation
CREATE TABLE click_sessions AS
SELECT 
    user_id, 
    session_id, 
    count(*) AS page_depth,
    bool_or(page_depth = 1) AS is_bounce
FROM clicks
WHERE ts >= current_date - 30
GROUP BY user_id, session_id;

-- Data compressed from 320M to 28M rows (91% reduction)

-- ✅ Step 2: Session → User aggregation
SELECT 
    user_id,
    sum(page_depth) AS pv,
    count(*) AS sessions,
    round(sum(CASE WHEN is_bounce THEN 1 ELSE 0 END)::FLOAT / count(*), 4) AS bounce_rate
FROM click_sessions
GROUP BY user_id
HAVING sum(page_depth) > 5;

Why is this so effective?

Key insight: count(DISTINCT x) is much slower than GROUP BY x + count(*). The former needs a giant hash set for deduplication; the latter groups by key and counts directly. Same logical result, vastly different execution plans.

Note the use of bool_or(page_depth = 1) — a clever ordered aggregate alternative. bool_or scans each row in the group and returns true as soon as any row matches. More efficient than count(*) FILTER (WHERE ...) because it can short-circuit.

Result: 5 min 38s → 1 min 02s ✅

Step 4: Materialized Sorted Tables — The Final Push

The Problem

We’d already cut the query from 47 minutes to 1 minute. But since this is a daily scheduled task, we can pay a one-time sorting cost and have every subsequent query benefit.

The Solution

-- Create a sorted materialized table
CREATE TABLE clicks_sorted AS
SELECT * FROM clicks
ORDER BY user_id, ts;

-- Update statistics
ANALYZE clicks_sorted;

Why sorting matters so much:

DuckDB’s columnar storage + min-max indexes love sorted data. When you query WHERE user_id = 123:

1. DuckDB reads the user_id column’s statistics (min/max per row group)
2. If data is sorted by user_id, adjacent user_ids fall in consecutive row groups
3. Irrelevant row groups are skipped entirely (page pruning)

Production ETL Pattern:

import duckdb

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

# Sort and insert daily incremental data
conn.execute("""
    INSERT INTO clicks_sorted
    SELECT * FROM read_parquet('daily/clicks_2026-05-26.parquet')
    ORDER BY user_id, ts;
""")

# Or use CTAS for atomic replacement
conn.execute("""
    CREATE TABLE clicks_sorted_new AS
    SELECT * FROM clicks_sorted
    UNION ALL
    SELECT * FROM read_parquet('daily/clicks_2026-05-26.parquet')
    ORDER BY user_id, ts;
""")

# Atomic swap
conn.execute("ALTER TABLE clicks_sorted RENAME TO clicks_sorted_old;")
conn.execute("ALTER TABLE clicks_sorted_new RENAME TO clicks_sorted;")
conn.execute("DROP TABLE clicks_sorted_old;")

# Update statistics
conn.execute("ANALYZE clicks_sorted;")

Result: 1 min 02s → 18.7 seconds ✅

The Final Configuration Checklist

Replace your DuckDB configuration with these parameters:

-- Optimal settings for analytical batch processing
SET threads = 4;                          -- Usually half your CPU cores
SET memory_limit = '24GB';                -- 70%-80% of available memory
SET temp_directory = '/mnt/ssd/tmp';      -- Must point to SSD
SET parquet_prefetch_mode = 'true';       -- Enable prefetching
SET parquet_file_reader_count = 2;        -- Reduce reader contention
SET force_compression = 'zstd';           -- Compress intermediate results

Full Performance Timeline

From 47 minutes 23 seconds → 18.7 seconds. 152x speedup. Zero hardware investment.

Comparison with Traditional Approaches

DuckDB tuning is about understanding data flow and memory allocation, not throwing more hardware at the problem. Most optimization work is configuration-level — no code changes needed.

Monetization Ideas

This performance tuning expertise has several market applications:

1. DuckDB Tuning Consulting

Many small-to-medium teams struggle with expensive big data solutions (Spark, Flink) but hit configuration issues when migrating to DuckDB. Offer on-site tuning services:

• One-time diagnosis: $300-800 (report + config checklist)
• Ongoing maintenance: $1000-2000/month (monitoring + ETL optimization)
• Target clients: E-commerce analytics teams, SaaS product data departments

2. SQL Tuning Template Product

Package these parameters and common query patterns into a DuckDB Performance Toolkit:

• One-click script: automatically detects hardware and generates optimal parameters
• Common query templates: 10+ scenarios (clickstream, order analytics, retention)
• Pricing: $49/package, or as subscription content

3. Data Pipeline Migration Service

Help teams migrate from Spark/ClickHouse to DuckDB:

• Assessment + PoC: $800-1500
• Full migration + tuning: $3000-8000 (data size dependent)
• Value prop: 10x cost reduction, equivalent or better performance

4. Knowledge Products

Package this article + more case studies into a DuckDB Performance Tuning Course:

• 10 episodes covering OLAP, streaming, ML inference, etc.
• Pricing: $29-49, expected 3-8% conversion
• Distribution: Gumroad, Udemy, or Substack

Summary

DuckDB performance tuning isn’t magic. Four steps — right-size memory and parallelism, optimize Parquet reading, leverage natural data hierarchies with multi-stage aggregation, and sort materialized tables for column pruning — each with measurable, quantifiable returns.

The most important takeaway: the most elegant SQL isn’t always the fastest. Sometimes the counter-intuitive optimizations (fewer threads, more intermediate tables) are the right answer.

Before you throw more hardware at a slow query, check these six parameters and your SQL structure. The money you save could buy a lot of SSDs.

📺 Watch the video tutorial: youtube.com/@duckdblab