DuckCon #7
When the sea lion learns to quack: DuckDB as a MariaDB storage engine
Roman Nozdrin · DuckCon #7
10
sections
9
slides
~17 min
duration
01 / The Goal
Teaching the sea lion to quack
- The MariaDB mascot is a sea lion; the talk frames the work as teaching it to quack like a duck (DuckDB).
- Goal: a high-performance OLAP execution runtime that lives as close to MariaDB as possible and works in multi-tenant scenarios.
- The 'duck' here is a new smart storage engine for MariaDB, with DuckDB embedded in-process.
02 / The Pool: OLAP in MariaDB Today
Existing analytics options and their limits
- MariaDB ColumnStore is a columnar analytics engine, but runs in its own processes (ExeMgr, PrimProc) beside the server.
- Exasol is a high-performance MPP database, but data is offloaded to a separate external cluster.
- Both run outside the MariaDB runtime: a process boundary, network hops, and data that must be kept in sync.
03 / Architecture of the Smart Engine
How MariaDB and DuckDB split responsibilities
- 'Smart' means the MariaDB optimizer can push not just filtered scans but parts of a query, ideally the full query, down to the storage engine.
- MariaDB stays in charge of client protocol, parsing, metadata, and all user authentication and privilege handling; DuckDB sees no ACL operations.
- DuckDB owns columnar storage, vectorized execution, and parts of MVCC.
- Two core components: a singleton DuckDB Manager (shared instance, catalog, buffer pool, WAL, temp dir, MariaDB-compatible UDFs, cross-engine joins) and per-connection DuckDB THD contexts (private MVCC transactions, temp tables, session settings).
More detail
Writes: native MariaDB DDL is translated into DuckDB syntax via converters. Multi-statement transactions can mutate both DuckDB tables and other engines (InnoDB, Aria). Mutations are row-by-row or batched; a delta appender buffer in the THD context periodically flushes into a temporary DuckDB table, which is merged with the persistent table at commit time.
04 / Read Path and Cross-Engine Joins
Select-handler pushdown and replacement scans
- The happy read path is select-handler pushdown: the whole query is handed to DuckDB for execution.
- SQL syntax differences between MariaDB and DuckDB are handled by simple rewrites early in the pushdown path.
- DuckDB returns data chunk by chunk; MariaDB converts it and streams results to the client over the client protocol.
- Cross-engine joins (mixing DuckDB with Aria/InnoDB in one query) use a custom replacement scan that loops back into MariaDB, similar to the pg_duckdb approach; full pushdown including limits is possible, and stateful fibers allow streaming batches back.
05 / First Quack in Numbers: TPC-H
Engine vs DuckDB 1.5.2 CLI on TPC-H SF1000
- TPC-H SF1000 (~8.7B rows, 400 GB Parquet): engine totals 480.8s vs 637.9s for DuckDB CLI 1.5.2, i.e. 1.33x faster, winning 17 of 22 queries; best case Q15 at 2.72x.
- Why: the engine is a persistent in-process DuckDB instance with a warm buffer pool, while the CLI spawns a fresh process per query and pays cold-start every time.
- Honest caveat: single run, no repetition; a single-session CLI run would narrow the gap. Q21 (EXISTS patterns) is the one regression where the CLI wins.
More detail
Hardware: AMD EPYC 9454 (48 cores), 1125.5 GB RAM, Micron 7500 PRO NVMe, both with a 90 GB memory limit, DuckDB v1.5.2. The engine currently tracks DuckDB 1.5.2 and will follow new vanilla DuckDB versions.
06 / ClickBench Results
On-disk size, cold and hot runs, concurrency
- ClickBench compared the engine, a recent DuckDB CLI (1.5.3), and the latest official ClickHouse build; engine and CLI loaded a single Parquet file while ClickHouse used partitioned Parquet.
- Wall time is dominated by DuckDB CLI, with the engine close behind (some extra overhead from added complexity); ClickHouse trails.
- On-disk size: ClickHouse is very good (likely dictionary-encoding changes); the engine footprint is larger because dictionary encoding was disabled for some columns in the generated DDL, a ~7 GB gap that matters later.
- Cold run is dominated by the CLI (the 7 GB size difference makes the gap huge); hot run is dominated by the engine thanks to the warm buffer cache.
07 / Concurrent, Multi-Tenant Scenarios
Running 10 ClickBenches in parallel
- The multi-tenant test runs 10 ClickBenches in parallel, measured by QPS and error ratio.
- ClickBench runs the DuckDB CLI in read-write mode, which blocks other clients from operating on data (a safety choice for the small C6A.4xlarge machine), producing a scary error column.
- On a bigger bare-metal machine tuned to run 10 threads, DuckDB CLI has no errors and performs well; ClickHouse, built for big machines, outperforms the CLI, but a hot buffer cache still makes a large difference (ClickHouse ~2x behind on hot runs).
08 / Embedding Isn't Enough
The MariaDB parser shuts the door on rich SQL
- Even with DuckDB embedded, every client query still enters through MariaDB's Bison parser.
- DuckDB-native syntax (EXCLUDE, QUALIFY, list/struct types, PIVOT, DuckDB extensions) is rejected before it ever reaches DuckDB, with an ERROR 1064 syntax error.
- The engine could handle this syntax, but the parser disables it very early, so embedding alone is not enough.
09 / The Plugin Path Forward
A new plugin to unlock full DuckDB SQL
- The MariaDB way to add options is a plugin; this plugin steps in very early in query processing.
- When activated and chosen, its handler returns one of three outcomes: 'not mine' (fall back to normal MariaDB parsing/execution), executed happy path (send results, clean up), or error (run error handling).
- Flow: extract statement to classify by statement type, then privilege check, then run the query, yielding either the executed happy path or an error.
- This delivers the rich DuckDB syntax alongside the multi-tenant performance wins, which the speaker considers reaching the goal.
10 / Acknowledgements & Availability
Thanks and where to try it
- Thanks to the DuckDB community and DuckLabs, the MariaDB Foundation/Corporation, Alibaba for open-sourcing AliSQL (which kick-started the project), and Monty for the quacking narrative.
- The code is merged into upstream MariaDB LTS branches 11.4, 11.8, 12.3, and 13.x.
- MariaDB server packages with the DuckDB storage engine ship with the next maintenance release, scheduled for end of July; for now you must reduce plugin maturity to use it.
- Licensed GNU GPL v2; source at github.com/MariaDB/server/tree/11.4/storage/duckdb.
Key Takeaways
- The project embeds DuckDB directly inside MariaDB as a 'smart' storage engine, keeping OLAP execution in-process instead of behind a network hop like ColumnStore or Exasol.
- MariaDB retains protocol, parsing, metadata, and ACL; DuckDB owns columnar storage, vectorized execution, and parts of MVCC.
- Full-query pushdown via a select handler, plus cross-engine joins through custom replacement scans that loop back into MariaDB (mirroring pg_duckdb).
- A persistent in-process DuckDB with a warm buffer pool beats the per-query cold-starting CLI: 1.33x on TPC-H SF1000 and strong hot-run ClickBench numbers.
- Benchmarks come with honest caveats: single runs, disabled dictionary encoding inflating on-disk size, and ClickBench's read-write CLI mode distorting concurrency results.
- Embedding alone is not enough: MariaDB's Bison parser rejects DuckDB-native syntax, so a new plugin intercepts queries early to unlock EXCLUDE, QUALIFY, PIVOT, and DuckDB extensions.
- The engine is already merged into MariaDB LTS branches (11.4, 11.8, 12.3, 13.x) under GPL v2, with packaged releases due end of July.