What made a SQLite job queue fast enough
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