r/sqlite

I've been building musql (pronounced "muscle"), a SQLite-compatible database written from scratch.

Repo: https://github.com/samyfodil/musql

It has a `database/sql` driver that registers as `sqlite`, so switching from another SQLite driver is usually just changing the import line. It uses its own storage format (`.musq`), with columns stored contiguously and an append-only delta for writes. `musql-convert` converts existing SQLite files in and out, in both directions.

SQL compiles to VDBE bytecode, the same design as C SQLite. A JIT turns hot paths into native code on amd64 and arm64 (Linux, macOS and Windows).

There's also `musqld`, a server that speaks Turso's Hrana protocol. `@libsql/client` and `libsql-client-go` connect unchanged, and there's a 5 MB Docker image. Replication supports CRDT or leader mode; you supply the network.

For performance, these are 100k-row read workloads, averaged over an amd64 and an arm64 machine:

• filtered `count(*)`: ~75x faster than C SQLite, ~210x faster than Turso
• sum over a filter: ~8x faster than C SQLite
• grouped aggregate: ~2.3x faster; `ORDER BY ... LIMIT`: ~2.4x faster

It also runs Doom. The same VM that runs SQL runs unmodified doomgeneric, compiled C -> LLVM IR -> VDBE bytecode. On an Intel i9 it gets ~38 fps on the bytecode interpreter and ~96 fps with the JIT. The idea came from Turso's demo. You can try it with:

It's early. I'd appreciate feedback on the API and the Hrana server, or a bug report if your SQL breaks. A failing query helps a lot.

I built a job queue on SQLite. The obvious concern is the single writer, so I benchmarked each step I used to work around it.

First: WAL.

On the queue workload, WAL was about 5-7x faster than DELETE journal mode.

Then I reduced the number of write transactions.

Claiming jobs in batches improved throughput by roughly 76-82% at concurrency 16.

The next step was a coordinator shared by all queues using the same database. Instead of every queue opening its own claim transaction, claims that arrive together are grouped into one transaction.

This matters most when there are many queues with low concurrency: each queue may only need one job, but the coordinator can still turn dozens of tiny write transactions into one. At higher per-queue concurrency, regular batching already captures most of that win.

That improved throughput by about 18% at concurrency 1, but only around 5% at concurrency 16.

The downside was transaction size.

With 256 queues and enough work available, one grouped claim could grow to 4096 jobs. Transaction p95 reached about 100 ms. That is bad both for another SQLite writer and, with a synchronous Node SQLite driver, for the event loop.

So I capped grouped claims at 512 jobs per transaction.

That reduced transaction p95 from ~100 ms to ~18-22 ms and competing-writer latency from ~63-68 ms to roughly ~10-22 ms, while keeping around 91-96% of the throughput of the huge transaction.

One more thing I tested was synchronous=FULL.

On heavily batched claim-only workloads, FULL and NORMAL were almost identical. On the complete end-to-end queue workload, FULL was still around 20-30% slower, mostly because completions are currently individual writes.

So the current shape is:

WAL, fewer transactions, group claims across queues, then put a hard bound on how large each write transaction is allowed to become.

Benchmarks and implementation:
https://github.com/unsady/walq

This shows real SQLite commands for JSON and running totals. I used SQLite 3.53.4 with made-up bakery data.

I made it with explainroo, a free tool I built. It's open source at https://github.com/vincentsch/explainroo