13. Tuning and operations

There are around two hundred settings and roughly ten of them matter. This chapter is those ten, and then the operational shapes DuckDB ends up in.

The settings that matter

-- how much memory queries may use.
-- Default: ~80% of system RAM.
SET memory_limit = '12GB';

-- how many threads. Default: number of cores.
SET threads = 8;

-- where to spill. Default: next to the database
-- file; UNSET for in-memory databases.
SET temp_directory = '/fast/scratch';
SET max_temp_directory_size = '200GB';

-- do not hold output in memory to preserve
-- input order. Chapter 10.
SET preserve_insertion_order = false;

-- cache Parquet/remote metadata across queries.
SET enable_object_cache = true;

-- a progress bar for long interactive queries
SET enable_progress_bar = true;

-- profiling
PRAGMA enable_profiling = 'query_tree';
PRAGMA profiling_output = '/tmp/profile.json';

-- lock down file and network access from SQL
SET enable_external_access = false;

-- checkpoint sooner or later
SET checkpoint_threshold = '1GB';

Everything else you can leave alone until a profile tells you otherwise.

-- see them all, with descriptions and defaults
SELECT name, value, description FROM duckdb_settings();

Memory sizing — the one that bites

memory_limit defaults to about 80% of system RAM, and DuckDB reads system RAM, not your cgroup limit.

   A 4 GB CONTAINER ON A 64 GB HOST

   DuckDB sees:      64 GB
   memory_limit:     ~51 GB
   cgroup limit:     4 GB

   → DuckDB happily allocates past 4 GB
   → the kernel OOM-kills the process
   → NO ERROR MESSAGE. Exit code 137.
   → hours lost to "DuckDB crashes randomly"

In any container, always set memory_limit explicitly, and set it below the container limit — not equal to it.

   RULE OF THUMB

   memory_limit ≈ (container limit) × 0.7

   Because DuckDB's accounting does NOT
   include:
     ─ the Python interpreter and your objects
     ─ Arrow buffers you are holding
     ─ pandas DataFrames you built from results
     ─ the extension libraries themselves
     ─ allocator fragmentation

Recent versions read cgroup limits in more situations than they used to. Set it anyway. It costs one line and it removes an entire class of unexplained failure.

And set temp_directory

   ✗ duckdb.connect()
     → in-memory, no temp_directory,
       NOWHERE TO SPILL.
       A big join fails instead of slowing
       down.

   ✓ con.execute("SET temp_directory = '/tmp/duck'")
     → the same join spills and completes.

In a container, /tmp is often small or tmpfs — which is RAM. Spilling to tmpfs is spilling into the thing you ran out of. Mount a real volume and point temp_directory at it.

Threads

   threads = number of cores        (default)

   LOWER IT WHEN:
     ─ you are in a container with a CPU quota.
       DuckDB may see the host's cores.
     ─ several DuckDB processes share a machine
     ─ the query is memory-bound, not CPU-bound
       — each thread has its own hash table,
       so 16 threads can use 16× the aggregate
       memory of 1

   RAISING IT ABOVE core count never helps.

The memory point is the non-obvious one. If a query OOMs, halving threads sometimes fixes it, because thread-local hash tables (chapter 4) are per-thread.

Writing efficiently

   FASTEST → SLOWEST for getting data in

   1. COPY / CREATE TABLE AS
      from Parquet or an Arrow table
      → columnar to columnar, no parsing

   2. INSERT INTO ... SELECT
      from a DataFrame or a file

   3. Appender API (C/Java/Go)
      → row-at-a-time, but batched
        efficiently

   4. executemany() with parameters

   5. a loop of INSERT statements
      → a transaction each. Chapter 11.
        Can be 100× slower than (1).

-- the fast path in one statement
CREATE TABLE events AS
SELECT * FROM read_parquet('s3://bucket/events/**/*.parquet');

-- and the fast path out
COPY (SELECT * FROM events)
TO 's3://bucket/out' (FORMAT parquet,
                      PARTITION_BY (year, month),
                      COMPRESSION zstd,
                      ROW_GROUP_SIZE 1000000);

ROW_GROUP_SIZE is worth setting deliberately. The default is 122,880 to match DuckDB’s own row group. Larger row groups compress better and give coarser pruning; smaller ones prune more finely and parallelise better. For files that other engines will read over object storage, one million rows or a few hundred megabytes per row group is a reasonable target.

Backup

There is no backup tool, because there does not need to be one.

   1. STOP WRITING (or CHECKPOINT), then
      COPY THE FILE.

      CHECKPOINT;   -- flush the WAL in

      cp analytics.duckdb backup.duckdb

      → and copy the .wal too if one exists,
        or neither.

   2. EXPORT, for version portability:

      EXPORT DATABASE 'backup_dir'
        (FORMAT parquet, COMPRESSION zstd);

      → writes a schema.sql, a load.sql, and
        one Parquet file per table.

      IMPORT DATABASE 'backup_dir';

   3. Or simply: keep the source data in
      Parquet and treat the .duckdb as a
      derived artefact you can rebuild.

Option 3 is the right answer far more often than people expect. If the database can be rebuilt from files in object storage in four minutes, it does not need backing up — it needs a rebuild script.

EXPORT DATABASE is also the upgrade path across a storage-format change. Since 1.0 that is rare, but it is how you move between major format versions when it happens.

Running it in the usual places

Docker

FROM python:3.12-slim

RUN pip install --no-cache-dir duckdb==1.4.0

# bake extensions in — do NOT download at runtime
RUN python -c "import duckdb; c=duckdb.connect(); \
    c.execute('INSTALL httpfs'); c.execute('INSTALL json')"

ENV HOME=/home/app
VOLUME /scratch

   ─ pin the version. Extensions are
     version-matched.
   ─ INSTALL at BUILD time. Runtime install
     means an outbound network call on every
     cold start, and a failure mode in
     air-gapped environments.
   ─ extensions cache in $HOME/.duckdb —
     make sure HOME is writable and
     persistent in the image layer.
   ─ mount a real volume for temp_directory.

Kubernetes

   DuckDB is not a StatefulSet. It is a
   LIBRARY INSIDE YOUR POD.

   ─ set memory_limit from the container's
     resource limit, via the downward API,
     at ~70%
   ─ set threads from the CPU limit, not
     from what /proc reports
   ─ emptyDir volume for temp_directory,
     sized for the spill
   ─ if a .duckdb file must persist, a
     ReadWriteOnce PVC and EXACTLY ONE
     writing pod (chapter 11)
   ─ read-only replicas: mount the same
     data read-only, or better, read Parquet
     from object storage and keep pods
     stateless

The Kubernetes-shaped answer is almost always “stateless pods reading Parquet from MinIO or S3.” A PVC with a single writer works, and it makes your pod a pet.

AWS Lambda

   ─ the wheel is ~40 MB. Use a Lambda LAYER
     or a container image; do not zip it into
     every function.
   ─ /tmp is 512 MB by default, configurable
     to 10 GB. That is your temp_directory
     and it is your spill budget.
   ─ memory_limit must be well under the
     function's memory, which is also its
     CPU allocation.
   ─ EXTENSIONS: bake them into the layer.
     A cold start that downloads httpfs is
     a cold start that sometimes times out.
   ─ set the extension directory to a
     read-only baked path:
       SET extension_directory = '/opt/duckdb-ext';

Lambda plus DuckDB plus Parquet in S3 is a genuinely good serverless analytics shape — a few hundred milliseconds of compute per query, no cluster, no idle cost — and every one of the bullets above is a thing people learn by having it fail.

CI

- run: pip install duckdb==1.4.0
- run: duckdb -c "SET memory_limit='2GB';
                  .read tests/fixtures.sql"

This is where DuckDB is most obviously correct and least often used. A test suite for a data pipeline that would otherwise need a warehouse, a Postgres container or a Spark session runs against an in-memory DuckDB in milliseconds, with no services to start. Combined with dbt-duckdb (chapter 12) it makes a data project’s CI genuinely fast.

Monitoring

There is no metrics endpoint, because there is no server. What exists:

SELECT * FROM duckdb_memory();            -- memory by component
SELECT * FROM duckdb_temporary_files();   -- what spilled
SELECT * FROM duckdb_databases();
SELECT * FROM duckdb_tables();
SELECT * FROM duckdb_extensions();
SELECT * FROM duckdb_settings();
SELECT * FROM pragma_database_size();     -- file size, block counts

-- profile every query to a file, for later analysis
PRAGMA enable_profiling = 'json';
PRAGMA profiling_output = '/var/log/duck/profile.json';

Instrument at your application’s level, not DuckDB’s. Wrap query calls, record duration and rows, and export from there — the same OpenTelemetry span you already emit around a Postgres call. DuckDB has no ambition to be observable on its own, because it is part of your process and your process is the thing being observed.

The operational checklist

   □ memory_limit set explicitly, ~70% of the
     container limit
   □ temp_directory set, on a real disk, sized
   □ threads set from the CPU quota, not the
     host
   □ extensions baked into the image, version
     pinned
   □ preserve_insertion_order = false for bulk
     export jobs
   □ enable_object_cache = true for repeated
     remote reads
   □ enable_external_access = false if SQL comes
     from users
   □ exactly one writer per database file
   □ a rebuild script, not a backup, where the
     data is derived
   □ the DuckDB version pinned in the lockfile

What to take from this chapter

• Ten settings matter: memory_limit, threads, temp_directory, max_temp_directory_size, preserve_insertion_order, enable_object_cache, enable_progress_bar, profiling, enable_external_access, checkpoint_threshold.
• memory_limit defaults to 80% of system RAM, not your cgroup. In a container that means the kernel kills you with exit code 137 and no error. Set it to ~70% of the container limit, always.
• Set temp_directory on in-memory connections — otherwise there is nowhere to spill and a big join fails instead of slowing down. Do not point it at tmpfs, which is the RAM you ran out of.
• Fewer threads sometimes fixes an OOM, because hash tables are per-thread.
• Load data with COPY / CREATE TABLE AS from Parquet or Arrow; a loop of INSERTs can be 100× slower. Set ROW_GROUP_SIZE deliberately when writing Parquet for others.
• Backup is CHECKPOINT then copy the file (and its .wal), or EXPORT DATABASE for portability — or a rebuild script, which is the right answer whenever the database is derived.
• Bake extensions into the image at build time and pin the version; runtime INSTALL is a network call on every cold start.
• In Kubernetes, DuckDB is a library in your pod, not a StatefulSet. Prefer stateless pods over object storage to a PVC with one writing pod.
• Lambda + DuckDB + Parquet in S3 is a genuinely good serverless shape — mind the 40 MB wheel, /tmp as your spill budget, and baked extensions.
• CI is where DuckDB is most obviously right and least often used.
• Monitor at your application’s level, not DuckDB’s — there is no server to scrape.

Next: failure modes — the fourteen ways it goes wrong, what each looks like, and what to do.