DuckDB Under the Microscope

DuckDB Under the Microscope: An Experimental Evaluation of Query Performance and Analytical Workloads

Rajesh Meher
Data Engineering & Architecture, Independent Researcher, San Antonio, Texas, USA

Abstract

DuckDB is an embedded analytical database designed to execute SQL workloads within an application process. Its in-process architecture and support for analytical execution make it a candidate for local analytics, exploratory data analysis, data transformation, and querying columnar files. However, performance depends on data volume, selectivity, join cardinality, memory limits, storage format, cache state, and engine configuration. This paper defines a reproducible experimental evaluation focused on query latency, scaling behavior, memory consumption, and workload type. The proposed benchmark covers scans, selective filters, aggregations, joins, sorting, and direct Parquet queries across multiple data scales. DuckDB is compared first with PostgreSQL on a single host; a distributed-engine extension is optional. The methodology specifies controlled conditions, repeated trials, correctness checks, reporting statistics, and validity safeguards. No performance measurements are asserted in this draft: results tables are intentionally left for values collected by running the protocol. This distinction is essential to avoid unsupported speedup claims and make the eventual paper auditable and reproducible.

Keywords—DuckDB; analytical database; OLAP; query performance; benchmark methodology; Parquet; PostgreSQL; reproducibility.

1. Introduction

Analytical workloads increasingly run outside centralized data warehouses. Engineers query local extracts, object-store files, lakehouse tables, notebooks, transformation pipelines, and application data. These workflows create demand for database systems that provide expressive SQL without routing every analytical task through a remote database service.

DuckDB addresses this use case through an embedded analytical database model. Rather than requiring a separate database server for typical single-process workflows, it can execute in the same process as the application. The original system paper describes DuckDB as an embeddable database designed for analytical SQL [1]. Work on embedded analytics further discusses the integration challenges that arise when data management is brought closer to data-science workflows [2]. These design choices motivate an empirical question: under which workload conditions does DuckDB execute analytical queries efficiently, and how does behavior change as the workload grows?

A useful performance study should not repeat vendor-published results or report a single query duration. Database comparisons are sensitive to benchmark construction, data loading, indexing, caching, configuration, query equivalence, and system familiarity. Raasveldt et al. identify common pitfalls that can produce misleading comparisons even when measurements appear objective [3]. This study therefore treats experimental control and transparent reporting as core contributions.

The paper defines a workload matrix that separates common analytical operators, specifies a repeatable procedure for collecting latency and resource metrics, and provides a reporting structure to connect observed performance with data scale, query shape, and execution plans.

2. Research Questions and Scope

RQ1: How do DuckDB query latency and resource consumption change as input data scales across scans, aggregations, joins, and sorting?

RQ2: How strongly does query shape affect performance, particularly selectivity, grouping cardinality, join multiplicity, and sort volume?

RQ3: Under a documented single-host setup and equivalent query semantics, how does DuckDB compare with PostgreSQL for analytical workloads, including Parquet queries where comparable access paths are available?

The primary comparison is DuckDB versus PostgreSQL on the same host. This is narrower than comparing embedded, server-based, and distributed engines in one experiment. PostgreSQL is a useful relational baseline, but its general-purpose design differs from DuckDB's analytical focus. Results should be interpreted as comparisons of tested configurations, not universal rankings. Spark SQL or Trino can be added in a separate extension, where startup, scheduling, shuffle, networking, and cluster resources are documented.

3. Background and Related Work

3.1 Embedded analytical execution

Raasveldt and Mühleisen introduced DuckDB as an embeddable analytical database at SIGMOD 2019 [1]. Its embedded model reduces the operational boundary between application code and query execution. Their later CIDR work frames embedded analytics as a response to workflows that combine independent tools for storage and processing [2]. These papers establish the system context but do not substitute for a workload-specific evaluation on the hardware and versions used here.

3.2 Profiling and workload behavior

DuckDB provides EXPLAIN and EXPLAIN ANALYZE for inspecting physical plans and runtime behavior [4]. Its performance guidance identifies grouping, joining, sorting, and windowing as blocking operators and discusses larger-than-memory execution limitations [5]. This motivates measuring both elapsed time and resource use.

3.3 Fair benchmarking

Fair comparison requires equivalent logical work, explicit configuration, repeated trials, and careful treatment of cache state. A benchmark can favor one engine through data preparation, query translation, indexing, or tuning choices that are not disclosed [3]. This protocol records versions, machine specifications, thread counts, memory limits, storage format, loading time, query text, and run order.

3.4 Study positioning

This study is a practical workload evaluation rather than a new database architecture. Its potential contribution is a transparent comparison across operator categories and data scales, including direct Parquet querying. Any novelty claim should be finalized only after a focused literature review establishes which benchmark combinations and versions have already been studied.

4. Experimental Design

4.1 Systems under test

Run DuckDB and PostgreSQL on the same physical or virtual host for the primary comparison. Record OS, CPU model, physical core count, RAM, storage type, filesystem, and available disk space. If internal settings cannot be aligned, document the differences rather than implying perfect equivalence.

4.2 Dataset design

Use deterministic synthetic data with a fixed seed so row count, value distribution, and key cardinality can be controlled. An initial scale set is 1 million, 10 million, and 50 million fact rows, subject to hardware limits. If the largest scale cannot be run reliably, reduce it and document why. Use a fact table with an integer identifier, date or timestamp, categorical dimension key, numeric measure, and additional attributes; generate dimension tables with controlled key uniqueness and cardinality.

For file-based tests, export the same logical data to Parquet and record compression codec, row-group size, column types, file count, and total bytes. Do not compare a heavily indexed PostgreSQL table with unindexed DuckDB data without explicitly treating that as a separate, application-oriented configuration.

4.3 Workload matrix

Define canonical logical queries. Engine-specific SQL differences are acceptable only when needed to preserve equivalent semantics and must be documented. Exclude client-side serialization from query timing unless measuring end-to-end application latency. Prefer scalar or small aggregate outputs to reduce client-transfer effects.

5. Measurement Protocol

5.1 Controlled environment

Record operating system, CPU, RAM, storage, filesystem, power mode, and whether the test uses a container or virtual machine.

Record exact engine versions and configuration. Use the same thread limit where meaningful and document engine-specific settings.

Use deterministic dataset generation with fixed seeds; preserve the generation script and parameters.

Separate data loading and index creation from query execution; report setup time independently.

Close competing workloads and record environmental interruptions.

5.2 Cache state

Cache state can materially influence elapsed time. Report warm-cache and cold-cache measurements separately where cold-cache conditions can be established consistently. Define warm runs as execution after a controlled warm-up. A cold-cache run requires a documented procedure that clears or bypasses relevant caches; if the environment does not permit a trustworthy reset, do not label a run cold-cache. Never combine both states into one average.

5.3 Repetition and run order

For each engine, dataset scale, query, and cache condition, perform at least five measured runs after a warm-up; ten measured runs are recommended where execution time permits. Exclude the warm-up from summary statistics. Rotate or randomize the order of engine/query combinations to reduce systematic bias from thermal state or background load. Preserve raw run-level data.

5.4 Metrics

Report median and interquartile range (IQR), not only the arithmetic mean. For paired comparisons, calculate the per-query latency ratio T_PostgreSQL / T_DuckDB. A value above 1 means lower elapsed time for DuckDB in that specific configuration; below 1 means lower elapsed time for PostgreSQL. Do not aggregate ratios across unrelated queries without stating the method.

5.5 Correctness validation

Validate row counts, aggregate values, join output counts, null behavior, and sorting/tie semantics. Use deterministic ordering only when order is part of the query contract. Floating-point aggregates can differ due to evaluation order; define a numeric tolerance before testing. Exclude or separately report queries whose semantics cannot be shown equivalent.

6. Hypotheses

H1: DuckDB's relative performance, if present, will vary by operator category rather than remain constant across all queries.

H2: Increasing input size will increase elapsed time, but scaling curves will differ among scans, aggregations, joins, and sorting.

H3: Direct Parquet queries will have a distinct performance profile from queries over materialized database tables because metadata, projection, predicate pushdown, and data layout affect the work performed.

H4: High-cardinality joins and blocking operators will increase memory pressure and may cause nonlinear changes at larger scales.

These are hypotheses, not findings. They should be retained, refined, or rejected based on measurements rather than rewritten after results are observed.

Interpretation: these are results reported by an independent published study, not measurements from the experiments proposed in this manuscript. The study used a specific clinical-data pipeline and workload, so its results motivate the present research question but cannot be generalized to every DuckDB query or hardware configuration.

A recent empirical study by Lee, Hwang, and Lee (2026) evaluated a clinical dataset containing approximately 13.76 million rows and reported a 90.2-second total for DuckDB versus 2,546.4 seconds for PostgreSQL across its scenario-based SQL workload. The ratio is approximately 28.2× (2,546.4 ÷ 90.2), corresponding to about a 96.5% reduction in elapsed time relative to that PostgreSQL total. The same study reported DuckDB/PostgreSQL CPU utilization of 72.1%/39.6%, memory use of 13.7/14.8 GB, and read throughput of 554.7/365.2 MB/s. Separately, it reported disk-access time of 940.2 seconds for CSV versus 44.2 seconds for Parquet, a reduction of approximately 95.3%. These figures are included as contextual evidence from the literature; they must not be represented as results generated by the author of this manuscript.

The cited study is domain-specific and its aggregate totals depend on its query mix, environment, storage layout, and configuration. A single aggregate result does not establish that DuckDB is 28× faster for every query, nor does it replace a benchmark with fully documented hardware and workload details. The present study will test whether similar patterns appear across a more explicitly separated matrix of scans, filters, group-by operations, joins, sorting, and Parquet queries.

7. Results from a Published Benchmark

Because this manuscript does not include newly executed benchmark runs, this section reports quantitative results from a peer-reviewed, published study by Lee, Hwang, and Lee (2026) as contextual evidence. The values below are attributed to that study and must not be interpreted as original measurements collected by the present author. The source evaluated a clinical-data pipeline with approximately 13.76 million rows and compared scenario-based analytical SQL workloads in DuckDB and PostgreSQL.

In that study, DuckDB's reported wall-clock time was 90.2 seconds compared with 2,546.4 seconds for PostgreSQL. The ratio is approximately 28.2, and the reduction in elapsed time is approximately 96.5% relative to the PostgreSQL total. These results apply to the specific workload mix, storage design, software versions, and execution environment used in the published study; they should not be generalized to every query or deployment.

7.1 Environment Summary

7.2 Latency Results

7.3 Memory and Scaling

Plot median latency against row count and peak memory by query type. Explain whether observed changes align with data volume, plan changes, spilling, or resource limits. Any explanation based on query plans should cite archived plan evidence.

The reported Parquet-versus-CSV disk-access reduction is approximately 95.3% (940.2 seconds to 44.2 seconds). This measures a storage-format change within the published clinical pipeline and should not be confused with the DuckDB-versus-PostgreSQL comparison. The present paper's contribution should be framed as an analysis and synthesis of these published findings unless a separate reproducible benchmark is conducted.

The published aggregate figures indicate lower elapsed time and memory consumption, and higher reported throughput, for DuckDB in that specific workload. Because hardware, software versions, and query-level results are not fully reproduced here, these findings are context-specific. Query-level performance and scaling conclusions for S1–S3 remain undetermined without running the proposed benchmark.

8. Discussion

The final discussion should translate measured results into bounded guidance. If DuckDB performs well on local scans and aggregations in the tested setup, that supports considering it for local analytical processing; it does not establish suitability for every concurrent multi-user or distributed workload. If PostgreSQL performs competitively on a query class, describe the workload and configuration instead of treating it as an anomaly.

Distinguish logical work, physical execution choices, and environmental effects. Query plans can help explain differences, but an operator's presence alongside a longer runtime does not prove causation. Treat direct Parquet queries separately: they may avoid loading and read selected columns, but measure a file-querying workflow rather than necessarily identical database-table execution.

9. Threats to Validity

Internal validity: cache state, background processes, thermal throttling, run order, and measurement overhead may affect timing.

Construct validity: elapsed time alone does not capture concurrency, operational overhead, update behavior, fault tolerance, or total cost of ownership.

Comparison validity: engines have different architectures and tuning options; equivalent SQL does not guarantee equivalent physical work.

External validity: synthetic data may not represent production skew, null rates, distributions, and schema complexity.

Version validity: results apply to the tested builds and may change after upgrades.

Memory validity: resident-memory measurements vary by platform; document the tool and limitations.

Researcher degrees of freedom: query rewrites, indexes, thread counts, and exclusions can affect results; record decisions and retain raw logs.

Mitigations include publishing query definitions and scripts, preserving raw measurements, predefining exclusion rules, reporting all tested query classes, and separating primary results from exploratory tuning.

10. Reproducibility and Artifact Plan

README with prerequisites, installation steps, versions, and execution instructions.

Dataset-generation script with fixed seed and scale parameters.

Canonical SQL files and documented system-specific variants.

Engine configuration and data-loading scripts.

Benchmark runner recording run ID, system/version, query ID, scale, cache state, elapsed time, status, and result checksum.

Raw CSV/JSON logs, summary script/notebook, figures, and archived query plans.

Limitations file documenting unavailable cold-cache controls, unsupported metrics, and protocol deviations.

Version-control the artifact and tag the exact revision used for reported results. For generated data, document the generator instead of relying solely on a large binary upload.

11. Conclusion

This manuscript defines a controlled evaluation framework for DuckDB query performance across analytical workload types and data scales. The design emphasizes repeated trials, query-result validation, transparent configuration, explicit cache treatment, and reporting of latency and resource consumption. It distinguishes direct Parquet querying from database-table execution and recommends treating distributed-engine comparisons as a separate experiment.

References

[1] M. Raasveldt and H. Mühleisen, “DuckDB: An Embeddable Analytical Database,” Proceedings of the 2019 ACM SIGMOD International Conference on Management of Data, 2019, pp. 1981–1984. doi: 10.1145/3299869.3320212.

[2] M. Raasveldt and H. Mühleisen, “Data Management for Data Science: Towards Embedded Analytics,” Conference on Innovative Data Systems Research (CIDR), 2020.

[3] M. Raasveldt, P. T. Holanda, T. Gubner, and H. Mühleisen, “Fair Benchmarking Considered Difficult: Common Pitfalls in Database Performance Testing,” Proceedings of the Workshop on Testing Database Systems (DBTest ’18), 2018. doi: 10.1145/3209950.3209955.

[4] DuckDB Documentation, “Profiling.” https://duckdb.org/docs/current/dev/profiling (accessed October 29, 2026).

[5] DuckDB Documentation, “Tuning Workloads.” https://duckdb.org/docs/current/guides/performance/how_to_tune_workloads (accessed October 29, 2026).

[6] PostgreSQL Global Development Group, “Using EXPLAIN.” https://www.postgresql.org/docs/current/using-explain.html (accessed October 29, 2026).

[7] Apache Spark, “Performance Tuning.” https://spark.apache.org/docs/latest/sql-performance-tuning.html (accessed October 29, 2026).
[8] J. Lee, S. Hwang, and K. H. Lee, “Scalable and Privacy-Conscious End-to-End Processing of Large-Scale Clinical Data for Precision Medicine: Empirical Evaluation Study,” JMIR Medical Informatics, vol. 14, 2026, e83487. doi: 10.2196/83487.

Appendix A. Minimum Query Semantics

The artifact must include exact SQL, data types, timestamp boundaries, null semantics, and parameters. Q6 is a direct-file workflow; compare it with PostgreSQL only if access path and timing boundary are explicit.

Appendix B. Suggested Run Log Schema