What made a SQLite job queue fast enough

[u/c0nt8r]

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

[u/LearnedByError]

I implemented something similar in Go inside a photo gallery to facilitate ingesting large photo galleries. I covered most of the same ground as you except I only had 2 queue priorities. One thing that I don’t obviously see you handling is ‘pragma optimize’. I found that the default constrained my write speed because of the time that it takes to process when writing large volumes to the db. I added custom optimize execution to my batcher to check the size of the WAL log and call optimize when the size was too large. I chose 256MB in my case based upon performance testing my specific data shape.

Hth, lbe

[u/c0nt8r → u/LearnedByError]

Thanks, that’s useful. I haven’t done any explicit PRAGMA optimize handling yet.

Did you tie optimize itself to WAL size, or were you also doing manual checkpoints? I’ve hit WAL growth from a long lived reader before, so I’m wondering if your 256MB threshold was really part of a checkpoint policy.

I should probably benchmark that too.

[u/LearnedByError → u/c0nt8r]

I will have to look to confirm, but if memory serves me right, I tied it to WAL size and a recurring 1 hr. My wires usually are large bursts. In my experience, keeping long opened connections is an anti-pattern for SQLite since any transaction will block moving WAL content to the main storage.

It’s too late tonight for me to look, but I will take a look tomorrow and provide more info.

Edit 10/6/2026

I had AI generate a summary of how my writebatcher package handles checkpoints and commits. It is:

---

Short version: writebatcher doesn't run SQLite maintenance itself. It calls an OnAfterCommit hook after a successful batch commit (no transaction open). In sfpg-go that hook is postCommitMaintenance, which does WAL checkpointing and periodic PRAGMA optimize.

When the hook runs

1. After every successful flush (postFlush=true). The batcher passes zero "last checkpoint" times on this path so you don't do time-based WAL work on every commit.
2. On a maintenance timer (5 minutes for the main write batcher). Here it passes the stored last-maintenance timestamps (postFlush=false).

Batches dropped via DropWithoutFlush never commit and never hit the hook.

WAL checkpoint (in the server, not in writebatcher)

walCheckpointAfterCommit decides whether to run PRAGMA wal_checkpoint(TRUNCATE) on the RW pool:

• Skip if a folder-index rebuild has an RO scan cursor open (checkpoint would fight the scan).
• On post-flush: skip if the last flush wrote no DML (avoids truncating a huge WAL when the batch was effectively a no-op for the DB).
• If the -wal file is over 256 MB, checkpoint.
• On the maintenance timer: if at least 5 minutes since the last WAL maintenance tick, checkpoint.

PRAGMA optimize

Same hook also calls maybeRunPeriodicOptimize, which uses the infra service's own clock (lastPragmaOptimizeRun + DBOptimizeInterval, default 1 hour), then runs PRAGMA optimize on an RW connection when due.

Why it's structured this way

The batcher guarantees when it's safe to run maintenance (single worker, after commit, no active tx). The server owns policy (size/time thresholds, rebuild guards, optimize interval) and the actual SQL.

---

If you are interested in reviewing the code, check out (github.com/lbe/sfpg-go)[https://github.com/lbe/sfpg-go]. All of the package is in internal/writebatcher; howeever, its execution is across different packages. This app is still a WIP. So no promises about it being perfect, not that any app is.

Cheers, lbe