How do I set DuckDB's temp directory?

Run SET temp_directory = '/var/lib/duckdb/tmp' on the connection, and make sure that path is a real, sized volume rather than the container's ephemeral layer. This is the setting that bites hardest in containers: the default can land on a small or memory-backed path, so a large join fills it and the pod dies looking like an out-of-memory kill when it is actually a disk problem. In Docker, create the directory, mount a volume at it, and set DUCKDB_TEMP_DIRECTORY so every process in the image agrees.

What does preserve_insertion_order do, and should I set it to false?

It controls whether DuckDB guarantees that results come back in the order rows were read. Keeping the guarantee costs parallelism, because the engine has to reassemble ordering across threads. For aggregate queries over Parquet — where you are grouping and sorting anyway — you almost never care, so SET preserve_insertion_order = false lets DuckDB read more aggressively. Turn it off when you are scanning object storage. Leave it on if any part of your code relies on unordered SELECT results arriving in file order.

Does DuckDB spill to disk when a query does not fit in memory?

Yes. When a query exceeds memory_limit, DuckDB spills intermediate state to temp_directory rather than failing. Two things decide whether that is a pause or a disaster: the temp directory must be on real disk with real space, and it should be NVMe if you can choose. Spilling to an undersized or slow volume is the most common way a single node looks broken when it is only badly configured.

How much memory should I give DuckDB in a container?

Roughly 70–75% of the container limit. DuckDB's memory_limit governs its own buffer pool — not the Python process around it, the Arrow tables in flight, or the runtime. Set memory_limit equal to the container limit and the orchestrator kills the process before DuckDB ever decides to spill. If you fan out several reader processes on one box, divide the host's memory by the worker count first, then take 70% of that.

How many threads should DuckDB use in Kubernetes?

Set threads from your CPU limit, not from the host. DuckDB reads the node's core count, so on a 64-core machine with a 4-core limit it will spawn 64 threads and spend its life being throttled by the cgroup. SET threads = 4 on that pod. This is a Kubernetes-specific trap; on a dedicated box the default is usually right.

Why is my self-hosted DuckDB slow?

In the order I actually see it: too many small Parquet files, so per-file metadata overhead swamps the reading — aim for 100MB–1GB per file and compact anything a streaming writer flushed every few seconds. Then temp_directory on ephemeral disk. Then threads left at the host core count inside a CPU-limited container. Then partitioning on a column you never filter by, which adds directories and prunes nothing. Tuning flags will not rescue a bad file layout — fix the layout first.

Running DuckDB on your own infrastructure: a production setup

If you're asking how to run DuckDB on your own infrastructure for faster queries, you've already made the important decision. What's left is mostly avoiding four or five specific mistakes that make a single node look slower than it is.

This is the setup I actually run.

Start with the shape, not the settings

The layout matters more than any tuning flag:

object storage (or a local disk)
  └── warehouse/
      └── events/
          dt=2026-08-11/part-0.parquet
          dt=2026-08-12/part-0.parquet
          dt=2026-08-13/part-0.parquet

one writer process   →  produces new partitions
N reader processes   →  open read-only, query across partitions

One writer. Many readers. Immutable partitions. Everything else is detail.

If you take one thing from this: don't put a mutable .duckdb file at the centre of a multi-process system. DuckDB is single-writer, and the moment two processes want to write, you're fighting the design. Write Parquet, read Parquet.

The settings that actually matter

Most DuckDB tuning advice is noise. These four are not:

-- Leave real headroom. This is DuckDB's budget, not the container's.
SET memory_limit = '12GB';

-- Spilling must land on real disk with real space.
SET temp_directory = '/var/lib/duckdb/tmp';

-- Match the cores you actually have, not the ones the host advertises.
SET threads = 8;

-- Only if you're reading from object storage.
SET preserve_insertion_order = false;

memory_limit should sit meaningfully below your container limit — I use roughly 70–75%. DuckDB accounts for its own buffer pool, not for the Python process around it, the Arrow tables in flight, or the runtime. Set it to the container limit and the orchestrator kills you before DuckDB ever decides to spill.

temp_directory is the one that bites in containers. The default may point at a path on the container's ephemeral layer, which is often small and sometimes memory-backed. A large join then fills it and the pod dies looking like an OOM when it's really a disk problem. Mount a volume and point at it.

threads matters in Kubernetes specifically. DuckDB sees the host's core count, not your CPU limit. On a 64-core node with a 4-core limit, it will happily spawn 64 threads and spend its life being throttled. Set this from your actual limit.

preserve_insertion_order = false lets DuckDB parallelise reads more aggressively when you don't care about row order — which, for aggregate queries over Parquet, you usually don't.

Partition for how you query

This is where the real speed is, and no setting substitutes for it.

# Good — date-partitioned, prunes before reading
events/dt=2026-08-13/part-0.parquet

# Good — tenant then date, if you always filter by tenant
events/tenant_id=acme/dt=2026-08-13/part-0.parquet

# Bad — one enormous file, every query reads everything
events/all_events.parquet

# Also bad — 400,000 tiny files, metadata overhead dominates
events/dt=2026-08-13/part-{0..399999}.parquet

Aim for files in the 100MB–1GB range. Too large and you lose pruning granularity; too small and you pay per-file overhead that swamps the actual reading. The "many small files" problem is the single most common reason a self-hosted setup feels slow, and it usually arrives by accident from a streaming writer flushing every few seconds.

Partition on the column you filter by most — usually date, sometimes tenant. Partitioning on something you rarely filter by adds directories and buys nothing.

The read-only fan-out

To serve concurrent queries from one box, run several reader processes against the same files:

import duckdb

def make_reader() -> duckdb.DuckDBPyConnection:
    con = duckdb.connect(read_only=True)      # in-memory catalogue over Parquet
    con.execute("INSTALL httpfs; LOAD httpfs;")
    con.execute("SET memory_limit='3GB'")     # per worker, not per host
    con.execute("SET threads=2")
    return con

Note the per-worker budgets. Eight workers each believing they can use 12GB on a 16GB box is a bad afternoon. Divide the host's memory by the worker count, then leave headroom.

Because readers never take a write lock, this scales across processes with no coordination. Your web framework's worker model does the concurrency for you.

Containers: the short version

FROM python:3.12-slim
RUN pip install --no-cache-dir duckdb==1.1.3   # pin it
RUN mkdir -p /var/lib/duckdb/tmp
VOLUME /var/lib/duckdb/tmp                     # real disk for spilling
ENV DUCKDB_TEMP_DIRECTORY=/var/lib/duckdb/tmp

Pin the version. DuckDB's storage format is stable within 1.x, but a file written by a newer version isn't guaranteed readable by an older one. An unpinned version in a rebuilt image is a rollback you can't perform.

If you keep a .duckdb file at all, it needs a persistent volume — and it should still be rebuildable from Parquet. Treat it as a cache with a fast rebuild path, not as the system of record.

Hardware: buy RAM, then NVMe

For a single-node analytical box, in priority order:

RAM. More working set in memory, less spilling. The cheapest performance you can buy.
NVMe. When spilling does happen, it's the difference between a pause and a disaster. Also where your temp directory should live.
Cores. DuckDB parallelises well, but it's usually not the binding constraint.
Network, if reading from object storage — throughput to S3 becomes the ceiling on cold scans.

A single machine with a few hundred GB of RAM covers working sets that people routinely provision clusters for. That's the whole local-first argument in one sentence.

The five things that actually go wrong

In rough order of how often I've seen them:

Too many small Parquet files. Compact them. Nothing else you do will matter as much.
temp_directory on ephemeral or undersized disk. Looks like an OOM, is a disk problem.
threads left at the host's core count in a CPU-limited container. Constant throttling.
Two processes writing the same file. Redesign to partitioned writes; don't try to coordinate locks.
Unpinned DuckDB version. Works until the day a rebuild picks up a new minor and you can't roll back.

None of these are exotic. All of them are silent until they aren't.

A minimal production checklist

Parquet on object storage (or disk) is the source of truth; any .duckdb file is rebuildable.
Exactly one writer; all other connections read_only=True.
memory_limit ≈ 70% of the container limit; temp_directory on a real, sized volume.
threads set from the CPU limit, not the host.
Partition files sized 100MB–1GB, on the column you filter by.
DuckDB version pinned in the image.

That's the whole setup. It's short because a single node genuinely has less to go wrong than the distributed alternative — which is most of the point.

The full treatment, with runnable code, is my book Local-First Analytics. Related: is DuckDB safe for production, what DuckDB is actually good at in production, DuckDB as a SaaS warehouse, and when local-first runs out of road.

If you'd like this sized and set up against your actual workload, a Data Platform Audit is a week and a written roadmap you keep. The scoping call below is free.