DuckDB Memory Management

DuckDB defaults to consuming 80% of system RAM for its buffer pool. On a developer laptop with 16 GB, that’s fine. In a 2 GB Kubernetes pod, that’s 1.6 GB — leaving 400 MB for the Go or JVM runtime, the application, and the OS. That’s how you get OOM-killed. This post walks through how DuckDB manages memory in-process, what happens when queries exceed the memory limit, and how to tune settings for container deployments.

The Buffer Pool

DuckDB manages memory through a single buffer pool. All data blocks, hash tables, sort buffers, and intermediate results are allocated from this pool. The pool size is controlled by the memory_limit setting.

SELECT
    current_setting('memory_limit') AS memory_limit,
    current_setting('threads') AS threads,
    current_setting('temp_directory') AS temp_directory;

On a Mac with 16 GB of RAM, memory_limit defaults to roughly 13 GB. You can change it at any time:

SET memory_limit = '256MB';

The two other settings that matter for memory behavior are threads (how many parallel workers DuckDB spawns) and temp_directory (where spill files go when memory runs out).

Watching Memory Under Load

Different query operations consume memory in different ways. A streaming aggregation like SUM needs almost no memory regardless of input size — it accumulates a single value. A GROUP BY builds a hash table that grows with the number of distinct groups. An ORDER BY must materialize the entire result before producing any output.

The duckdb_memory() function shows current buffer pool usage by category:

SET memory_limit = '512MB';
SET threads = 2;

CREATE TABLE events AS
SELECT
    i AS id,
    'user_' || (i % 10000)::VARCHAR AS user_id,
    ('2024-01-01'::DATE + (i % 365)::INTEGER)::TIMESTAMP AS event_time,
    CASE i % 5
        WHEN 0 THEN 'click'
        WHEN 1 THEN 'view'
        WHEN 2 THEN 'purchase'
        WHEN 3 THEN 'scroll'
        WHEN 4 THEN 'hover'
    END AS event_type,
    repeat('x', 100) AS payload
FROM range(5000000) t(i);

SELECT
    tag,
    size / (1024*1024) AS size_mb
FROM duckdb_memory()
WHERE size > 0
ORDER BY size DESC;

After creating the 5 million row table, you will see buffer pool categories like BASE_TABLE consuming significant memory. Running a GROUP BY on user_id and event_type adds HASH_TABLE allocations. A self-join builds probe tables. Each operation has a distinct memory footprint that you can observe through duckdb_memory().

Spill to Disk

When a query needs more memory than memory_limit allows, DuckDB does not crash. Instead, it spills intermediate results to disk — evicting buffer pool blocks to temporary files in the temp_directory. The query runs slower but completes correctly.

SET memory_limit = '50MB';
SET temp_directory = '/tmp/duckdb_spill_demo';

CREATE TABLE big_table AS
SELECT
    i AS id,
    random() AS val,
    'user_' || (i % 50000)::VARCHAR AS user_id,
    repeat('payload_data_', 5) AS wide_col
FROM range(2000000) t(i);

SELECT count(*) FROM (
    SELECT * FROM big_table ORDER BY val, user_id, wide_col
);

This ORDER BY on 2 million rows with a 50 MB memory limit forces DuckDB to write sort runs to /tmp/duckdb_spill_demo. After the query, you can inspect the spill directory:

ls -la /tmp/duckdb_spill_demo/
du -sh /tmp/duckdb_spill_demo/

The mechanism is partitioned spilling — DuckDB does not dump the entire working set to disk. It partitions the data (by hash for joins, by sort runs for ORDER BY), spills some partitions, and processes them in phases. Temp files are cleaned up when the connection closes.

Hash aggregation also spills. A GROUP BY with string_agg that produces large intermediate results will trigger the same behavior, writing hash partitions to disk when the hash table exceeds available memory.

What Happens Without Spill

If temp_directory is set to an empty string, DuckDB cannot spill. When a query exceeds the memory limit, it fails with an error:

SET memory_limit = '50MB';
SET temp_directory = '';

SELECT count(*) FROM (
    SELECT * FROM big_table ORDER BY val, user_id, wide_col
);
-- Out of Memory Error: could not allocate block of X bytes

The process does not crash. DuckDB catches the allocation failure and returns an error. The connection remains usable — only the query failed. This is much better than a kernel OOM kill, but the query did not complete. The fix is to configure temp_directory to a writable volume.

Tuning for Containers

A realistic configuration for a 2 GB Kubernetes pod with 2 CPU cores:

SET memory_limit = '768MB';
SET threads = 2;
SET temp_directory = '/tmp/duckdb_spill';
SET max_temp_directory_size = '2GB';

The reasoning behind each setting:

Setting	Value	Why
memory_limit	768 MB	Leaves ~1.2 GB for the app runtime, the OS, and headroom
threads	2	Matches the CPU request — more threads than cores wastes time in scheduling
temp_directory	/tmp/duckdb_spill	Points to an emptyDir volume mount
max_temp_directory_size	2 GB	Caps spill disk usage to avoid exhausting ephemeral storage

In your Go application startup:

db.Exec("SET memory_limit = '768MB'")
db.Exec("SET threads = 2")
db.Exec("SET temp_directory = '/scratch/tmp'")
db.Exec("SET max_temp_directory_size = '2GB'")

And in the pod spec:

resources:
  requests: { memory: 2Gi, cpu: "2" }
  limits:   { memory: 2Gi, cpu: "2" }
volumes:
  - name: duckdb-scratch
    emptyDir:
      sizeLimit: 5Gi
volumeMounts:
  - name: duckdb-scratch
    mountPath: /scratch

Comparison with Snowflake

Both DuckDB and Snowflake spill intermediate query results to disk when memory is exhausted. The goal is identical — let queries succeed even when the working set exceeds available RAM. The implementations reflect their different architectures.

DuckDB has one spill tier: memory to local disk. Snowflake has two: memory to local SSD, then to remote object storage (S3/GCS/Azure). Snowflake manages memory limits internally — you cannot configure them. If you are spilling to remote storage, the recommended fix is to use a larger warehouse.

DuckDB                              Snowflake
──────                              ─────────
Buffer Pool (memory_limit)          Node Memory (managed)
    │                                   │
    ▼                                   ▼
Local temp directory                Local SSD (tier 1)
                                        │
                                        ▼
                                    Remote Storage (tier 2)

Aspect	DuckDB	Snowflake
Spill tiers	1 (local disk)	2 (local SSD, then remote storage)
Memory limit	User-configurable	Managed internally
Scale-up option	Increase memory_limit	Resize warehouse (XS to S to M)
Scale-out option	None (single process)	Multi-cluster warehouse
Cost of spill	Slower queries (disk I/O)	Slower queries + potential dollar cost

With DuckDB embedded in a pod, you are responsible for setting the memory limit and providing a writable volume for spill. You have only one tier — if the emptyDir fills up, the query fails. You cannot scale out by adding nodes. The tradeoff: DuckDB gives you full control and zero cost for idle time. Snowflake gives you elastic scale and managed infrastructure but charges per-second of compute.