SQLite as a work queue, within reason

2026-05-21

SQLite gets dismissed as a toy in discussions about queues, usually by people who have not measured it. On one machine, with one writer, it is fast, durable, and dramatically simpler than operating a broker. The trick is to respect the constraints instead of pretending they are not there.

The shape that works

Use WAL mode, a generous busy_timeout, and a single process that owns writes. Claim work in a short transaction, mark it as leased with an expiry, and let a sweeper return expired leases to the queue. Keep payloads small; put large blobs on disk and store a path.

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA synchronous = NORMAL;

UPDATE jobs
SET leased_until = strftime('%s','now') + 60, attempts = attempts + 1
WHERE id = (
  SELECT id FROM jobs
  WHERE leased_until IS NULL OR leased_until < strftime('%s','now')
  ORDER BY created_at LIMIT 1
)
RETURNING id, payload;

The update-and-return pattern is atomic, which is the whole game. Workers can read concurrently; only the claim path needs to be serialized.

When to stop

SQLite stops being the right answer when you need multiple writers across machines, when the queue must survive the host, or when throughput is measured in tens of thousands of claims per second. Until then, it is a file, a backup, and a SELECT away from being understood.