31 Dec 2023
How fast is SQLite?
Plenty fast
I have recently been on a path towards radical simplification of the systems I build. Forgoing Spring Boot for a simple Axum Rust backend is a simple choice for me. Choosing a database is an entirely different story. I have been using PostgreSQL successfully, but operating it has been a bit of a black box. Sure, I just spin up a standard docker container and it works, but I wouldn’t want to start straying from the default configuration considering the absolute wealth of features and manual pages there are. So I started looking into SQLite.
Advantages
SQLite is an embedded database, a library if you will. Compared to dedicated DB servers that need to be operated separately, this simplifies the deployment significantly. Additionally, the latency of queries is extremly low, as they are more function call than networked request. For incremental backups, there is the excellent Litestream project that observes the write-ahead log and pushes changes up to an S3 compatible object storage. Right up my alley.
Performance
I conducted some microbenchmarks to gauge the performance of SQLite. There is one table that is declared as follows:
CREATE TABLE metrics(bucket TEXT NOT NULL, date TEXT NOT NULL, data TEXT NOT NULL);You might notice that the motivating application to try SQLite is a home-grown metrics collection
system. For one scenario I also add indices on bucket and date. Next, I ran a bunch of
(hopefully) self explanatory scenarios and measured the execution time of the queries in isolation.
Read queries are prepared statements of the form:
SELECT * FROM metrics WHERE bucket = ?1 AND rowid = ?2;Notice that rowid is an implicit column added by SQLite. Write queries:
INSERT INTO metrics (bucket, date, data) VALUES (?1, ?2, ?3);The benchmarks were run on my local macbook, as well as a linux ARM server and a linux x86 server on Hetzner.
Results
| Scenario | Throughput | 90 percentile duration |
|---|---|---|
| Vanilla (read) | 183’150/s | 6us |
| Vanilla (write) | 4’363/s | 248us |
| WAL Mode (read) | 454’338/s | 3us |
| WAL Mode (write) | 14’401/s | 37us |
| WAL + Synchronisation Normal (read) | 483’558/s | 3us |
| WAL + Synchronisation Normal (write) | 113’684/s | 8us |
| WAL + In-memory (read) | 50’000’000/s | 0us |
| WAL + In-memory (write) | 981’836/s | 1us |
| WAL + Index (read) | 440’917/s | 3us |
| WAL + Index (write) | 47’359/s | 25us |
| WAL + Index (mixed, 80% read) | 197’012/s | 15us |
| WAL + Index (mixed 80% write) | 59’111/s | 16us |
Table 1: M1 Macbook Air results
| Scenario | Throughput | 90 percentile duration |
|---|---|---|
| Vanilla (read) | 94’786/s | 12us |
| Vanilla (write) | 560/s | 2’463us |
| WAL Mode (read) | 238’322/s | 6us |
| WAL Mode (write) | 3’316/s | 585us |
| WAL + Synchronisation Normal (read) | 275’406/s | 5us |
| WAL + Synchronisation Normal (write) | 46’512/s | 18us |
| WAL + In-memory (read) | 943’396/s | 1us |
| WAL + In-memory (write) | 299’976/s | 4us |
| WAL + Index (read) | 186’358/s | 7us |
| WAL + Index (write) | 19’479/s | 46us |
| WAL + Index (mixed, 80% read) | 75’153/s | 24us |
| WAL + Index (mixed 80% write) | 22’801/s | 42us |
Table 2: Linux Ampere ARM results (Hetzner CAX31)
| Scenario | Throughput | 90 percentile duration |
|---|---|---|
| Vanilla (read) | 73’800/s | 14us |
| Vanilla (write) | 925/s | 1’484us |
| WAL Mode (read) | 332’225/s | 4us |
| WAL Mode (write) | 5’542/s | 211us |
| WAL + Synchronisation Normal (read) | 315’756/s | 4us |
| WAL + Synchronisation Normal (write) | 80’145/s | 12us |
| WAL + In-memory (read) | 952’380/s | 1us |
| WAL + In-memory (write) | 322’695/s | 3us |
| WAL + Index (read) | 315’357/s | 4us |
| WAL + Index (write) | 26’469/s | 34us |
| WAL + Index (mixed, 80% read) | 121’703/s | 15us |
| WAL + Index (mixed 80% write) | 36’260/s | 26us |
Table 3: Linux AMD x86 results (Hetzner CPX31)
Observations
Notice the increase of queries per second across the board once write-ahead log (WAL) mode is turned
on and Synchronization is set to Normal. The 90th percentile duration column is indicated in
microseconds! Even the slow queries are still incredibly fast. The most “real-world” scenario here
is probably WAL + Index (mixed, 80% read). We are looking at a QPS in the ballpark of 100'000
regardless of platform. Plenty fast for my usecases.
Limitations
I found it hard to get any speedup when trying to access a single SQLite DB from multiple threads.
Writes are single threaded by nature, not much of a debate there, but reads (at least in WAL mode)
should scale well. It’s probably down to the library I use for connection pooling. But considering
single threaded performance is already so good, I didn’t spend to much time here. In Rust lingo, I
just use an Arc<Mutex<Connection>> to protect a single connection from concurrent access.