Methodology
Fairness rules
- Each system is the project's latest release as shipped, with its own bundled DuckDB. The DuckDB version is shown next to results.
- One system runs at a time. Each database container gets the same cores and memory cap, the load generator its own cores, and the page cache is dropped before cold runs.
- Engine settings are sized to the container, not left at defaults. Every non-default setting is listed below with a reason, and correctness-relevant knobs are set explicitly.
- The same queries run everywhere with only mechanical dialect translation (diffs below). A query a system cannot run is recorded as a failure with the error, never rewritten by hand.
- Every result is checked against a reference; a wrong answer is a failure whatever its speed and is never drawn as a bar.
- Timeouts, errors, crashes and n/a cells are shown with their reasons. Nothing is dropped.
- Overhead against plain DuckDB compares only equal DuckDB versions. The headline
geomean_commonuses the queries every integration completed; each system's owngeomeanand how many queries went into it are shown beside it, because a system that times out on slow queries would otherwise score better. - VillageSQL has no DuckDB table type: it runs DuckDB SQL over Parquet files. It is listed with every other system, its table-based measures are n/a with that reason, and its numbers are in the Parquet entry points section, next to the same entry point of plain DuckDB, pg_duckdb and MariaDB on the same files. The two workloads are never drawn on one chart.
Limits and run parameters
| Database CPU set | 0-15 |
|---|---|
| Runner CPU set | 16-23 |
| Container memory | 64 GB |
| Per-query timeout | 900 s |
| Warm runs per query | 3 |
| Concurrent streams | 1, 4, 8, 16, 32 |
| Scale factors | 1, 10, 100, 300 |
Campaigns
| File | SF | Limits | Timeout | Measures | Systems |
|---|---|---|---|---|---|
| campaigns/gate-sf10.yaml | 10 | 0-15 / 16-23 / 64 GB | 900 | M1, M2, M3, M4, M5, M8 | innodb, postgres, duckdb-1.5.5, duckdb-2.0.0, duckdb-1.4.3, duckdb-1.5.3, duckdb-1.5.2-dev736, alisql, mariadb, percona, pg_duckdb, pg_ducklake |
| campaigns/smoke-sf1.yaml | 1 | 0-7 / 8-11 / 16 GB | 900 | M1, M2, M3, M4, M5, M8 | innodb, postgres, duckdb-1.5.5, duckdb-1.4.3, alisql, mariadb, percona, pg_duckdb, pg_ducklake |
| campaigns/talk-sf100.yaml | 100 | 0-15 / 16-23 / 64 GB | 900 | M1, M2, M3, M4, M5, M8 | duckdb-1.5.5, duckdb-1.4.3, duckdb-2.0.0, duckdb-1.5.3, duckdb-1.5.2-dev736, innodb, postgres, alisql, mariadb, percona, percona-duckdb-1.5.5, pg_duckdb, pg_ducklake |
Correctness tolerances
Results are compared order-independently. Integer columns must match exactly; other numeric cells match when
abs(a-b) <= max(abs_tol, rel_tol * max(|a|, |b|)) with
rel_tol = 1e-06 and abs_tol = 0.0001. NULL equals only NULL, CHAR padding is ignored.
Reference: InnoDB and PostgreSQL at SF1 and SF10, plain DuckDB at the newest version beyond, cross-checked against an older version.
Host
| cpus | 80 |
|---|---|
| docker_version | 29.1.3 |
| kernel | 6.8.0-60-generic |
| mem_gb | 187 |
Each record also stores the system version, image digest, bundled DuckDB version, config file and run date.
Every non-default setting
Rendered with the talk limits (64 GB, 16 cores). Comments in the config files give the reason.
AliSQL
| Setting | Value | Reason | File |
|---|---|---|---|
skip-log-bin | Binary logging is off on every MySQL-family server (MySQL, Percona and AliSQL enable it by default, MariaDB does not): replication is not under test and the default differs between families. | systems/alisql/my.cnf | |
duckdb_mode | ON | The engine is off unless the server is started with duckdb_mode=ON (static, default NONE). | systems/alisql/my.cnf |
duckdb_memory_limit | 48G | Caps DuckDB (the engine that runs the queries) at 75% of the container, the share the InnoDB baseline gives its buffer pool; the rest covers the server, connections and the OS. The engine default 0 means about 80% of physical RAM of the host, not of the container cgroup. | systems/alisql/my.cnf |
duckdb_threads | 16 | One DuckDB thread per core pinned to the database container (default 0 = auto). | systems/alisql/my.cnf |
duckdb_temp_directory | /var/lib/mysql/duckdb_tmp | Explicit spill location on the datadir volume (created by the image entrypoint), the same spill-location rule as every engine. duckdb_max_temp_directory_size stays at its default 0, which the variable documentation defines as 90% of the free disk, so spilling is not capped. | systems/alisql/my.cnf |
duckdb_use_double_for_decimal | OFF | Exact TPC-H DECIMAL results (set explicitly, disclosed). The default ON maps DECIMAL with precision > 38 to DOUBLE; the benchmark schema has none, but MariaDB gets the same setting. | systems/alisql/my.cnf |
duckdb_sql_normalization | OFF | Queries reach DuckDB exactly as written, with no MySQL-to-DuckDB rewriting (the default, set explicitly and disclosed): a query the engine rejects is recorded as an error. | systems/alisql/my.cnf |
duckdb_require_primary_key | ON | Every DuckDB table must have a primary key (the default, set explicitly); the schema declares them. | systems/alisql/my.cnf |
innodb_buffer_pool_size | 4G | The InnoDB side only holds the catalog and row-store staging (the load_convert path); the data lives in DuckDB, so a large pool would only take memory from DuckDB. | systems/alisql/my.cnf |
innodb_parallel_read_threads | 16 | Same as the InnoDB baseline. Only COUNT(*) without WHERE and CHECK TABLE use parallel read threads (ordinary scans stay single-threaded); set to every core so those two are not held back. | systems/alisql/my.cnf |
innodb_redo_log_capacity | 8G | A redo log of mem_gb/8 keeps checkpoints from throttling the row-store staging load; same capacity as the InnoDB baseline. | systems/alisql/my.cnf |
join_buffer_size | 256M | Per-join memory comparable to Postgres work_mem=256MB, same as the InnoDB baseline. | systems/alisql/my.cnf |
sort_buffer_size | 256M | Per-sort memory comparable to Postgres work_mem=256MB, same as the InnoDB baseline. | systems/alisql/my.cnf |
tmp_table_size | 4G | Internal temporary tables stay in memory up to mem_gb/16, same as the InnoDB baseline. | systems/alisql/my.cnf |
max_heap_table_size | 4G | Same cap for MEMORY-engine temporary tables, which use the smaller of the two sizes. | systems/alisql/my.cnf |
temptable_max_ram | 4G | RAM the TempTable engine may use before spilling, equal to the caps above. | systems/alisql/my.cnf |
secure_file_priv | /data | Server-side LOAD DATA INFILE may read only the read-only dataset mount. | systems/alisql/my.cnf |
local_infile | OFF | Bulk load is server-side from /data; client-side LOCAL loads stay disabled. | systems/alisql/my.cnf |
max_connections | 200 | Headroom for the 32-stream throughput run plus probes and cancel connections. | systems/alisql/my.cnf |
MariaDB
| Setting | Value | Reason | File |
|---|---|---|---|
skip-log-bin | Binary logging is off on every MySQL-family server (MySQL, Percona and AliSQL enable it by default; MariaDB is already off, set explicitly): replication is not under test and the default differs between families. | systems/mariadb/duckdb.cnf | |
plugin-load-add | ha_duckdb.so | The DuckDB engine ships as a plugin library, not built in. | systems/mariadb/duckdb.cnf |
plugin-maturity | gamma | The engine is gamma maturity; the server refuses to load it under the default 'stable'. | systems/mariadb/duckdb.cnf |
duckdb_memory_limit | 48G | Caps DuckDB (the engine that runs the queries) at 75% of the container, the share the InnoDB baseline gives its buffer pool; the rest covers the server, connections and the OS. | systems/mariadb/duckdb.cnf |
duckdb_max_threads | 16 | One DuckDB thread per core pinned to the database container. | systems/mariadb/duckdb.cnf |
duckdb_temp_directory | /var/lib/mysql/duckdb_tmp | Explicit spill location on the datadir volume (created by the image entrypoint), the same spill-location rule as every engine. | systems/mariadb/duckdb.cnf |
duckdb_allow_run_in_duckdb | ON | run_in_duckdb() runs DuckDB SQL inside the server and is refused by default. It is MariaDB's documented bulk load (INSERT ... SELECT FROM read_parquet(), storage/duckdb/tpch/04_load.sh), the engine's only way to run DuckDB's CHECKPOINT, and the Track B entry point. | systems/mariadb/duckdb.cnf |
duckdb_use_double_for_decimal | OFF | Exact TPC-H DECIMAL results: the default (ON) maps DECIMAL to DOUBLE. AliSQL gets the same setting. | systems/mariadb/duckdb.cnf |
innodb_buffer_pool_size | 4G | The InnoDB side only holds the catalog and the row-store staging of the conversion load (load_convert, which stages the whole dataset in InnoDB before ALTER TABLE ... ENGINE=DuckDB); the data lives in DuckDB, so a larger pool would only take memory from DuckDB. The InnoDB baseline gets 0.75 * mem_gb. | systems/mariadb/duckdb.cnf |
innodb_log_file_size | 8G | A redo log of mem_gb/8 keeps checkpoints from throttling the row-store staging load; same capacity as the InnoDB baseline (MariaDB calls it innodb_log_file_size). | systems/mariadb/duckdb.cnf |
join_buffer_size | 256M | Per-join memory comparable to Postgres work_mem=256MB, same as the InnoDB baseline. | systems/mariadb/duckdb.cnf |
sort_buffer_size | 256M | Per-sort memory comparable to Postgres work_mem=256MB, same as the InnoDB baseline. | systems/mariadb/duckdb.cnf |
tmp_table_size | 4G | Internal temporary tables stay in memory up to mem_gb/16, same as the InnoDB baseline. | systems/mariadb/duckdb.cnf |
max_heap_table_size | 4G | Same cap for MEMORY-engine temporary tables, which use the smaller of the two sizes. | systems/mariadb/duckdb.cnf |
query_cache_type | OFF | MySQL 9.7 has no query cache, so MariaDB's is switched off for parity (it is off by default in recent releases, set explicitly so the result does not depend on that). | systems/mariadb/duckdb.cnf |
query_cache_size | 0 | Zero-size query cache, same reason. | systems/mariadb/duckdb.cnf |
secure_file_priv | /data | Server-side LOAD DATA INFILE may read only the read-only dataset mount. | systems/mariadb/duckdb.cnf |
local_infile | OFF | Bulk load is server-side from /data; client-side LOCAL loads stay disabled. | systems/mariadb/duckdb.cnf |
max_connections | 200 | Headroom for the 32-stream throughput run plus probes and cancel connections. | systems/mariadb/duckdb.cnf |
character-set-server | utf8mb4 | The DuckDB engine accepts only utf8mb4 tables. | systems/mariadb/duckdb.cnf |
collation-server | utf8mb4_general_ci | utf8mb4 default collation for the bench database and its tables. | systems/mariadb/duckdb.cnf |
Percona
| Setting | Value | Reason | File |
|---|---|---|---|
skip-log-bin | Binary logging is off on every MySQL-family server (MySQL, Percona and AliSQL enable it by default, MariaDB does not): replication is not under test and the default differs between families. | systems/percona/mysqld-args.j2 | |
innodb_buffer_pool_size | 4G | The InnoDB side only holds the catalog and row-store staging; the data lives in DuckDB. | systems/percona/mysqld-args.j2 |
innodb_parallel_read_threads | 16 | Same as the InnoDB baseline: only COUNT(*) without WHERE and CHECK TABLE use parallel read threads. | systems/percona/mysqld-args.j2 |
secure_file_priv | /data | Server-side LOAD DATA INFILE may read only the read-only dataset mount. | systems/percona/mysqld-args.j2 |
local_infile | OFF | Bulk load is server-side from /data; client-side LOCAL loads stay disabled. | systems/percona/mysqld-args.j2 |
max_connections | 200 | Headroom for the 32-stream throughput run plus probes and cancel connections. | systems/percona/mysqld-args.j2 |
join_buffer_size | 256M | Per-join memory comparable to Postgres work_mem=256MB, same as the InnoDB baseline. | systems/percona/mysqld-args.j2 |
sort_buffer_size | 256M | Per-sort memory comparable to Postgres work_mem=256MB, same as the InnoDB baseline. | systems/percona/mysqld-args.j2 |
tmp_table_size | 4G | Internal temporary tables stay in memory up to mem_gb/16, same as the InnoDB baseline. | systems/percona/mysqld-args.j2 |
max_heap_table_size | 4G | Same cap for MEMORY-engine temporary tables, which use the smaller of the two sizes. | systems/percona/mysqld-args.j2 |
temptable_max_ram | 4G | RAM the TempTable engine may use before spilling, equal to the caps above. | systems/percona/mysqld-args.j2 |
innodb_redo_log_capacity | 8G | A redo log of mem_gb/8 keeps checkpoints from throttling the row-store staging load. | systems/percona/mysqld-args.j2 |
DUCKSDB_MEMORY_LIMIT | 0.75 x mem_gb = 48GB | Caps DuckDB's memory (the engine that runs the queries) at 75% of the container, the same share the InnoDB baseline gives its buffer pool; the rest covers MySQL, connections and the OS. | systems/percona/README.md |
DUCKSDB_TEMP_DIR | /var/lib/mysql/duckdb_tmp | Explicit spill location for DuckDB, on the datadir volume (created at start) instead of the engine default; same spill-location rule for all engines. | systems/percona/README.md |
MYSQL_ROOT_PASSWORD | empty | Passwordless root, as every MySQL-family adapter. | systems/percona/README.md |
MySQL InnoDB
| Setting | Value | Reason | File |
|---|---|---|---|
skip-log-bin | Binary logging is off on every MySQL-family server (MySQL and Percona enable it by default, MariaDB does not): replication is not under test and the default differs between families. | systems/innodb/my.cnf | |
innodb_buffer_pool_size | 48G | 75% of container memory for the buffer pool; the rest covers connections, sort and join buffers. | systems/innodb/my.cnf |
innodb_parallel_read_threads | 16 | Only COUNT(*) without WHERE and CHECK TABLE use parallel read threads; ordinary scans stay single-threaded in MySQL. Set to every core given to the database so those two are not held back. | systems/innodb/my.cnf |
secure_file_priv | /data | Server-side LOAD DATA INFILE may read only the read-only dataset mount. | systems/innodb/my.cnf |
local_infile | OFF | Bulk load is server-side from /data; client-side LOCAL loads stay disabled. | systems/innodb/my.cnf |
max_connections | 200 | Headroom for the 32-stream throughput run plus probes and cancel connections. | systems/innodb/my.cnf |
join_buffer_size | 256M | Per-join memory comparable to Postgres work_mem=256MB, so hash and block-nested joins do not spill. | systems/innodb/my.cnf |
sort_buffer_size | 256M | Per-sort memory comparable to Postgres work_mem=256MB, so ORDER BY and GROUP BY sorts stay in RAM. | systems/innodb/my.cnf |
tmp_table_size | 4G | Internal temporary tables (GROUP BY, DISTINCT, derived tables) stay in memory up to mem_gb/16. | systems/innodb/my.cnf |
max_heap_table_size | 4G | Same cap for MEMORY-engine temporary tables, which use the smaller of the two sizes. | systems/innodb/my.cnf |
temptable_max_ram | 4G | RAM the TempTable engine may use before spilling to disk, equal to the caps above. | systems/innodb/my.cnf |
innodb_redo_log_capacity | 8G | A redo log of mem_gb/8 keeps checkpoints from throttling bulk load at the 100MB default. | systems/innodb/my.cnf |
PostgreSQL
| Setting | Value | Reason | File |
|---|---|---|---|
listen_addresses | '*' | The runner container connects over the docker bridge. | systems/postgres/postgresql.conf |
shared_buffers | 16GB | 25% of container memory, the usual starting point; the OS page cache holds the rest. | systems/postgres/postgresql.conf |
effective_cache_size | 48GB | Planner's estimate of buffer plus page cache: 75% of container memory. | systems/postgres/postgresql.conf |
max_parallel_workers_per_gather | 16 | Allow one query to use every core given to the database. | systems/postgres/postgresql.conf |
max_parallel_workers | 16 | Cap on parallel workers across all sessions: one per core. | systems/postgres/postgresql.conf |
max_worker_processes | 16 | Must be at least max_parallel_workers for those workers to start. | systems/postgres/postgresql.conf |
work_mem | 256MB | Analytical sorts and hashes stay in memory instead of spilling at the 4MB default. | systems/postgres/postgresql.conf |
max_connections | 200 | Headroom for the 32-stream throughput run plus probes and cancel connections. | systems/postgres/postgresql.conf |
max_wal_size | 16GB | WAL of mem_gb/4 keeps checkpoints from throttling bulk load at the 1GB default. | systems/postgres/postgresql.conf |
checkpoint_timeout | 30min | Timed checkpoints every 30 minutes, so load and query runs are not interrupted by them. | systems/postgres/postgresql.conf |
pg_duckdb
| Setting | Value | Reason | File |
|---|---|---|---|
listen_addresses | '*' | The runner container connects over the docker bridge. | systems/pgduckdb/postgresql.conf |
shared_preload_libraries | 'pg_duckdb' | The extension must be preloaded (the image adds this to the default file, which is replaced here). | systems/pgduckdb/postgresql.conf |
shared_buffers | 16GB | 25% of container memory, as in the Postgres baseline: DuckDB reads heap pages through it. | systems/pgduckdb/postgresql.conf |
effective_cache_size | 48GB | Planner's estimate of buffer plus page cache: 75% of container memory. | systems/pgduckdb/postgresql.conf |
max_parallel_workers_per_gather | 16 | Allow one query to use every core given to the database. | systems/pgduckdb/postgresql.conf |
max_parallel_workers | 16 | Cap on parallel workers across all sessions: one per core. | systems/pgduckdb/postgresql.conf |
max_worker_processes | 16 | Must be at least max_parallel_workers for those workers to start. | systems/pgduckdb/postgresql.conf |
work_mem | 256MB | Analytical sorts and hashes stay in memory instead of spilling at the 4MB default. | systems/pgduckdb/postgresql.conf |
max_connections | 200 | Headroom for the 32-stream throughput run plus probes and cancel connections. | systems/pgduckdb/postgresql.conf |
max_wal_size | 16GB | WAL of mem_gb/4 keeps checkpoints from throttling bulk load at the 1GB default. | systems/pgduckdb/postgresql.conf |
checkpoint_timeout | 30min | Timed checkpoints every 30 minutes, so load and query runs are not interrupted by them. | systems/pgduckdb/postgresql.conf |
duckdb.max_memory | 32GB | DuckDB memory limit: half of container memory, leaving the rest for shared_buffers and page cache. | systems/pgduckdb/postgresql.conf |
duckdb.threads | 16 | DuckDB worker threads: one per core in the DB cpuset (default -1 lets DuckDB pick). | systems/pgduckdb/postgresql.conf |
duckdb.temporary_directory | '/var/lib/postgresql/duckdb_tmp' | DuckDB spill directory, explicit and on the data volume (the default is also under PGDATA). | systems/pgduckdb/postgresql.conf |
duckdb.threads_for_postgres_scan | 16 | Threads of one DuckDB heap scan: one per core (default 2), the analogue of max_parallel_workers_per_gather. | systems/pgduckdb/postgresql.conf |
duckdb.max_workers_per_postgres_scan | 16 | Postgres workers one DuckDB heap scan may use: one per core (default 2). | systems/pgduckdb/postgresql.conf |
pg_ducklake
| Setting | Value | Reason | File |
|---|---|---|---|
listen_addresses | '*' | The runner container connects over the docker bridge. | systems/pgducklake/postgresql.conf |
shared_preload_libraries | 'pg_ducklake' | The extension must be preloaded (the image adds this to the default file, which is replaced here). | systems/pgducklake/postgresql.conf |
shared_buffers | 4GB | Only catalog and staging heap tables live in shared_buffers: mem_gb/16 (the InnoDB-pool rule). | systems/pgducklake/postgresql.conf |
effective_cache_size | 48GB | Planner's estimate of buffer plus page cache: 75% of container memory. | systems/pgducklake/postgresql.conf |
max_parallel_workers_per_gather | 16 | Allow one query to use every core given to the database. | systems/pgducklake/postgresql.conf |
max_parallel_workers | 16 | Cap on parallel workers across all sessions: one per core. | systems/pgducklake/postgresql.conf |
max_worker_processes | 16 | Must be at least max_parallel_workers for those workers to start. | systems/pgducklake/postgresql.conf |
work_mem | 256MB | Analytical sorts and hashes stay in memory instead of spilling at the 4MB default. | systems/pgducklake/postgresql.conf |
max_connections | 200 | Headroom for the 32-stream throughput run plus probes and cancel connections. | systems/pgducklake/postgresql.conf |
max_wal_size | 16GB | WAL of mem_gb/4 keeps checkpoints from throttling bulk load at the 1GB default. | systems/pgducklake/postgresql.conf |
checkpoint_timeout | 30min | Timed checkpoints every 30 minutes, so load and query runs are not interrupted by them. | systems/pgducklake/postgresql.conf |
VillageSQL
| Setting | Value | Reason | File |
|---|---|---|---|
skip-log-bin | Binary logging is off on every MySQL-family server (MySQL, Percona and AliSQL enable it by default, MariaDB does not): replication is not under test and the default differs between families. | systems/villagesql/my.cnf | |
innodb_buffer_pool_size | 4G | The InnoDB side only holds the small table the JSON_TABLE join example uses; the analytical data is read by DuckDB from Parquet, so a large pool would only take memory from DuckDB. | systems/villagesql/my.cnf |
innodb_parallel_read_threads | 16 | Same as the other MySQL-family servers: every core for the two operations that use parallel reads. | systems/villagesql/my.cnf |
innodb_redo_log_capacity | 8G | Same redo log capacity as the InnoDB baseline. | systems/villagesql/my.cnf |
join_buffer_size | 256M | Per-join memory comparable to Postgres work_mem=256MB, same as the InnoDB baseline. | systems/villagesql/my.cnf |
sort_buffer_size | 256M | Per-sort memory comparable to Postgres work_mem=256MB, same as the InnoDB baseline. | systems/villagesql/my.cnf |
tmp_table_size | 4G | Internal temporary tables stay in memory up to mem_gb/16, same as the InnoDB baseline. | systems/villagesql/my.cnf |
max_heap_table_size | 4G | Same cap for MEMORY-engine temporary tables, which use the smaller of the two sizes. | systems/villagesql/my.cnf |
temptable_max_ram | 4G | RAM the TempTable engine may use before spilling, equal to the caps above. | systems/villagesql/my.cnf |
secure_file_priv | /data | Server-side LOAD DATA INFILE (the nation table of the join example) may read only the dataset mount. | systems/villagesql/my.cnf |
local_infile | OFF | Loads are server-side from /data; client-side LOCAL loads stay disabled. | systems/villagesql/my.cnf |
max_connections | 200 | Headroom for probes and cancel connections. | systems/villagesql/my.cnf |
character-set-server | utf8mb4 | vsql_duckdb returns utf8mb4 text; the server default is already utf8mb4, set so it cannot change. | systems/villagesql/my.cnf |
vsql_duckdb.threads | 16 | One DuckDB worker thread per core pinned to the database container (the extension default is 2). | systems/villagesql/settings.sql |
vsql_duckdb.memory_limit_mb | 49152 | Caps DuckDB at 75% of the container, the share the other MySQL-family engines give DuckDB (the extension default is 1024 MB). | systems/villagesql/settings.sql |
vsql_duckdb.allow_local_files | ON | Required to read the Parquet files on the dataset mount (the default OFF refuses local files); the same switch also lets DuckDB spill to disk. | systems/villagesql/settings.sql |
vsql_duckdb.temp_directory | '/var/lib/mysql/duckdb_tmp' | Explicit spill location on the data volume (only used while allow_local_files is ON). | systems/villagesql/settings.sql |
vsql_duckdb.timeout_ms | 900000 | The benchmark's per-query timeout (900 s) in ms; the extension default is 30000 and its maximum is 3600000. The adapter lowers it per query when a run uses a shorter --timeout. | systems/villagesql/settings.sql |
vsql_duckdb.max_result_bytes | 16777216 | The largest result the extension accepts (default 1048576). The result buffer the extension declares is 1 MiB on the stable extension ABI, so results above 1 MiB are still refused; see systems/villagesql/README.md. | systems/villagesql/settings.sql |
Dialect translation diffs
Plain DuckDB, pg_duckdb and the Parquet entry points use the canonical text. MySQL-family systems (including VillageSQL) and PostgreSQL get only these mechanical rewrites.
mysql: 1 of 22 queries change, 21 identical
--- q13.duckdb.sql
+++ q13.mysql.sql
@@ -4,14 +4,13 @@
FROM (
SELECT
c_custkey,
- count(o_orderkey)
+ count(o_orderkey) AS c_count
FROM
customer
LEFT OUTER JOIN orders ON c_custkey = o_custkey
AND o_comment NOT LIKE '%special%requests%'
GROUP BY
- c_custkey) AS c_orders (c_custkey,
- c_count)
+ c_custkey) AS c_orders
GROUP BY
c_count
ORDER BY
postgres: 0 of 22 queries change, 22 identical
Measure notes
SQL compatibility and silent slow paths (M5, M8)
An OLTP user does not read an engine's pushdown rules before writing a query. M5 asks what happens when they simply try the things a MySQL or PostgreSQL application does; M8 turns M1 and M5 into the share of statements that quietly left DuckDB.
M5: the probe suite
probes/probes.yaml holds 120 probes in nine categories: types (each column type round trips), functions (string, date, math, GROUP_CONCAT/string_agg, DATE_FORMAT, JSON), joins (inner, outer, semi, anti, and joins between an engine table and a row-store table), subqueries, cte (plain and recursive), window, dml (single-row and bulk writes, INSERT ... SELECT, upsert, transactions with rollback), ddl (ALTER, rename, TRUNCATE, index, CTAS) and variables (user variables, prepared statements, session settings).
- The suite was fixed before any result was seen. A probe changes afterwards only when its two dialects turn out not to express the same intent, or when the baseline's own result is invalid. It is never changed to move an engine's outcome. Such changes are listed in the results write-up.
- Two spellings per probe.
sql_mysqlandsql_postgressay the same thing. Where a feature has no equivalent (MySQL user variables,FULL JOIN, inlineENUM) the PostgreSQL spelling is the nearest idiom, and the probe says so in itsnote. - Scratch tables. Each probe creates its own tables (
pc_*) insetup, with{{ engine_clause }}(the system's engine:ENGINE=DuckDB,USING ducklake, or nothing forpg_duckdband the baselines) and drops them inteardown. Mixed-engine probes use{{ rowstore_clause }}(ENGINE=InnoDB, or a plain heap table) for a small row-store table joined with an engine table. Read-only probes may read the TPC-H tablesnation,regionandsupplier. A leftover table from an interrupted run is dropped beforesetup. - Every probe table has a primary key (a natural
idcolumn; a surrogatepkcolumn where ids repeat), as the TPC-H schema has wherever a system accepts one. Without it, an engine that requires a key (AliSQL and MariaDB: error 1173) would fail every probe atsetup, and the run would never show whether it supports the feature a probe is about. The single exception isddl_create_table_without_pk, which creates a table without a key and inserts a row, so that requirement is measured once, on its own. (A MySQLCREATE TABLE ... AS SELECTdeclares its key; PostgreSQL has no such syntax.) This was decided before any engine-specific tuning; the first version of the suite had no keys and was replaced for that reason. - Every read touches a table. A statement that reads no rows leaves a status counter such as MariaDB's
Rows_readunchanged and would look pushed.
Expected results
When a probe has no literal expected, the expectation is the result of the same probe on the row-store baseline of the same dialect (innodb for MySQL-dialect probes, postgres for PostgreSQL ones). The baselines run first, cache each result under results/<run_id>/_probe_reference/<probe>.<dialect>.json together with a hash of the probe's text for that dialect (setup, teardown, statements, check, compare mode, literal expected) and of its rendering for the baseline (so a change to the baseline's clauses or to the template rendering counts too), and record every probe as n/a (reason "row-store baseline; reference for the probe suite"). A cached result whose hash differs from the probe's current one is treated as missing, never reused: an engine record without a usable reference carries extra.reference = "stale" or "absent" ("ok" otherwise) and extra.compared = false. On resume the stale cells are redone: a baseline's cell whose cache is missing or keyed differently runs again and rebuilds the reference, an engine's cell compared with an older reference (including one set aside as n/a by the server control below after a wrong result) runs again and is compared with the new one, an engine's failure recorded without the server control runs again with it, and M8 is recomputed. Comparison is the one M1 uses (dbench.correctness.compare: order independent, numeric and CHAR tolerance, NULL only equals NULL). A probe with compare: json additionally parses every cell that holds a JSON object or array and re-serialises it canonically (sorted keys, no whitespace) on both sides, because systems print the same document with different spacing; real differences still show. A baseline that fails a probe leaves no reference; the engines' record then has extra.compared = false and is classified from pushdown only. When both baselines have run, the second also compares its result with the first's; extra.baseline_disagreement marks a probe whose dialects do not behave alike, which is a probe bug, not an engine finding.
Server control: failures of the host server itself
The reference comes from one server per dialect, MySQL 9.7 with InnoDB and Postgres 18. The integrations run on other servers: AliSQL, MariaDB, Percona's MySQL 9.7 build, Postgres with an extension. A probe can therefore fail because the host server's own SQL dialect lacks the feature, in its parser or its function library, before the DuckDB integration is involved at all (MariaDB has no REGEXP_LIKE, no LATERAL, and its LAG takes no default argument). Such a failure says nothing about the integration, so M5 checks for it. The rule is the same for every system:
- Whenever an engine probe ends as
errororwrong_result, it runs once more as a server control: the same probe on the same server and session, with every{{ engine_clause }}rendered as the system's{{ rowstore_clause }}(ENGINE=InnoDB, or a plain heap table). That is the same SQL on plain row-store tables, without the integration. Mixed-engine probes already use the row-store clause for some tables; in the control every table is a row-store table. - The control uses the probe's own tables and teardown: the probe's tables are dropped after the probe and again before and after the control, so the two never share a table. It has the same timeout and the same recovery after a crash or a timeout as the probe.
- The host server fails too. If the control fails with an error, or, for a wrong result, returns the same rows as the integration (so the server's own result differs from the reference in the same way), the probe does not test the integration on this server. It is recorded as
n/awith the reason "not supported by the *system* server itself (same failure on a row-store table): *the control's error*" (or "same result on a row-store table"). The integration's own outcome is kept:extra.integration_class,extra.integration_errororextra.diffs, andextra.server_controlholds what the control saw. - Only the integration fails. If the control runs and matches the expectation, the original class stands (an
ENGINE=DuckDBtable that refuses a table without a primary key, aCONCATthat ignoresNULL) andextra.server_controlis"ok", so a reader can see the check was made. The original class also stands when the control runs but differs from the reference, as long as the integration behaves differently from its own server: it failed where the control ran, or its wrong result is not the control's result.extra.server_controlthen records the control's mismatch. - No verdict. A control that crashes or times out proves nothing; the original class stands and
extra.server_control.conclusiveisfalse. - No control (
extra.server_control.skippedsays why) where the engine and row-store clauses are identical (pg_duckdb: there is no row-store variant to compare with), for probes that read the TPC-H tables (nation,region,supplierare engine tables on every integration, so the control would not be free of it), when the probe itself crashed or timed out (never set aside), when the server did not recover from the probe, and when the probe's teardown failed (a table left behind would make the control fail for the wrong reason). The baselines get no control.
When the probe has a literal expected, the control is compared with it, as the probe is.
Classes
extra.class is exactly one of:
| Class | Record status | Meaning |
|---|---|---|
duckdb | ok | A read probe ran, matched the expectation, and pushdown shows DuckDB ran it. |
fallback | ok | It ran and matched, on the row path (the engine did not take it). |
error | error | A statement failed: extra.stage is setup, statement or check, extra.error the server's message. A timeout or crash is an error too (extra.outcome); a crash restarts the server and the run goes on. |
wrong_result | wrong_result | It ran, but the rows differ from the expectation (extra.diffs). Reported even when the statement was pushed. |
unknown | ok | It ran and matched, but the adapter cannot tell where: pushdown_counter_scope is none, or the counter could not be read. |
ok_unobserved | ok | A DML or DDL probe that succeeded and whose follow-up check matched. Their pushdown is not scored (see below). |
n/a | n/a | The row-store baseline (reference builder); a mixed-engine probe on a system where the engine and row-store clauses are the same (pg_duckdb queries heap tables: "there is no separate engine table to mix with"); or a probe the host server itself does not support, as the server control shows ("not supported by the ... server itself"). |
Pushdown detection is the adapter's normal path, the same as M1: a session status counter (AliSQL Com_duckdb_select, MariaDB Rows_read), a global counter (Percona Ducksdb_pushdown_count; fine here because probes run one at a time), or a plan-only EXPLAIN (pg_duckdb and pg_ducklake). Systems with pushdown_counter_scope = none are unknown unless they errored.
DML and DDL probes. The counters watch SELECT pushdown only, so a counter around an UPDATE says nothing, and the follow-up SELECT (check_*) is not the statement under test. Write probes (categories dml and ddl) are therefore never duckdb or fallback: a correct one is ok_unobserved, a failed write is error, and a follow-up result that differs from the baseline is wrong_result (which is how a transaction that does not roll back shows up). The check's pushdown is kept as extra.check_pushed, as a fact about the check. Write probes are excluded from M8's probe share and from any per-category pushdown count. Scripts with several statements (BEGIN ... ROLLBACK, SET then SELECT) run on one session: the statements before the last are not observed and the last one is the probe, or the check when the category is dml or ddl. On PostgreSQL a plan-only EXPLAIN is never sent inside an open transaction (a failed one would abort it, and a SAVEPOINT guard is refused by DuckDB-backed tables), so the pushdown of a statement inside BEGIN ... COMMIT is unknown. The statements before the last get the same statement_timeout / watchdog bound as a query.
Each M5 record has item = <probe id>, extra.category, extra.title, extra.pushed (a read probe's pushdown; extra.check_pushed for a write probe), extra.class, and, when relevant, extra.error, extra.stage, extra.diffs, extra.row_count, extra.note. A probe that ended as error or wrong_result also has extra.server_control ("ok", what the control saw, or skipped), and one set aside by the control has extra.integration_class and, for an error, extra.integration_error.
M8: silent slow paths
M8 is derived, after M1 and M5, from the same system's records of the same run. It has three cells:
fallback_share_tpch: of the M1 queries that ran (statusokorwrong_result), the share withpushed = fallback.fallback_share_probes: the same status set over the M5 *read* probes (write probes are left out; errors are counted separately inextra.not_run).visibility: 1 when the adapter can observe pushdown at all, else 0.extra.mechanismisstatus_counter(session or global counter),explainornone. For a global counterextra.notesays the reading is ambiguous under concurrency.
A share is fallback / (duckdb + fallback + unknown). Statements whose pushdown is unknown are counted in extra.counts.unknown and in extra.unobserved_share, never as duckdb. A system with no way to observe pushdown has no share (n/a with the reason): 0% fallback there would only mean "not looked at". The baselines have no M8 (n/a: every statement is on the row path by construction), and plain DuckDB and Track B have neither M5 nor M8.
Changes after the first run
Made for every system alike, none for an engine's outcome; the YAML header lists them too.
fn_json_buildhascompare: json: JSON text was a falsewrong_result({"k": 1}vs{"k":1}), a representation difference the comparison now ignores.- Write probes are classed
ok_unobservedinstead of by the follow-up SELECT's pushdown. - The probe reference is keyed by a hash of the probe text and its baseline rendering; resume rebuilds stale references and re-runs the cells that depend on them.
- M8's TPC-H share now counts the same statuses as the probe share (
okandwrong_result). join_mixed_tpch_dimensioncarriesmixes: true(it joins a row-store table with the TPC-H engine table), so the generic mixed-engine rule makes itn/awhere the two clauses are the same.ddl_truncateinserts a sentinel row afterTRUNCATEand counts rows, so the check reads a row.var_session_time_zonenow reads a wall-clock timestamp on both sides (MySQLDATETIME, PostgreSQLTIMESTAMP, cast totimestamptz) and converts it in the session time zone (UTC+2); the MySQL side had usedTIMESTAMP, an absolute instant that ignores the session zone, so the two dialects did not agree (the baselines disagreed on it).- Mixed-engine probes are
n/awhere the engine and row-store clauses are identical. - The server control was added: a probe that fails, or returns the same wrong result, on a row-store table of the same server is
n/a("not supported by the ... server itself") instead of an integrationerrororwrong_result. It applies to every integration alike. On resume, every engine record with statuserrororwrong_resultand noextra.server_control(made before the control existed) runs again, unless no control would apply to it (see above); M8 is recomputed with it. - Earlier: primary keys on every scratch table (with
ddl_create_table_without_pkfor the no-PK case),ddl_add_primary_keybecameddl_change_primary_key,types_long_textusesLONGTEXTandcte_recursive_pathusesTEXTon PostgreSQL because the baseline result was invalid. - PostgreSQL probes use JSONB, the type applications normally choose; MySQL has one JSON type.
Storage size and load paths (M2, M3)
What is measured
M3 records the size of each system's data directory in bytes, at two points:
| item | taken |
|---|---|
after_load | right after the bulk load, before any query and before a checkpoint |
after_checkpoint | after the system's checkpoint statement |
A system without a checkpoint statement gets n/a for after_checkpoint with the reason; its after_load value is still recorded.
The value of each cell is the whole data directory, measured inside the database container with du -sb (apparent bytes). Replication logs are left out (MySQL-family binlog.*; every server runs with binary logging off, so these files are normally absent). Redo log and WAL files are counted: they are on the data volume and the system cannot run without them. Plain DuckDB is the exception: its value is the database file plus its .wal file, the only files the engine keeps.
Checkpoint statements:
| system | statement |
|---|---|
| plain DuckDB | CHECKPOINT |
| PostgreSQL, pg_duckdb, pg_ducklake | CHECKPOINT |
| AliSQL | CALL dbms_duckdb.query('CHECKPOINT') (the server rejects a bare CHECKPOINT) |
| MySQL InnoDB | none: n/a, InnoDB flushes pages continuously |
| Percona | none: n/a, no statement or function exists |
| MariaDB | SELECT run_in_duckdb('CHECKPOINT') (no CHECKPOINT statement; the server runs with duckdb_allow_run_in_duckdb = ON, see the bulk load below) |
Breakdown
Each M3 record carries extra.breakdown, the same total split by file:
| key | contents |
|---|---|
data_bytes | DuckDB database files and table data files |
redo_wal_bytes | redo log and write-ahead log (preallocated or not) |
temp_bytes | DuckDB spill and temporary directories, InnoDB temporary tablespace |
other_bytes | everything else: catalogs, config, doublewrite files, directory entries |
The buckets add up to the total (du -sb counts directory entries, which land in other_bytes; binlogs are excluded from all four). The listing is taken with find <dir> -printf '%y %s %P\n' in the container, and each path is classified by these rules.
MySQL family (MySQL, Percona, MariaDB, AliSQL), under /var/lib/mysql:
redo_wal_bytes:#innodb_redo/(MySQL 8.0.30 and later, including its spare files),ib_logfile*(MariaDB), and DuckDB*.waltemp_bytes:duckdb_tmp/, directories ending in.tmp,ibtmp1,#innodb_temp/data_bytes:*.duckdbandduckdb.db*(DuckDB files),ibdata*,*.ibd(InnoDB system and user tablespaces: they hold rows and the dictionary, and are not logs)- undo tablespaces (
undo*) are transient and count asother_bytes - a DuckDB
.walfile counts asredo_wal_bytes; engines without a checkpoint statement may keep rows that are not yet checkpointed there - excluded:
binlog.* - everything else is
other_bytes
PostgreSQL family (PostgreSQL, pg_duckdb, pg_ducklake), under $PGDATA:
redo_wal_bytes:pg_wal/temp_bytes:pgsql_tmpandduckdb_tmpdirectoriesdata_bytes:base/(heap, index and catalog files) andpg_ducklake//pg_duckdb/(the DuckLake Parquet files)- everything else is
other_bytes
The DuckDB spill directory configured for the Postgres engines lies beside $PGDATA (/var/lib/postgresql/duckdb_tmp), outside both the total and the breakdown.
Plain DuckDB, under /var/lib/duckdb: the .duckdb file is data_bytes, the .wal file is redo_wal_bytes, *.tmp is temp_bytes; directory entries are not counted.
The redo log is preallocated on InnoDB-based servers, so redo_wal_bytes is large and nearly constant whatever the data size; compare data_bytes when comparing engines.
Load paths (M2)
| item | what the time covers |
|---|---|
load_bulk | wall time of create_schema plus the system's bulk load, including the row-count check against the dataset manifest and the statistics step (ANALYZE, or VACUUM FREEZE for Postgres) |
load_convert | only the conversion of row-store tables to the DuckDB engine, plus the analyze step; the row-store staging load is in extra.rowstore_load_s |
The bulk path is each system's fastest documented loader. Most systems load the CSV files with LOAD DATA INFILE or COPY. pg_ducklake reads the same CSV files with DuckDB's read_csv. MariaDB follows its own TPC-H kit (storage/duckdb/tpch/04_load.sh in the MariaDB server repository): run_in_duckdb('INSERT INTO tpch.<table> SELECT * FROM read_parquet([...])') per table, so it reads the Parquet copy of the same dataset, not the CSV files. Its conversion path (load_convert) still stages the CSV files in InnoDB with LOAD DATA INFILE.
load_bulk is the same load that sets up the other measures of the system. It is timed once. A resumed campaign that has to load the data again to run missing cells does not record it again. load_convert runs after all the system's other measures, on a fresh datadir that is removed afterwards. Systems with no DuckDB engine or no row store to convert from record n/a with the reason. A row-count mismatch is recorded as error with extra.kind = "row_count".
Throughput (M4)
N concurrent streams each run the 22 TPC-H queries once, every stream in its own deterministic permutation (stream_order; streams are numbered from 1). One record per stream count, item = streams=N, unit = qph, value = ok_queries * 3600 / elapsed_s. Queries that errored, timed out or crashed are counted (counts_by_status) and do not count in the numerator. elapsed_s is the wall time from the moment every stream is released until the last one finishes.
- What is inside the timed region (the same for every family). Before the clock starts, each stream thread opens its own persistent connection, applies the session settings and the per-query timeout once, and runs one trivial statement. That statement executes in DuckDB on pg_duckdb, pg_ducklake, VillageSQL and plain DuckDB (a Postgres backend's DuckDB instance already exists when timing starts). The MySQL DuckDB engines (AliSQL, MariaDB, Percona) share one server-wide DuckDB instance, which the unrecorded warm-up pass already warms. All streams meet at a barrier and the clock starts. Per query a stream only sends the statement and fetches the result on its connection: no connect, no per-query
SET, noSHOW STATUS/EXPLAINprobe. The clock stops when the last stream finishes; only then are the connections closed. Client-side decoding of a result (VillageSQL's JSON) is inside the stream. A query that fails drops that stream's connection and the next query reopens one (a rare path, identical everywhere). - No correctness check. M1 owns correctness; M4 keeps only each query's status.
- Warm cache. One unrecorded warm-up pass of the 22 queries on a single stream runs before each stream count. The OS page cache is not dropped between stream counts.
- Memory per backend. pg_duckdb (
0.5 * mem_gb / N) and pg_ducklake (0.75 * mem_gb / N) divide DuckDB's memory by the stream count per session (prepare_streams), recorded asmemory_per_backend. MySQL-family engines and VillageSQL share one DuckDB instance per server and get no per-stream setting. - Pushdown. M4 does not probe pushdown (that is M1/M8's job);
pushed_countsonly countsnot_measured.pushdown_counter_scope(session, global, explain, none) is recorded for M8. - Contamination. After a timeout the stream calls
cancel()and polls whether that very query is still running (still_running: processlist /pg_stat_activity) for up to 10 s. The run iscontaminatedwhen it survives, whencancel()could not be issued, when a probe fails, when a timeout's stop was never confirmed and several streams ran, or whenlingering_queries()still shows queries after all streams are done. The server is restarted after the record is written, never mid-run. After a timeout without a server-confirmed stop, that stream's poll (up to 10 s) is inside the timed region. - Status. Only the per-query outcomes decide it. A run in which any query
crashed(its server's container had stopped) iscrashed; otherwise one in which any query failed with anerroriserror. Neither has a value: a failed query frees its stream early, so the queries per hour of the rest would read as throughput. That figure is kept asextra.ok_queries_qphand the error names the counts. A crash is named a memory kill ("killed by its memory limit", OOMKilled) only when Docker reports it for the container. A run ofokandtimeoutqueries only isokwith its value, since a timeout is a measured outcome.contaminatedandrestarted_after_runrecord what the harness did after the run and never change the status. Records written before this rule were given it withdbench m4-restate <run_id>, which reads only their stored counts (extra.restatedmarks them). - Host suspend. If the host slept more than 1 s during a run, the run is discarded and repeated (up to 2 retries, recorded in
discarded_attempts); if every attempt slept the campaign records an automatic failure that a resume retries. - Track B (over Parquet). With
track_b.measures: [M1, M4]in the campaign, each Track B system (plain DuckDB 1.5.5, VillageSQL, pg_duckdbduckdb.query(), MariaDBrun_in_duckdb()) runs the same M4 at the campaign's stream counts, sending the 22 Track B query texts (DuckDB SQL over Parquet) through its raw entry point, under the run id<run_id>-trackb. Everything above applies unchanged. The hybrid joins are M1 only. Track B throughput is shown in its own chart and never compared with the table-based throughput.
Rerun it yourself
git clone https://github.com/EvgeniyPatlan/duckdb-in-oltp-bench.git && cd duckdb-in-oltp-bench
scripts/dev.sh dbench --version
scripts/build-images.sh
DBENCH_DATA=/srv/dbench/data scripts/dev.sh dbench datagen --sf 10
DBENCH_DATA=/srv/dbench/data DBENCH_RESULTS=/srv/dbench/results DBENCH_RUNNER_CPUSET=16-23 \
scripts/dev.sh dbench campaign campaigns/gate-sf10.yaml
python -m sitegen.build --results /srv/dbench/results --out _site
A campaign resumes after any interruption. See campaigns/README.md for the smoke run and the SF100 talk campaign.