--- title: Why SQLite description: Why an embedded database, what it costs, and how fast Sluurp is on it. section: Start order: 3 --- # Why SQLite

What Sluurp offers is one binary, one file and nothing to run beside it. That depends on an embedded database: a backend that first asks you to stand up Postgres has given up that promise before it starts.

## What an embedded database buys - **No network between the app and its data.** A query is a function call inside the process, not a round trip to another server. For the small indexed reads most pages make, that is most of the cost gone. - **Nothing to operate.** No database server to install, upgrade, tune, secure or wake up at night. `sluurp serve` is the whole deployment. - **A project is a file.** Each project is its own SQLite file, and nothing spans two. Tenancy is a folder of files, and a backup is a copy of one (see Backups in the admin UI). Moving a tenant is moving a file. - **Readers don't wait for each other.** In WAL mode, reads run concurrently with each other and with the writer. Sluurp's connection pool follows the CPU count, so reads scale with cores. - **The same engine in the browser.** [Sync](/docs/sync) can keep rows in SQLite in the browser, the official WebAssembly build, so SQL works the same on both sides, `FOR SYSTEM_TIME AS OF` included. - **Collections are real tables.** A collection is a table with real indexes, uniqueness and SQLite's query planner, not rows in a generic key-value blob. Rules and filters are compiled into that SQL with bound parameters. ## What it costs **One writer at a time.** SQLite serialises writes. Reads scale with cores, but writes don't. A write in Sluurp is a short transaction (the row, its history where the collection keeps one, its rule checked again), so the queue moves quickly. For most apps (a school, a shop, an internal tool, a SaaS with tenants in their own files) that's far more headroom than they use. An app that has to take tens of thousands of writes a second into one table needs something else. ## Why not something else | | Why not | |---|---| | **Postgres** | A server to run beside the binary, which is the thing Sluurp exists to remove. It stays the escape hatch for an install that outgrows one file: storage is behind a trait, so a Postgres driver is a driver, not a rewrite. | | **CockroachDB** | Written in Go, so it has no library form to embed. Since v24.3 the self-hosted Core edition is gone, and the source-available licence restricts redistribution. | | **pgrust** | Postgres rewritten in Rust, and progressing fast. It is AGPL-3.0, warns against storing data you care about in it, and its embeddable build is still on the roadmap. | | **Turso** | The Rust rewrite of SQLite: MIT, in-process, compatible with SQLite's file format, with async I/O and concurrent writes. The likely future engine, and switching to it would be a driver change, not a migration. That's why storage sits behind a trait. | ## How fast `sluurp bench` measures collection access where Sluurp does it: through its storage layer, as a request's handler calls it. That covers the rule compiled into the query, the filter parsed and bound, SQLite, and the row back as JSON. It runs in a fresh data directory that it throws away afterwards, and reports each operation per second, with the median and the 99th percentile of one call. ```sh title="Terminal" sluurp bench # a table, and a score sluurp bench --json # the same, for a page to show ``` On AMD Ryzen 7 9800X3D 8-Core Processor, 16 threads, Windows, 10,000 rows (Sluurp 0.1.0): | Operation | Callers at once | Per second | Median | p99 | |---|---|---|---|---| | Create, one caller | one | 6,308 | 0.124 ms | 0.336 ms | | Create | 32 | 3,939 | 4.656 ms | 60.301 ms | | Read one row by id, one caller | one | 21,728 | 0.04 ms | 0.098 ms | | List 20, filtered on an index, one caller | one | 7,975 | 0.116 ms | 0.199 ms | | Read one row by id | 32 | 25,847 | 0.66 ms | 14.915 ms | | List 20, filtered on an index | 32 | 23,222 | 1.354 ms | 3.417 ms | | Count by group (10000 rows) | 32 | 24,041 | 1.084 ms | 5.323 ms | | List 20 under a row rule | 32 | 41,618 | 0.681 ms | 2.423 ms | | Update one field | 32 | 7,721 | 2.186 ms | 41.342 ms | | Create with history kept | 32 | 2,946 | 6.399 ms | 72.496 ms | | List 20 as it stood (asOf) | 32 | 6,513 | 4.044 ms | 29.912 ms | | Delete | 32 | 1,912 | 10.319 ms | 83.838 ms | **Score: 2,048.** The score is the geometric mean of each measure's speed relative to a reference run on this same machine, times 1,000. Every measure counts alike, whatever its scale, and a machine twice as fast at everything scores 2,000. Two runs on one machine differ by a few percent. This machine scores about twice the reference because the reference run came before lists "as it stood" were made faster, and before writers stopped sleeping through their turn at the lock. ### Against bare SQLite The same operations straight through SQLite (rusqlite, the same settings, one caller, prepared statements), with no rules, no filter parsing, no pool and no JSON. It shows what Sluurp adds on top of the engine: | Operation, one caller | Per second | Median | p99 | |---|---|---|---| | Create, one at a time | 11,404 | 0.065 ms | 0.161 ms | | Read one row by id, one caller | 214,210 | 0.005 ms | 0.005 ms | | List 20, filtered on an index, one caller | 47,475 | 0.021 ms | 0.03 ms | | Count by group (10000 rows), one caller | 4,651 | 0.21 ms | 0.348 ms | A row read by id takes about 5 µs in bare SQLite and about 45 µs through Sluurp. The difference is the view rule compiled into the query, the call handed to a pooled connection, and the row turned into JSON. A create costs about 0.07 ms bare and 0.11 ms through Sluurp, which also checks the create rule again inside the transaction. How to read the table: - **Reads don't wait for each other.** A row by id, a page filtered on an index, a page under a row rule and an aggregate over 10,000 rows each take about a millisecond, and tens of thousands run a second across cores. - **Writes take turns.** One caller creates a row in about 0.1 ms. Many callers at once share SQLite's single writer, so their throughput is about the same and each one waits its turn. - **The past is quick too.** A list "as it stood" (`asOf`) is rebuilt by SQLite in one statement from the change log, and kept on the connection until something at or before that moment changes. It used to take about a second on this data. - **Deletes keep up.** A delete also writes the row to the recycle bin, and still runs about as fast as a create with history kept. Writers waiting their turn used to sleep up to 100 ms between tries, while the lock was free for most of that; they now try again within a fraction of a millisecond, which made 32 deletes at once two and a half times faster than before (778 a second), and updates twice as fast. ## Beyond one machine Several Sluurp nodes can serve one data directory behind a load balancer. Each node is told where it can be reached (`--advertise`, `--node-id`), and a tab stays on one node through a cookie the balancer pins on. Every node needs the same `--dir`, on storage they can all read and write, and that storage is the part with real constraints: the README's "Sharing the data directory" section covers them. Projects are separate files, so spreading them over more machines later is a routing problem (move a file, update a map), not a distributed-join problem.