Introduction

In our previous two articles, we covered the basics of DuckDB memory management, including memory_limit, threads, temp_directory, and partition tables. However, many users still encounter tricky problems when deploying DuckDB in production:

• OOM errors: Queries crash suddenly with “OUT OF MEMORY”
• Slow queries: Query time grows exponentially as data volume increases
• Concurrency contention: Individual query performance drops sharply with multiple concurrent queries

This article focuses on production deployment scenarios, walking through OOM troubleshooting strategies, spill-to-disk optimization, and multi-thread concurrency tuning with real-world examples to help you build a stable and efficient DuckDB production environment.

Figure: DuckDB Memory Management Architecture — from SQL query execution to memory allocation, disk spilling, and thread scheduling

1. Production OOM Troubleshooting Guide

1.1 Typical OOM Error Scenarios

The most common OOM errors in production look like this:

duckdb::OutOfMemoryException: Out of Memory Error! 
Unable to allocate 268435456 bytes for a BlockManager.
Current memory usage: 8589934592 / 8589934592 bytes.
Consider increasing the memory limit or enabling spilling.

This error tells us three things:

1. The current query needs an additional 256MB of memory
2. Memory is already full (8GB / 8GB)
3. DuckDB suggests increasing memory_limit or enabling spill

1.2 Troubleshooting Steps

Step 1: Check current memory configuration

-- Check current memory settings
PRAGMA memory_limit;
PRAGMA memory_total;
PRAGMA memory_used;
PRAGMA temp_directory;

-- Expected output:
-- memory_limit  = 8589934592 (8 GB)
-- memory_total  = 10737418240 (10 GB)
-- memory_used   = 8234567890  (~7.7 GB)
-- temp_directory = /tmp/duckdb-temp

Step 2: Identify the OOM-triggering query

-- Use EXPLAIN ANALYZE to analyze memory usage of the query
EXPLAIN ANALYZE
SELECT 
    user_id,
    SUM(amount) AS total_spent,
    COUNT(*) AS order_count
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY total_spent DESC
LIMIT 100;

Step 3: Examine memory information in the execution plan

+--------------------------------------------------------------------+
|                          EXPLAIN ANALYZE                            |
+--------------------------------------------------------------------+
| Explain Analyze                                                    |
|  +--------------------------------------------------------------+  |
|  | Limit                                                        |  |
|  |   Output: user_id, total_spent, order_count                  |  |
|  |   Rows: 100                                                  |  |
|  |  +--------------------------------------------------------+  |  |
|  |  | Sort                                                   |  |  |
|  |  |   Output: total_spent DESC                             |  |  |
|  |  |   Rows: 150000 (est 120000)  -- Memory hotspot!        |  |  |
|  |  |  +------------------------------------------------+    |  |  |
|  |  |  | HashAggregate                                  |    |  |  |
|  |  |  |   Output: user_id, SUM(amount), COUNT(*)       |    |  |  |
|  |  |  |   Groups: 150000                               |    |  |  |
|  |  |  |   Estimated hash table size: 12 MB             |    |  |  |
|  |  |  +------------------------------------------------+    |  |  |
|  |  |  Filter: order_count > 5                             |  |  |
|  |  |  Rows Before Filter: 500000                          |  |  |
|  |  |  Rows After Filter: 150000                           |  |  |
|  |  +------------------------------------------------------+  |  |
|  |  Filter: created_at >= 2024-01-01                          |  |
|  |  File Scan [orders]                                        |  |
|  |    Rows Loaded: 5000000                                    |  |
|  |    Compression Ratio: 0.35                                 |  |
|  +--------------------------------------------------------------+  |
| Execution Time: 2340.5ms                                           |
+--------------------------------------------------------------------+

From the execution plan, we can see that the Sort operation is the largest memory consumer, with an estimated 150,000 groups.

1.3 Solutions

Solution 1: Increase memory_limit

-- Check available system memory
PRAGMA memory_total;

-- Set an appropriate memory limit (recommended: 60-80% of physical memory)
SET memory_limit = '12GB';

-- Re-run the query
EXPLAIN ANALYZE
SELECT user_id, SUM(amount) AS total_spent, COUNT(*) AS order_count
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY total_spent DESC
LIMIT 100;

Solution 2: Enable spill-to-disk (Recommended)

-- Set temp directory to an SSD path
PRAGMA temp_directory = '/data/duckdb-temp';

-- Enable spilling (enabled by default, but verify)
SET enable_spilling = true;

-- Execute the same query — DuckDB will automatically spill
-- intermediate results to disk
SELECT user_id, SUM(amount) AS total_spent, COUNT(*) AS order_count
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY total_spent DESC
LIMIT 100;

Solution 3: Optimize query structure to reduce memory usage

-- Original query (high memory consumption)
SELECT user_id, SUM(amount) AS total_spent, COUNT(*) AS order_count
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY total_spent DESC
LIMIT 100;

-- Optimized: Execute in steps, filter first then aggregate
-- Step 1: Filter by date range into a temp table
CREATE TEMP TABLE recent_orders AS
SELECT user_id, amount
FROM orders
WHERE created_at >= '2024-01-01';

-- Step 2: Aggregate on the smaller table
SELECT user_id, SUM(amount) AS total_spent, COUNT(*) AS order_count
FROM recent_orders
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY total_spent DESC
LIMIT 100;

2. Deep Dive into spill-to-disk Optimization

2.1 Understanding the Spill Mechanism

When query intermediate results exceed memory_limit, DuckDB automatically spills data to the directory specified by temp_directory. This process is transparent for most queries, but for large-scale data analysis, properly configuring spill can significantly reduce OOM risks.

How spill works:

1. DuckDB monitors current memory usage
2. When memory usage exceeds the threshold (default: 90% of memory_limit), spilling begins
3. Intermediate results are written to disk files in temp_directory
4. The query continues execution, reading spilled data from disk

2.2 Configuring temp_directory

-- Check current temp directory
PRAGMA temp_directory;

-- Recommended: Use an SSD-specific directory
PRAGMA temp_directory = '/data/duckdb-temp';

-- Or use ramfs (fast but data lost on restart)
PRAGMA temp_directory = 'ramfs:/duckdb-temp';

Performance comparison across storage media:

2.3 Spill Monitoring and Tuning

-- Enable detailed spill logging
SET application_name = 'memory_debug';
SET force_parallel_mode = 'on';

-- Execute a large query and observe spill behavior
SELECT 
    region,
    category,
    strftime(created_at, '%Y-%m') AS month,
    COUNT(*) AS order_cnt,
    SUM(amount) AS revenue
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2024-01-01'
GROUP BY region, category, strftime(created_at, '%Y-%m')
ORDER BY revenue DESC;

Use EXPLAIN ANALYZE to check where spills occur:

+---------------------------------------------------------------------+
|                          EXPLAIN ANALYZE                            |
+---------------------------------------------------------------------+
| Explain Analyze                                                     |
|  +---------------------------------------------------------------+  |
|  | Sort                                                          |  |
|  |   Output: region, category, month, order_cnt, revenue         |  |
|  |   Rows: 120 (est 100)                                          |  |
|  |   Spills: 1 (5.2 MB written to disk)  ⚠️ spill detected       |  |
|  |  +---------------------------------------------------------+  |  |
|  |  | HashAggregate                                           |  |  |
|  |  |   Output: region, category, month, COUNT(*), SUM        |  |  |
|  |  |   Groups: 120                                           |  |  |
|  |  |   Spills: 0                                             |  |  |
|  |  +---------------------------------------------------------+  |  |
|  |  Cross Product                                                |  |
|  |  +---------------------------------------------------------+  |  |
|  |  | Filter                                                  |  |  |
|  |  |   Rows: 5000000 (est 4800000)                            |  |  |
|  |  |   Spills: 2 (48.5 MB written to disk)  ⚠️ spill        |  |  |
|  |  +---------------------------------------------------------+  |  |
|  |  File Scan [orders]                                           |  |
|  |    Rows Loaded: 5000000                                       |  |
|  +---------------------------------------------------------------+  |
| Execution Time: 8520.3ms (incl. 2 spill passes)                     |
+---------------------------------------------------------------------+

From the results, we can see that the Filter stage had 2 spills, writing 48.5 MB to disk. This indicates the memory was insufficient to hold all intermediate results.

3. Multi-thread Concurrency Tuning

3.1 Understanding the threads Parameter

DuckDB defaults to using as many threads as CPU cores. In production, you need to adjust based on actual workload:

3.2 Concurrency Test Experiment

-- Create test dataset
CREATE TABLE performance_test AS
SELECT 
    gen AS id,
    DATE '2024-01-01' + (random() * 365)::INTEGER AS dt,
    CASE random() * 10
        WHEN 0 THEN 'A' WHEN 1 THEN 'B' WHEN 2 THEN 'C'
        WHEN 3 THEN 'D' WHEN 4 THEN 'E'
        WHEN 5 THEN 'F' WHEN 6 THEN 'G' WHEN 7 THEN 'H'
        WHEN 8 THEN 'I' WHEN 9 THEN 'J'
        ELSE 'K'
    END AS type,
    ROUND((random() * 9999 + 1)::NUMERIC, 2) AS value,
    (random() * 1000 + 1)::INTEGER AS qty
FROM generate_series(1, 5000000) AS gen;

-- Test query performance under different thread counts
\timing on

-- 1 thread
SET threads = 1;
SELECT type, SUM(value) AS total, AVG(qty) AS avg_qty
FROM performance_test
WHERE dt >= '2024-06-01'
GROUP BY type
ORDER BY total DESC;
-- Execution time: ~2.8s

-- 2 threads
SET threads = 2;
SELECT type, SUM(value) AS total, AVG(qty) AS avg_qty
FROM performance_test
WHERE dt >= '2024-06-01'
GROUP BY type
ORDER BY total DESC;
-- Execution time: ~1.5s

-- 4 threads
SET threads = 4;
SELECT type, SUM(value) AS total, AVG(qty) AS avg_qty
FROM performance_test
WHERE dt >= '2024-06-01'
GROUP BY type
ORDER BY total DESC;
-- Execution time: ~0.9s

-- 8 threads
SET threads = 8;
SELECT type, SUM(value) AS total, AVG(qty) AS avg_qty
FROM performance_test
WHERE dt >= '2024-06-01'
GROUP BY type
ORDER BY total DESC;
-- Execution time: ~0.7s

-- 16 threads (may be slower due to overhead)
SET threads = 16;
SELECT type, SUM(value) AS total, AVG(qty) AS avg_qty
FROM performance_test
WHERE dt >= '2024-06-01'
GROUP BY type
ORDER BY total DESC;
-- Execution time: ~0.8s (thread scheduling overhead)

\timing off

Test Results Summary:

3.3 Multi-Connection Concurrency Scenario

In production, multiple concurrent queries are common. Thread allocation strategy is critical:

-- Scenario: 4 concurrent queries, 2 threads each on an 8-core machine
-- Connection 1
SET threads = 2;
SELECT region, SUM(amount) FROM sales GROUP BY region;

-- Connection 2
SET threads = 2;
SELECT category, AVG(amount) FROM sales GROUP BY category;

-- Connection 3
SET threads = 2;
SELECT strftime(sale_date, '%Y-%m') AS month, COUNT(*) FROM sales GROUP BY month;

-- Connection 4
SET threads = 2;
SELECT region, category, SUM(amount) FROM sales GROUP BY region, category;

Concurrency Performance Comparison:

From the results, allocating 2 threads per connection achieves the best overall throughput in a 4-connection scenario.

4. Partition Pruning in Production

4.1 Time-Based Partitioning for Large Datasets

-- Create a monthly-partitioned large orders table
CREATE TABLE orders_large (
    order_id BIGINT,
    user_id BIGINT,
    amount DECIMAL(12,2),
    created_at TIMESTAMP,
    region VARCHAR
) PARTITION BY (created_at);

-- Load data (batch loading by different months)
INSERT INTO orders_large
SELECT gen, (random() * 1000000)::BIGINT,
       ROUND((random() * 9999 + 1)::NUMERIC, 2),
       DATE '2023-01-01' + (random() * 730)::INTEGER,
       CASE random() * 5
           WHEN 0 THEN 'East' WHEN 1 THEN 'South'
           WHEN 2 THEN 'North' WHEN 3 THEN 'West'
           WHEN 4 THEN 'Northeast' ELSE 'Other'
       END
FROM generate_series(1, 10000000) AS gen;

-- Check partition info
SELECT 
    table_name,
    partition_column,
    num_partitions
FROM duckdb_partitions()
WHERE table_name = 'orders_large';

+------------------+----------------+----------------+
|    table_name    |partition_column│num_partitions  |
+------------------+----------------+----------------+
| orders_large     │   created_at   │      24        │
+------------------+----------------+----------------+

4.2 Verifying Partition Pruning Effect

-- Query last month's data (unpartitioned vs partitioned)

-- Unpartitioned table (full table scan)
\timing on
SELECT region, SUM(amount) 
FROM orders_large
WHERE created_at >= '2024-08-01' AND created_at < '2024-09-01'
GROUP BY region;
-- Execution time: ~3200ms (scanning all 10M rows)

-- Partitioned table (automatic partition pruning, only scans Aug 2024)
\timing on
SELECT region, SUM(amount) 
FROM orders_large
WHERE created_at >= '2024-08-01' AND created_at < '2024-09-01'
GROUP BY region;
-- Execution time: ~150ms (scanning only 1/24 of data)

\timing off

Performance Comparison:

Partition pruning allows queries to scan only the target month’s data instead of the entire table, delivering over 20x performance improvement.

5. Production Deployment Checklist

When deploying DuckDB in production, follow this checklist:

-- One-click check script
SELECT 
    name,
    value
FROM duckdb_settings()
WHERE name IN (
    'memory_limit', 'threads', 'temp_directory',
    'enable_spilling', 'force_parallel_mode'
);

+---------------------+--------------------------+
| name                │ value                    │
+---------------------+--------------------------+
| memory_limit        │ 8589934592               │
| threads             │ 4                        │
| temp_directory      │ /data/duckdb-temp        │
| enable_spilling     │ true                     │
| force_parallel_mode │ on                       │
+---------------------+--------------------------+

6. Summary

Deploying DuckDB in production requires continuous iteration on memory management and performance tuning. This article covered:

1. OOM Troubleshooting: Use EXPLAIN ANALYZE to locate memory hotspots; resolve by increasing memory_limit or enabling spill
2. spill-to-disk: Properly configure temp_directory to SSD so DuckDB degrades gracefully instead of crashing when memory is insufficient
3. Multi-thread Tuning: Adjust threads based on concurrent connections to balance single-query performance and overall throughput
4. Partition Pruning: Partition large time-series data by month; automatic pruning skips irrelevant partitions, delivering 20x+ performance gains

Remember: there is no silver bullet. Every tuning decision should be tested against your actual data volume and query patterns. EXPLAIN ANALYZE is your best friend.

More DuckDB production tips, follow DuckDB Lab (duckdblab.org)