mORMot Open Source

tfopc

Member

Registered: 2024-01-08

Posts: 57

DuckDB in mORMot 2 - analytics benchmark against SQLite3,PostgreSQL

Hi everyone,

First of all, a big thank you to Arnaud for mORMot 2 - a brilliant framework - and for reviewing and merging the DuckDB support.

DuckDB is an embedded, in-process analytical database: one library like SQLite3, but a column store with a vectorized, multi-threaded engine and a rich analytical SQL dialect, which also queries CSV and Parquet files directly. mORMot 2 accesses it through the usual mormot.db.sql classes, either embedded via the DuckDB library, or remotely via a native Quack protocol client which needs no DuckDB library at all.

To be very clear: this is not meant to replace SQLite3. SQLite3 stays the strong and fast engine for writing and for typical ORM work - in my measures, a primary key lookup or a single insert is 400 to 1,000 times faster with SQLite3 than with DuckDB. DuckDB is the tool for the other side: aggregating and analyzing large amounts of data. And both work together, since DuckDB can query an existing SQLite3 (or PostgreSQL) database directly via ATTACH, with no migration.

To see where DuckDB pays off, I wrote a small benchmark tool (dbbench) which runs the same queries on DuckDB, SQLite3 and PostgreSQL, all through mormot.db.sql, with best-practice indexes for SQLite3 and PostgreSQL. It uses two datasets: TPC-H (6 million line items, generated by DuckDB), and real open data from the Dutch railways (all 2024 train services, 22 million stops).

Results (4 vCPU Linux VM, DuckDB 2.0.0, SQLite3 3.51/3.45, PostgreSQL 16):
- analytical queries: DuckDB is 3 to 200 times faster than SQLite3, and 2 to 65 times faster than PostgreSQL
- the DuckDB database file is 5 to 9 times smaller, indexes included
- DuckDB reading an existing SQLite3 file runs the heavy reports up to 100 times faster than SQLite3 itself
- point lookups and single-row inserts: SQLite3 wins by far, as written above

So far it has been tested with FPC on Linux - feedback with Delphi and Windows is welcome.

All details - datasets, queries in the three SQL dialects, indexes, settings and full results - are in the README:
- English: https://github.com/tobfel/mORMot2/blob/ … /README.md
- German: https://github.com/tobfel/mORMot2/blob/ … ADME.de.md

If you are currently aggregating large amounts of data with mORMot 2, you are very welcome to run the benchmark - or your own queries - against your data, and to report your query times here.

Enjoy the weekend - may your queries be quick and your ducks in a row! <(')___