SQLite in WAL mode is enough for most side projects
The service is a small feed reader: a few thousand rows a day, one writer, many readers. It has been running on a single SQLite file since 2023. The interesting part is how little of the usual database advice applied.
Two pragmas did most of the work. journal_mode=WAL lets readers
continue while a write is in progress, and synchronous=NORMAL
removes an fsync per commit without giving up crash safety in a way that
matters for this workload. Everything else — page size, cache size, mmap —
moved the benchmark numbers by a few percent and nothing else.
What broke was never the engine. It was a long-running transaction held open by a background job, which stopped the WAL from being checkpointed until the file grew to four gigabytes. Now every job has a timeout and the checkpoint is explicit.
Backups are VACUUM INTO to a temporary file, then restic to
object storage. Restoring takes eleven seconds, which is the only database
metric I actually care about.