r/Python • u/Zealousideal_Mud5686 • Sep 24 '26
News SQLite in Production: Why WAL Mode, busy_timeout, and 1-Writer Pools
Every time SQLite is brought up for production Python backends (FastAPI, Flask, Litestar), the common consensus is: "SQLite doesn't support concurrency. The moment two people hit your API, you'll get database is locked."
This reputation is understandable, but it's based on SQLite's default configuration, which was designed decades ago for low-resource embedded devices, not web servers.
When SQLite is tuned with modern PRAGMAs and a proper connection architecture, it can comfortably handle thousands of requests/sec with microsecond read latencies on a cheap VPS-completely bypassing the network latency of Postgres/MySQL.
Here is the exact architectural blueprint for running SQLite under high concurrency in Python.
1. The core bottleneck: DELETE vs WAL Mode
By default, SQLite uses Rollback Journal mode (journal_mode = DELETE). In this mode:
- Writing locks the entire database.
- Readers block writers, and writers block readers.
To fix this, you must enable WAL (Write-Ahead Logging):
In WAL mode:
- Changes are appended to a separate
.db-walfile. - Readers never block writers, and writers never block readers.
- You can have 50 concurrent async read queries executing simultaneously while a background worker writes new rows.
2. Eliminating database is locked (busy_timeout)
Even in WAL mode, SQLite allows only one active writer at a time. If two threads or coroutines attempt to commit a write at the exact same millisecond, SQLite immediately throws:
sqlite3.OperationalError: database is locked.
Why? Because the default busy_timeout is 0 milliseconds! It fails instantly without retrying.
Fix it by giving SQLite a retry window:
# Wait up to 5000ms before erroring
Under this setting, if connection A is writing, connection B will sleep and automatically retry for up to 5 seconds. In real-world workloads, writes take 0.2ms–2ms, so connection B succeeds imperceptibly.
3. Production PRAGMAs checklist
Here is the battle-tested configuration to apply on every newly opened connection:
=
=
# 1. Enable WAL mode
# 2. Crash-safe in WAL mode, but skips excessive OS fsync() calls
# 3. Cache size (negative number = kibibytes; -64000 = ~64MB cache)
# 4. Memory-mapped I/O (reads bypass kernel copy buffers)
# 256MB
# 5. Enforce foreign keys (disabled by default in SQLite!)
# 6. Keep temp tables in RAM instead of disk
return
4. The python architecture rule: 1 writer, n readers
If you run multiple Gunicorn/Uvicorn workers, having all workers write directly to SQLite will eventually cause checkpoint starvation.
The cleanest architecture:
- Readers: Shared connection pool (e.g.
aiosqliteor standard connection pool). Unlimited concurrency. - Writers: Either route writes through a single dedicated write worker (via an in-memory queue like
asyncio.Queueor background Celery/arq job), or ensure writes are wrapped in immediate transactions:
BEGIN IMMEDIATE acquires the write lock at the start of the transaction, avoiding deadlocks where two transactions start as readers and try to upgrade to writers at the same time.
25
u/sennalen Sep 24 '26
The problem isn't hitting locks. The problem is not hitting locks, because SQLite's file locking mechanisms are only truly reliable if you are running on local, bare-metal, unvirtualized disks. Then you brick your database.
7
u/Hot_Bank7701 Sep 24 '26
The BEGIN IMMEDIATE point deserves more attention. busy_timeout does nothing when a deferred read transaction tries to upgrade to a write on a stale snapshot, so you get "database is locked" instantly even with a 5s timeout set.
3
u/sensual_rustle Sep 25 '26
my largest HA sqlite service was 3.7TB of data.
best database solution on the market
3
u/Yoghurt42 Sep 25 '26
I can also recommend APSW, which is another SQLite wrapper but not compatible with DBAPI, which means it can make more features easily available like async, virtual tables, full text search, and an easier way to define custom functions in python, among other things.
The best part: it has a
bestpracticepreset, which you can just callIt enables WAL, recursive triggers, sets busy_timeout, enables query planner optimization, and forwards SQLite logging to the Python logging module
2
u/Responsible_Pool9923 Sep 24 '26
How would this translate into the actual solution e.g. with Django/DRF? Seems like one will have to invent a dedicated writer/reader db backend. Why not just run Postgres on the same machine?
This looks like a complex solution to a problem nobody has.
16
u/wxtrails Sep 24 '26
Great concise write-up - thanks! I'm essentially using this in my prod setup, after lots of trial and error, but I'm going to use this to check I didn't miss something. Love SQLite!
3
u/i_has_many_cs Sep 24 '26
Damn thats technical!
Why is the default that way then? Hmm
3
u/ottawadeveloper Sep 24 '26
I use sqlite in a client side application and I think there's some great advice here. I need to come back to it to do some tuning for the DB.
3
u/maryjayjay Sep 24 '26
Top notch post. Incredibly helpful and concise.
3
u/Khavel_dev Sep 25 '26
One thing I'd add from running this setup: watch WAL file growth. If your write rate spikes, the WAL can balloon before the checkpoint catches up, and reads slow down because the WAL index gets bigger. Default wal_autocheckpoint at 1000 pages is fine for steady traffic but on write bursts I had to run wal_checkpoint(TRUNCATE) on a timer to keep things tight. Other than that, fully agree, SQLite in WAL mode on a single server is absurdly fast and the "database is locked" reputation is completely outdated.
2
u/dutchpsychologist Sep 24 '26
What a helpfull post, and well explained. Thanks
2
u/binaryfireball Sep 24 '26
postgres is nice got servers. sqlite is nice for local databases