SQLite queues need gentle writes

I love SQLite because it lets a small project remain honestly small. There is one file, no database server to babysit, and almost no setup between an idea and a working app. Then I add a background worker and continue thinking of app.db as my quiet little file, even though the web process and worker are now both trying to write to it. SQLite eventually reminds me that it has one writer lane, usually by making a user request wait at the worst possible moment.

Imagine a web server where users create records, change settings, and enqueue jobs. A worker reads the jobs table and writes results back to the same database. There is also a cleanup task because completed jobs cannot live forever, and for a while the whole setup is peaceful. Then the cleanup runs this delete:

DELETE FROM jobs
WHERE status = 'done'
  AND finished_at < datetime('now', '-7 days');

The table is larger than I remember or the useful index is missing, so the delete holds its write transaction longer than expected. Meanwhile a user request tries to insert another job:

INSERT INTO jobs (type, payload, status)
VALUES ('send_email', '{}', 'pending');

Now the request waits or fails with database is locked. My brain objects because the cleanup was “in the background”, as if that phrase creates another disk and a private set of locks. The database has no interest in this emotional model.

WAL helps, then the writer still matters#

The usual first step is WAL:

PRAGMA journal_mode=WAL;

WAL makes reads and writes coexist much more nicely, and I enable it on most apps like this. It does not create a second writer. One long write transaction can still block every other write, which becomes visible very quickly on a busy app. SQLite hides enough operational nonsense that I occasionally start treating it like a magic file that absorbs anything. A background worker still shares CPU, disk, tables, connection limits, and that single writer lane with user traffic. A bad five seconds can look very ordinary:

• 10:00:00 a user signs up
• 10:00:01 the cleanup worker starts
• 10:00:03 another user signs up
• 10:00:03 the request waits on SQLite
• 10:00:08 the request gives up

Nothing exotic happened. Two writers reached the same small door and one of them stood there long enough for the request to give up.

Make the writes less rude#

I would try smaller fixes before moving the app to Postgres. Postgres may become the right answer, but it also brings a server, backups, local setup, tuning, and the general life admin of running another thing. SQLite often just needs the worker to spend less time holding the door. I can batch cleanup work first:

DELETE FROM jobs
WHERE id IN (
  SELECT id
  FROM jobs
  WHERE status = 'done'
  ORDER BY id
  LIMIT 100
);

Add the index the cleanup query needs:

CREATE INDEX jobs_status_finished_at
ON jobs (status, finished_at);

Set a busy timeout so tiny collisions can wait instead of failing instantly:

PRAGMA busy_timeout = 5000;

I also keep transactions short and move slow computation outside them. The advice is almost disappointingly plain, but SQLite rewards plain habits much more than a clever queue abstraction.

For a while I would log slow writes and lock waits:

db_write table=jobs op=delete duration_ms=842 rows=100
db_busy route=/signup waited_ms=5000

Worker starts and finishes belong beside them:

job=cleanup_done_jobs started
job=cleanup_done_jobs finished duration_ms=3200 deleted=5000

When a request fails at 10:00:03, I want to see what else touched the database at 10:00:03. Otherwise I will spend the first fifteen minutes blaming the web handler because that is where the error appeared. SQLite can keep serving a small app very nicely after the worker arrives. I just have to remember that my private little file has become a shared road, and all writes still squeeze through one lane.