DuckDB Optimization: A Developer's Guide to Better Performance

If you are already using DuckDB, this guide will help you with some optimization techniques that can improve your application's performance. If you are new to DuckDB, don't fret — you'll still learn something new. I will share some of the practical tips that helped me optimize my applications. Let's dive in!

Why DuckDB?

Before we jump into optimization techniques, let's quickly discuss what makes DuckDB stand out. In the official DuckDB documentation, many benefits are listed. Give it a read.

Loading Data 

One thing to remember about data loading in DuckDB is that the file format you choose makes a huge difference. Here's what I've learned:

-- Here's how I usually load my Parquet files
SELECT * FROM read_parquet('my_data.parquet');

-- And here's a neat trick for CSV files
CREATE TABLE my_table AS 
SELECT * FROM read_csv_auto('my_data.csv');

Tip: If you're working with CSV files, consider converting them to Parquet. Parquet files are compressed, columnar, and super fast to query.

Chunking: Because Size Matters!

I've successfully processed datasets in chunks, especially the larger ones. It's not only efficient, but also can help you debug any issues smoothly.

import duckdb
import pandas as pd

def process_big_data(file_path, chunk_size=100000):
    # Let's break this elephant into bite-sized pieces
    conn = duckdb.connect(':memory:')
    
    print("Ready to tackle this big dataset!")
    
    processed_count = 0
    while True:
        # Grab a chunk
        chunk = conn.execute(f"""
            SELECT * 
            FROM read_csv_auto('{file_path}')
            LIMIT {chunk_size}
            OFFSET {processed_count}
        """).fetchdf()
        
        if len(chunk) == 0:
            break
            
        # Implement your logic here
        process_chunk(chunk)
        
        processed_count += len(chunk)
        print(f"Processed {processed_count:,} rows... Keep going!")

I like to think of this as eating a pizza — you wouldn't try to stuff the whole thing in your mouth at once, right? The same goes for data processing.

Query Optimization

I've used some queries that would make any database. I learned some of the best practices the hard way (well, hard on the database, too). Here are some tips:

1. Use EXPLAIN ANALYZE to See What's Happening Under the Hood

This will show you exactly how DuckDB is processing your query. This should inform you how to further tune your query.

EXPLAIN ANALYZE
SELECT category, COUNT(*) as count
FROM sales
WHERE date >= '2024-01-01'
GROUP BY category;

2. Be Specific With Columns 

It's like packing for a weekend trip — do you really need to bring your entire wardrobe?

-- Good: Only fetching what we need
SELECT user_id, SUM(amount) as total_spent
FROM purchases
WHERE category = 'books'
GROUP BY user_id;

-- Not great: Why fetch all columns when we only need two?
SELECT * FROM purchases
WHERE category = 'books';

3. Smart Joins Make Happy Databases

It's more like organizing a party — you wouldn't invite everyone in town and then figure out who knows each other, right?

- Good: Filtering before the join
SELECT u.name, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'Canada'
  AND o.status = 'completed';

-- Not optimal: Joining everything first
SELECT u.name, o.order_date
FROM (SELECT * FROM users) u
JOIN (SELECT * FROM orders) o ON u.id = o.user_id
WHERE u.country = 'Canada'
  AND o.status = 'completed';

4. Window Functions Done Right 

It's like keeping a running score in a game — you update as you go, not by recounting all points for each play.

- Good: Efficient window function usage
SELECT 
    product_name,
    sales_amount,
    SUM(sales_amount) OVER (PARTITION BY category ORDER BY sale_date) as running_total
FROM sales
WHERE category = 'electronics';

-- Less efficient: Using subqueries instead
SELECT 
    s1.product_name,
    s1.sales_amount,
    (SELECT SUM(sales_amount) 
     FROM sales s2 
     WHERE s2.category = s1.category 
       AND s2.sale_date <= s1.sale_date) as running_total
FROM sales s1
WHERE category = 'electronics';

Memory Management

Here's another thing that I learned the hard way: always set memory limits. Here's how I keep things under control:

• Set memory_limit to 50-70% of available system RAM
• Set max_memory to about half of memory_limit
• Monitor and adjust based on your workload

First, let's understand how DuckDB uses memory:

-- Check current memory settings
SELECT * FROM duckdb_settings() 
WHERE name LIKE '%memory%';

-- View current memory usage
SELECT * FROM pragma_database_size();

Basic Memory Settings 

Think of these settings as setting a budget for your shopping:

• memory_limit is like your total monthly budget
• max_memory is like setting a limit for each shopping trip
• temp_directory is like having a storage unit when your closet gets full

-- Set overall memory limit
SET memory_limit='4GB';

-- Set maximum memory per query
SET temp_directory='/path/to/disk';  -- For spilling to disk
SET max_memory='2GB';                -- Per-query limit

Monitoring Memory Usage

-- Enable progress bar to monitor operations
SET enable_progress_bar=true;

-- Enable detailed profiling
SET enable_profiling=true;
PRAGMA enable_profiling;

-- After running your query, check the profile
PRAGMA profile;

Memory-Related Warning Signs 

Watch out for these signs of memory pressure:

1. Slow query performance
2. System becoming unresponsive
3. Query failures with "out of memory" errors
4. Excessive disk activity (spilling to disk)

Clean Up Regularly

Drop temporary tables and vacuum when needed:

-- Clean up temporary objects
DROP TABLE IF EXISTS temp_results;
VACUUM;

Conclusion

Always start with the basics, measure everything, and optimize where it matters most. Here's a quick checklist I use:

• Is my data in the right format? (Parquet is usually the answer)
• Am I processing data in chunks when dealing with large datasets?
• Are my queries as specific as possible?
• Have I set reasonable memory limits?