SQLite is simple until it has a queue behind it

I like SQLite a lot.

One file. No server. No Docker container running just because I wanted to store five rows. For small projects, it feels very peaceful.

But SQLite is still a database. It has rules. The rule that becomes visible first is usually this one: there can only be one writer at a time.

That is fine until a background worker joins the app.

the small app version#

Imagine a tiny app like this:

browser -> web server -> app.db
                         ^
                         |
                    worker process

The web server writes when users do things. The worker writes when jobs finish. Maybe there is a cleanup job too.

For a while, everything is fine. Then the worker runs a delete:

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

If that table is bigger than expected, or the right index is missing, the delete can take longer than you thought.

While that transaction is open, a request tries to insert a new job:

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

The request can then fail with:

database is locked

WAL helps, and the writer rule still stays#

The normal advice is to enable WAL:

PRAGMA journal_mode=WAL;

This is good advice. WAL mode lets readers and writers work together better. The writer side still has one-at-a-time behaviour, so long write transactions can still block other writes.

That part is easy to forget because WAL fixes enough pain that it starts feeling like magic. It mostly gives better behaviour for common cases.

background work still uses foreground resources#

I think the trap is the word “background”.

It sounds separate. It sounds like the worker is doing its own thing far away from user requests.

But the worker is still using the same database file. If it holds a write transaction for too long, the user request can feel it.

A simple bad moment can look like this:

10:00:00 user signs up
10:00:01 worker starts cleanup
10:00:03 another user signs up
10:00:03 request waits on SQLite
10:00:08 request fails

A simple lock conflict caused the whole thing. Two writers reached the same small door at the same time.

what I would try first#

I would try a few smaller fixes before moving to Postgres. Sometimes Postgres is the right answer, but it is a larger operational step.

First I would make writes shorter.

Batch cleanup work:

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 a tiny collision can wait instead of failing instantly:

PRAGMA busy_timeout = 5000;

And keep transactions small. That rule sounds boring, but it matters a lot here.

what I would log#

I would log slow writes for a while:

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

I would also log when workers start and finish:

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

The goal is simple: when a request fails at 10:00:03, I want to know what else happened at 10:00:03.

Without that, I am just guessing.

my rule#

SQLite is great when the write path is simple.

When I add a queue, cron job, importer, webhook worker, or cleanup task, I stop thinking of it as “just a file”. It is now a small shared system.

It can stay simple and lovely, as long as I remember the coordination part.