DocsGet started

Why SQLite

Why Sluurp uses an embedded database, what it costs, and how fast it is.

Sluurp promises one binary, one data file and nothing else to run. That needs an embedded database: a backend that starts with “first, set up Postgres” has already broken the promise.

What you get

  • No network hop. A query is a function call in the same process, not a round trip. For the small indexed reads most pages do, that removes most of the cost.
  • Nothing to operate. There is no database server to install, upgrade, tune, secure or be paged about. sluurp serve is the whole deployment.
  • A project is a file. Each project is its own SQLite file. Many tenants are a folder of files, a backup is a copy of a file, and moving a tenant is moving a file.
  • Concurrent reads. In WAL mode, readers don’t block each other or the writer. The connection pool is sized to the number of CPUs, so reads scale with cores.
  • The same filters in the browser. Sync answers a list from rows the page already holds, matching filters the way SQLite does, so a narrower list shows at once.
  • Collections are real tables. They have real indexes, unique constraints and SQLite’s query planner, not rows in a generic key-value blob. Rules and filters compile to SQL with bound parameters.

The tradeoffs

One writer at a time. SQLite runs writes one after another: reads scale with cores, writes don’t. A write in Sluurp is a short transaction (the row, its history if kept, the rule checked again), so the queue moves fast. Most apps, such as a shop, an internal tool or a SaaS with one file per tenant, have far more room than they need. If you need tens of thousands of writes per second into one table, use something else.

Every commit waits for the disk. Sluurp runs SQLite with synchronous = FULL, so a write that was answered survives a power cut. In a swarm, a write also waits until a majority of the nodes have it, which adds one round trip on your network.

Why not something else

Why not
PostgresA separate server, which is exactly what Sluurp exists to remove. It stays the way out for installs that outgrow one file: storage sits behind a trait, so Postgres support is a new driver, not a rewrite.
CockroachDBWritten in Go, with no embeddable library. Since v24.3 the self-hosted Core edition is gone, and the source-available licence restricts redistribution.
pgrustPostgres rewritten in Rust, and moving fast. But it is AGPL-3.0, warns against storing important data, and its embeddable build is still on the roadmap.
TursoThe Rust rewrite of SQLite: MIT, in the same process, compatible with the file format, with async I/O and concurrent writes. The likely future engine. Switching would be a driver change, not a migration, which is why storage sits behind a trait.

How fast

To measure it on your machine, run sluurp bench. It goes through Sluurp’s storage layer exactly as a request does: rule compilation, filter parsing and binding, SQLite, and JSON. It uses a throwaway data folder, and reports throughput with the median and p99 latency of each operation.

sluurp bench            # table and score
sluurp bench --json     # machine-readable

On an AMD Ryzen 7 9800X3D (8 cores, 16 threads), Windows, 10,000 rows, Sluurp 0.1.0:

OperationCallers at oncePer secondMedianp99
Create, one callerone6,3080.124 ms0.336 ms
Create323,9394.656 ms60.301 ms
Read one row by id, one callerone21,7280.04 ms0.098 ms
List 20, filtered on an index, one callerone7,9750.116 ms0.199 ms
Read one row by id3225,8470.66 ms14.915 ms
List 20, filtered on an index3223,2221.354 ms3.417 ms
Count by group (10000 rows)3224,0411.084 ms5.323 ms
List 20 under a row rule3241,6180.681 ms2.423 ms
Update one field327,7212.186 ms41.342 ms
Create with history kept322,9466.399 ms72.496 ms
List 20 as it stood (asOf)326,5134.044 ms29.912 ms
Delete321,91210.319 ms83.838 ms

Score: 2,048. The score is the geometric mean of each operation’s speed against a reference run, × 1,000. Every operation weighs the same whatever its scale, so a machine twice as fast at everything would score about 4,100. Runs vary by a few percent. This machine scores about twice the reference because the reference predates the asOf speed-up and the fix to retries on the write lock.

Against bare SQLite

The same operations straight on SQLite (rusqlite, the same settings, one caller, prepared statements), with no rules, filter parsing, pool or JSON. This is what Sluurp adds over the raw engine:

Operation, one callerPer secondMedianp99
Create, one at a time11,4040.065 ms0.161 ms
Read one row by id, one caller214,2100.005 ms0.005 ms
List 20, filtered on an index, one caller47,4750.021 ms0.03 ms
Count by group (10000 rows), one caller4,6510.21 ms0.348 ms

Reading a row by id takes about 5 µs in bare SQLite and 45 µs through Sluurp. The difference is the compiled view rule, the dispatch to a pooled connection, and JSON. A create takes about 0.07 ms bare and 0.11 ms through Sluurp, which also checks the create rule again inside the transaction.

What the numbers say:

  • Reads scale. A read by id, a filtered page on an index, a page under a row rule and a count over 10,000 rows each take about a millisecond, at tens of thousands per second across cores.
  • Writes queue. One caller creates a row in about 0.1 ms. Concurrent writers share SQLite’s single writer, so the total stays about the same and each waits its turn.
  • Time travel is fast. SQLite rebuilds an asOf list in one statement from the change log, and the connection caches it until something at or before that point changes. It used to take about a second on this data.
  • Deletes keep up. A delete also writes to the recycle bin, and still runs about as fast as a create with history. Writers waiting for the lock used to sleep up to 100 ms between tries; they now retry within a fraction of a millisecond, which made 32 concurrent deletes 2.5× faster (from 778/s) and updates 2× faster.

Beyond one machine

To run on several machines, use a swarm. One node leads and writes, every node keeps the same copy of the data, and a write counts once a majority of the nodes have it.

Each server, and each node of a swarm, keeps a data folder of its own: a second server started on a folder already in use stops with an error, so two never write one database file.

Since projects are separate files, spreading them across machines later is a routing problem (move a file, update a map), not a problem of joins across machines.

View as Markdown