DriftDB vs SQLite and DuckDB on 100 million rows
We loaded a year of sensor readings into three embedded databases and timed inserts, range scans and downsampling queries.
On this page4 sections
People ask us the same question in Discord every week: why not just use SQLite? It’s a fair question. So we ran the numbers on DriftDB v0.9.0, SQLite 3.50 and DuckDB 1.4, all embedded, all on the same machine.
The setup
- Data: 100 million readings from 2,000 simulated sensors, one reading every 5 minutes for a year. Each row is a timestamp, a sensor id and a
f64value. - Hardware: AMD Ryzen 9 7950X, 64 GB RAM, Samsung 990 Pro NVMe, Linux 6.10.
- Settings: SQLite in WAL mode with a
(sensor_id, ts)index. DuckDB with default settings. DriftDB with default settings and 64 MB block cache.
Every query ran 10 times after one warm-up run. The table shows the median.
Results
| Workload | DriftDB 0.9 | SQLite 3.50 | DuckDB 1.4 |
|---|---|---|---|
| Bulk insert, 100M rows | 41 s | 6 min 12 s | 58 s |
| Size on disk | 1.3 GB | 6.8 GB | 1.9 GB |
| Last hour, one sensor | 0.2 ms | 0.4 ms | 11 ms |
| Last 30 days, one sensor | 3.1 ms | 48 ms | 19 ms |
| Hourly average, all sensors, 1 day | 38 ms | 2.9 s | 61 ms |
| Daily max, all sensors, 1 year | 410 ms | 51 s | 290 ms |
What the numbers say
DriftDB wins where time-series workloads spend most of their time: appending data and reading recent ranges. Columns are compressed per block with delta-of-delta timestamps and Gorilla-encoded floats, so a year of data fits in 1.3 GB.
DuckDB wins the full-year scan. It’s a general analytical engine with a vectorized executor, and for wide scans over everything it’s faster than us. We think that’s fine. If most of your queries look like the last row, DuckDB is a great choice.
SQLite is not built for this workload, and nobody should read this as a knock on SQLite. Its row storage and B-tree index do exactly what they promise.
What we’re fixing next
Wide scans are slow because DriftDB decodes every block before it aggregates. In 0.10 we’re adding block-level min, max and sum statistics, so a daily max over a year can skip decoding entirely.