Benchmark DuckDB Without Benchmarking Your Cache

Version scope: DuckDB 2.0 documentation.

A query that finishes faster on its second run has not necessarily improved. It may be benefiting from filesystem cache, cached metadata, a warm connection or remote data already downloaded. A useful benchmark makes those conditions visible instead of hiding them in one elapsed-time number.

The question comes first. Are you comparing two SQL formulations, deciding whether to import Parquet into a native table, or measuring the latency users see through an application? Each requires a different experiment. Avoid collecting numbers until the decision and acceptance criteria are written down.

Freeze the workload and the result contract

Choose immutable synthetic or approved inputs. Record their exact file list, digests, row counts and distributions. Include key skew and null rates when they affect the query. Save the engine version, extension versions, SQL, configuration, hardware and storage environment.

DuckDB's tuning guide discusses connection reuse and workload-dependent performance considerations. Those are reasons to control the environment, not reasons to apply a universal tuning recipe. DuckDB workload tuning

Define correctness before speed. For an aggregate report, compare the grouped keys and values. For a top-k query, define how ties are handled. For a query without ordering, compare results as a multiset rather than treating arbitrary output order as a correctness difference.

Name each cache condition honestly

Use labels that describe what you actually controlled:

• A fresh process means the application process was restarted.
• A reused connection means the same connection served repeated queries.
• A warm repeated run means the workload was executed after a prior run.
• A controlled cold-cache run requires a documented method for resetting the relevant caches.

Restarting the process does not necessarily clear the operating system's filesystem cache. Reopening a file does not guarantee a remote object store or network proxy serves identical conditions. If you cannot control a layer, disclose it rather than calling the run cold.

Never flush shared host caches or interfere with a production machine merely to create a benchmark. Use an isolated test environment and obtain any required operational authorization.

Measure the work the user actually waits for

Record query execution separately from result fetching, serialization and display. If the application needs all result rows, an experiment that only times the first available batch understates the user-visible work. If the application only needs a summary, forcing a huge export changes the question.

Run repeated trials and retain every result, including failures. Interleave alternatives to reduce bias from a machine that warms up or becomes busier over time. Report the number of trials and an appropriate spread, not only the fastest run. For unstable remote workloads, increase repetitions or state that the variance prevents a strong conclusion.

The raw record can be simple: trial identifier, variant, input digest, process state, elapsed time, peak memory, output row count, result-check status and error. Record measurement units in column names.

Decide what the evidence supports

If variant B is faster for one dataset on one machine, say exactly that. Do not turn a local result into a universal claim about DuckDB, PostgreSQL or any other engine. A different concurrency level, distribution or storage path may reverse the outcome.

This article provides a benchmark protocol and contains no measured performance results. Before publishing numbers of your own, attach the runnable harness and raw output and have a reviewer reproduce the comparison.

A trustworthy benchmark gives another engineer enough information to disagree productively. The strongest result is not the largest speedup; it is a comparison whose conditions and correctness can be independently checked.

Cache labels describe observed controls. A fresh process alone is not proof of cold storage caches.