DuckDB vs Polars Benchmarking

Benchmarking DuckDB against Polars on TPC-H, using real dbgen-generated data (no duplication tricks) at five fixed scale factors: SF=10, 50, 100, 200, 465 (~10GB to ~465GB on disk).

Built on ibis-bench, the real package behind the ibis-project reproduction post — bench h gen/bench h run below are its actual CLI, not something we wrote. Three things it doesn't cover, which we add on top:

• DuckDB memory/temp-directory tuning — its run CLI doesn't expose memory_limit/temp_directory, which caused an OOM at SF=50 (see note.md). bench-tuned is a thin wrapper around the same ibis-bench query/monitor code that adds those settings.
• Polars out-of-core execution — ibis-bench's own result-writing code always uses a plain in-memory collect() for Polars, even for polars-lazy (a deliberate workaround for an old Polars sink_parquet bug on their end). At SF=50 on a 25.7GB-RAM machine this thrashed swap and died instead of failing cleanly. bench-tuned uses collect(engine="streaming") instead — verified fixed, see note.md.
• A one-command full pipeline per scale factor (scripts/run.sh) — validates/generates data, runs the full 22-query benchmark on both engines, builds the report, and archives everything under a scale-labeled directory so results from different scale factors never overwrite each other.

Hardware: 25.7GB RAM, 10 cores (this project's dev machine). All five scale factors above have complete, archived results — see Results summary below for the headline findings, or note.md for the detailed history of fixes/decisions made along the way.

Setup

uv sync

pyproject.toml pins duckdb==1.5.5 / polars==1.43.2 and overrides ibis-bench's own (older) pins on those two packages via [tool.uv] override-dependencies — verified working (see note.md) before adopting.

Two CLIs are installed:

• bench — the real ibis-bench CLI (bench h gen, bench h run, ...)
• bench-tuned — our wrapper around bench h run that adds DuckDB --memory-limit/--temp-dir/--max-temp-directory-size

Generate data

Scale factor (SF) is the TPC-H convention for dataset size, but it's worth being precise about which size: SF≈GB refers to the raw/in-memory representation, not the compressed Parquet files we store on disk (which run noticeably smaller, ~0.35GB per SF unit measured here) or the fully materialized in-memory size (which runs larger than the on-disk Parquet size, ~4x in one measurement on lineitem at SF=50 — see note.md). Don't assume "SF=50 → 17GB on disk" means the query only ever touches 17GB of RAM.

An earlier version of this project used a file-duplication trick (duplicating lineitem's Parquet files to cheaply approximate very large sizes without paying for a full dbgen run) to get to ~300-500GB quickly. That approach is no longer used — duplicated primary keys inside lineitem made aggregate results TPC-H-incorrect, so results were only valid for raw scan/join throughput, not correctness. The project now generates real data at every scale via dbgen, at five fixed, already-validated scale factors:

SF	-n (partitions)	~on-disk size
10	1	~3.4GB
50	1	~17.6GB
100	1	~33GB
200	24	~70GB
465	55	~167GB

-n isn't arbitrary — each value keeps every dbgen generation step to ~8.4GB of raw data, which is the safety margin this project found necessary on a 25.7GB-RAM machine with no memory_limit on the generation connection (generation uses DuckDB's dbgen(sf=SF, children=n, step=i) internally, one step per partition; a too-low -n at large SF risks OOMing generation itself, the same failure mode bench-tuned exists to prevent at query time). -n must match between generation and querying — it just selects which tpch_data/parquet/sf=<N>/n=<N>/ directory to read from.

uv run bench h gen -s 10 -n 1
uv run bench h gen -s 50 -n 1
uv run bench h gen -s 100 -n 1
uv run bench h gen -s 200 -n 24
uv run bench h gen -s 465 -n 55

Generation is naturally idempotent — bench h gen skips regenerating if the target sf=/n= directory already exists — but scripts/run.sh (see below) goes further: it verifies every table actually has the expected number of Parquet files, all non-empty, before trusting existing data, and regenerates from scratch if that check fails (e.g. after an interrupted run).

Run benchmarks

The easy way: scripts/run.sh

./scripts/run.sh 10    # or 50, 100, 200, 465 - no other values accepted

One command per scale factor: checks/generates data (see above), runs the full 22-query benchmark on both ibis-duckdb-sql and polars-lazy with tuned DuckDB settings (--memory-limit 20GB --threads 4, sized for this project's 25.7GB-RAM machine — adjust in the script for a different machine), builds the report, and archives everything to bench_logs_v2_full_sf<N>/ and results_data_full_sf<N>/.

Safe to re-run, including on a scale factor that's already been run: it refuses to overwrite an existing archive (remove/rename it yourself first if you want to redo that scale) rather than silently clobbering prior results, and it wipes bench_logs_v2/results_data before starting so a stale, never-archived run can't silently mix its files into the new one.

The manual way

-s/-n must match the scale factor / partition count used when generating the data (they just select which tpch_data/parquet/... directory to read from) — they don't control how much data gets scanned; that's determined by whatever is actually on disk in that folder.

# plain ibis-bench, no DuckDB tuning
uv run bench h run ibis-duckdb-sql polars-lazy -s 1

# with DuckDB memory/temp-dir tuning + Polars streaming collect (needed once
# data gets large - see note.md)
uv run bench-tuned ibis-duckdb-sql polars-lazy -s 465 -n 55 \
  --memory-limit 20GB --threads 4 \
  --temp-dir /tmp/duckdb_spill_sf465 --max-temp-directory-size 465GB

bench-tuned also takes --polars-engine (default streaming) for polars/polars-lazy systems — Polars' out-of-core collect engine, used instead of ibis-bench's own plain in-memory collect(). Pass --polars-engine in-memory to reproduce ibis-bench's original behavior. Only applies to polars-lazy — the eager polars system has no lazy collect step to redirect, so it's still exposed to the same OOM risk at large scale.

Each query runs in its own subprocess. Even with --polars-engine streaming, at large scale (SF=465, ~167GB on disk) Polars can still get killed outright by the OS's OOM killer (SIGKILL) rather than raising a catchable Python exception — the whole process just vanishes mid-query, no traceback, no log line. Since ibis-bench (and our earlier wrapper) ran every query in one long-lived process, that took the entire benchmark run down silently — including everything queued after the dead query. bench-tuned now supervises each query from a parent process: a signal-based death (SIGKILL=9 almost always means OOM) or a hang (--query-timeout <seconds>, unlimited by default) gets logged with an explicit, actionable message and recorded as a normal failure row, and the run continues to the next query instead of hanging or dying with no explanation.

Caveat on --polars-engine streaming (the default here): a Polars maintainer, on the exact bug thread ibis-bench's own code cites (pola-rs/polars#16694), was explicit about this:

sink_parquet uses the streaming engine, whereas collect().write_parquet() uses the in-memory engine. We are completely redesigning the streaming engine and will likely not improve the performance of the current one (it will be discontinued).

We don't recommend using the streaming engine at the moment (if it works for you great), but we are not happy with it. If you use it for benchmarking, I think you should make clear that it is polars-streaming you are benchmarking. — ritchie46, Polars maintainer

Without --polars-engine streaming, polars-lazy can't complete at all at SF=50 on this machine (thrashes swap and dies — see note.md), so it's the default here out of necessity. But per the maintainer's own guidance: any reported Polars numbers from this default config should be labeled "polars-streaming," not treated as representative of Polars' normal (in-memory) execution path — it's a distinct, less-optimized engine they plan to discontinue and rebuild.

Each system string is parsed by splitting on -, and picks both the engine and how it's queried:

system	engine	how it's queried
ibis-duckdb	DuckDB	Ibis dataframe API, compiled down to DuckDB SQL
ibis-duckdb-sql	DuckDB	raw SQL text sent straight to DuckDB via Ibis, bypassing its compiler
ibis-datafusion / ibis-datafusion-sql	DataFusion	same two variants, against DataFusion instead
polars	Polars	eager DataFrame, no Ibis involved
polars-lazy	Polars	lazy LazyFrame (deferred/optimized execution), no Ibis involved

Pass as many as you want in one command, e.g. bench-tuned ibis-duckdb-sql ibis-duckdb polars polars-lazy -s 50 ... runs all four. -q <n> to run specific query numbers, -e <n> to exclude.

Results are written as JSON, one file per query/system/scale-factor run, under bench_logs_v2/raw_json/. bench-tuned also writes a result row on failure (success: false, error: "...") — ibis-bench's own run writes nothing at all when a query raises, which makes "failed" and "never attempted" indistinguishable later. Only failures that go through bench-tuned get this; failures from plain bench h run still vanish.

See queries/README.md for what each of the 22 queries tests.

View results

uv run bench-report -s 50

Builds a single self-contained HTML file (bench_logs_v2/report.html by default — just open it in a browser) from every JSON file in bench_logs_v2/raw_json/, with:

• total duration of successful queries at a chosen scale factor, one bar per engine — the "how long did the whole suite take" headline number. Note: a lower total can mean fewer queries completed, not necessarily faster ones — cross-check against the success-count chart below.
• query success count by system, against a reference line at 22/22
• per-query median duration at a chosen scale factor, log scale, with failed queries marked explicitly (✕) rather than silently omitted
• relative difference in duration, one diverging bar per query — (loser − winner) / winner as a percentage (same formula as Coiled's Dask-vs-Spark TPC-H comparison), drawn as a signed bar: up and colored for whichever engine won that query, down and colored for the other engine when it wins instead. Y-axis ticks are mirrored to read as plain percentages on both sides of zero (the sign only encodes direction, not a negative quantity). Only plots queries where both engines succeeded — a relative difference needs two real numbers to compare.
• table size on disk at that scale factor, descending (reads the actual Parquet files under --data-dir, default tpch_data/parquet — not the JSON logs; pass --data-dir '' to skip this chart)
• peak physical RAM used per query (GB, log scale) — automatically recorded by bench-tuned for every query, success or failure, via a background sampler around each query's subprocess (0.1s interval, psutil, tracking the process's own memory footprint — not swap; see note.md). Deliberately includes failed queries (red/hatched bars), unlike the timing charts — this is exactly the case where "how close to the wall did it get" matters most for root-causing an OOM. Each bar also carries a downward whisker down to that query's average RAM usage over its own execution (not across runs) — a short whisker means sustained heavy usage the whole time, a long one means a brief spike; same peak, different profile, only visible once you have both numbers. (Only appears once avg_rss_mb is present in the results — older result files predating this feature just render without the whisker.) (A peak_swap_mb field is also recorded in the raw JSON — system-wide OS swap, not the same thing as physical RAM used or as an engine's own spill-to-disk volume — but it's not currently charted; see note.md for the scope/attribution caveats that led to dropping it from the report.)
• relative difference in peak RAM used, same diverging-bar formula/style as the duration variant (legend reads "uses less memory" instead of "is faster"), but includes queries where either engine failed (hatched bars) since peak RAM is recorded on failure too — unlike the duration variant, which needs both to have actually finished.

(A "total time by scale factor," "total time by query," and "run-to-run variance" chart each existed at one point and were removed — with one run per query at one scale factor, which is how this project actually benchmarks, per-query sum/median/variance collapse to the same or a degenerate single number, so they were three redundant views of the same data. "Total duration" above is a different aggregation — suite-wide, one number per engine, not per query — which is why it's back while the other two stay out. Worth re-adding "by scale factor"/variance if multi-run/multi-scale-factor benchmarking ever happens.)

Series are labeled by how they were actually run, not just the raw system name — a polars-lazy row produced with --polars-engine streaming (the bench-tuned default) is labeled polars-streaming in every chart, per the maintainer caveat above. -s <scale_factor> picks which scale factor the per-query/variance charts focus on (defaults to the largest one present).

Results summary

Full 22-query runs completed at all five scale factors (both engines, archived under bench_logs_v2_full_sf<N>/):

SF	DuckDB	Polars (streaming)
10	22/22	22/22
50	22/22	22/22
100	22/22	22/22
200	22/22	22/22
465	22/22	21/22 (Q5 SIGKILL/OOM)

DuckDB completed every query at every scale tested. Polars' streaming engine only starts failing outright once you cross into SF=465 territory — but failure count alone understates the gap. Even where Polars succeeds, it gets progressively slower relative to DuckDB as scale increases, and the slowdown isn't uniform across queries — it's concentrated in join-heavy queries that chain multiple joins through lineitem (this dataset's dominant table), especially ones that effectively self-join it (e.g. Q21: ~2x slower than DuckDB at SF=10, ~60x slower at SF=465). Root cause traced to a known, currently-unfixed upstream Polars issue (pola-rs/polars#24206) — the streaming engine's memory accumulates across chained joins instead of being fully released between stages, confirmed directly by sampling RSS over time during a real run (see note.md for the full investigation, including a structural DuckDB-vs-Polars physical-plan comparison for Q21).

This is specific to the memory-constrained regime this project deliberately tests (data significantly exceeding the 25.7GB-RAM test machine) — Polars' own published PDS-H benchmark shows streaming ≈ DuckDB, but that's run on 192GB-RAM hardware with a dataset ~19x smaller than available RAM, nowhere near the regime where this accumulation bug bites.

Notes / open questions

• bench-tuned's memory/temp-dir flags only apply to DuckDB-backed systems (ibis-duckdb*); Polars has no equivalent connection-level setting, so those flags are logged as ignored when used with polars/polars-lazy.
• bench-report has been validated against real multi-scale-factor result sets (SF=10 through SF=465, see Results summary above), not just synthetic data.