SQLite Is a Production Database (For Your Homelab, Anyway)

Count the databases running in your homelab right now. If you are like most people, you will name Postgres and maybe a MySQL you keep meaning to migrate off, and you will be badly undercounting. Your bookmark manager has a SQLite file. So does your feed reader, your torrent client, your notes app, the media server indexing your films, the DNS filter logging every query, and the chat bridge you set up eighteen months ago and forgot about. SQLite is almost certainly the most-used database in your house, and you have probably never thought of it as a database at all. You think of it as a file, which is exactly the misunderstanding that leads people to reach for a heavyweight server the moment an app looks vaguely serious.

I want to argue the opposite. For the overwhelming majority of what a homelab actually does, SQLite is a production database, and treating it as one — configuring it deliberately, backing it up properly, understanding its limits — will save you a container, a port, a memory allocation and a whole category of two-in-the-morning failures. The catch is that SQLite ships with defaults chosen for compatibility with a 2004 world of shared hosting and flaky filesystems, so out of the box it behaves far more timidly than the engine underneath is capable of. Fix that, and a great deal of the case for a database server evaporates.

What “production” actually asks of a database

Before defending SQLite, it helps to be honest about what the word production is doing when people use it as an argument. Usually it smuggles in a set of assumptions that came from a day job rather than a homelab: dozens of application servers connecting over a network, hundreds of concurrent writers, a team that needs role-based access control, replication across availability zones. Those are real requirements for a business with a fleet of stateless web workers. Your Grafana instance and your recipe app have none of them.

What a single-machine self-hosted app genuinely needs from its database is durability (a committed write survives a crash), reasonable read performance, enough write throughput to keep up with one or two users clicking around, and a backup story that lets you recover after the SD card dies. SQLite delivers all four, and it delivers the first one more rigorously than a lot of people assume. Its transactional guarantees are ACID, its handling of fsync is careful to the point of pedantry, and it is the single most thoroughly tested piece of software most of us will ever run — the project maintains roughly six hundred times more test code than library code, and it is the database certified for use in aircraft avionics. The reliability question was settled a long time ago.

The performance question is where the defaults betray it. SQLite’s traditional journal mode takes a coarse lock during writes and forces readers to wait, which is where the folk wisdom that “SQLite can’t handle concurrency” comes from. That wisdom is describing a configuration almost nobody should still be running.

The one change that matters: WAL mode

If you take a single thing from this article, take this: turn on write-ahead logging. In the default rollback-journal mode, SQLite writes changes by first copying the original pages into a separate journal file, then modifying the database in place; a writer blocks all readers for the duration. Write-ahead logging inverts that. New changes are appended to a -wal file and the main database is left untouched until a checkpoint folds the changes back in. The practical consequence is that readers no longer block the writer and the writer no longer blocks readers. One writer at a time still holds the write lock, but everyone reading the database carries on unbothered, seeing a consistent snapshot from the moment their transaction began.

For a homelab workload, which is nearly always many reads and a trickle of writes, this is transformative. Here is the pragma block I apply to essentially every SQLite database I control, either through the application’s config or by opening the file once with the sqlite3 CLI:

1
2
3
4
5
6
7

-- Run once per database; WAL and page_size persist, the rest are per-connection.
PRAGMA journal_mode = WAL;        -- readers and the writer stop fighting
PRAGMA synchronous = NORMAL;      -- sync at checkpoints; safe in WAL mode
PRAGMA busy_timeout = 5000;       -- wait 5s for a lock instead of erroring instantly
PRAGMA cache_size = -20000;       -- 20 MB page cache (negative = kibibytes)
PRAGMA foreign_keys = ON;         -- enforce referential integrity (off by default!)
PRAGMA temp_store = MEMORY;       -- keep temp b-trees in RAM

Two of these deserve a word on the “why”, because copying pragmas without understanding them is how people corrupt data. synchronous = NORMAL is safe specifically in WAL mode: it means SQLite stops calling fsync on every single commit and instead syncs at each checkpoint. In WAL mode a power loss between checkpoints costs you nothing already-committed, because the WAL file is the source of truth and it is synced when it matters. This one line often multiplies write throughput several times over on a spinning disk and still gives you durability against application crashes. (The stricter FULL guards against a very narrow class of power-loss corruption on some filesystems; on a homelab box with a UPS, NORMAL is the sensible trade.)

busy_timeout is the fix for the other half of the “SQLite can’t do concurrency” complaint. Without it, a connection that finds the write lock held gives up immediately with SQLITE_BUSY, and the application surfaces that as an error. Set a timeout and the connection instead waits politely for the lock to free up, which under a homelab write load it always does within milliseconds. A great many “SQLite is flaky under load” bug reports are really “the developer never set a busy timeout”.

Concurrency, honestly

Let me be precise about the limit, because pretending it does not exist is how you get burned. SQLite allows exactly one writer at a time per database. WAL mode lets that writer run alongside any number of readers, but two transactions cannot both be writing at the same instant; the second waits for the first. This is a genuine ceiling, and it is worth knowing where it sits.

In numbers, a modern SSD will happily push SQLite through several thousand small write transactions per second in WAL mode, and tens of thousands of reads. The write ceiling only becomes a wall when you have many independent processes all trying to commit constantly — a busy multi-tenant SaaS, an event ingestion pipeline, a job queue hammered by fifty workers. A homelab app with one household poking at it commits a few writes a minute at its busiest. You are three or four orders of magnitude below the ceiling. The concurrency limit is real and it is also irrelevant to your actual workload, and holding both of those thoughts at once is the whole skill.

One practical corollary: keep write transactions short. The single-writer rule means a transaction that stays open for two seconds while the application does unrelated work is two seconds during which nothing else can write. Batch related writes into one transaction, do the work, commit promptly. If an app is genuinely serialising badly, that is nearly always the cause rather than SQLite itself.

Backups: the part people get wrong

The “it’s just a file” mental model produces exactly one dangerous habit, which is copying the file with cp while the application is running. In WAL mode the live database is spread across the main file and the -wal file, and a naïve copy can catch them mid-checkpoint and hand you a corrupt snapshot. Do not do it. SQLite gives you correct online backup tools, and they are one line each:

1
2
3
4
5

# Consistent snapshot of a live database — safe while the app is running.
sqlite3 /var/lib/myapp/app.db ".backup '/backups/app-$(date +%F).db'"

# Or the streaming equivalent, handy for piping straight to compression:
sqlite3 /var/lib/myapp/app.db ".dump" | gzip > /backups/app-$(date +%F).sql.gz

.backup uses SQLite’s online backup API to produce a transactionally consistent copy without stopping the writer; .dump emits SQL you can re-import anywhere. For anything you would actually miss, run one of these on a schedule and copy the result off the machine. If you want continuous, point-in-time protection rather than nightly snapshots — the sort of thing where losing the last hour of writes would hurt — that is precisely the job Litestream does for SQLite, streaming the WAL to object storage as it is written. The wider strategy of dumps versus WAL shipping versus point-in-time recovery is worth understanding across every engine you run, and I have laid out how to think about database backups properly elsewhere.

When SQLite is genuinely the wrong tool

I am not selling a religion. There are workloads where you should reach for Postgres, and knowing them keeps this argument honest.

The clearest case is multiple machines needing to write to the same database over a network. SQLite is an in-process library; it has no server, no wire protocol, no listening port. If two containers on two hosts must both write, you want a database server, full stop. A network filesystem does not rescue you here — SQLite over NFS or SMB is a documented way to corrupt data, because the locking primitives it relies on are unreliable across the network.

The second case is heavy concurrent write contention on a single box: a busy job queue with many worker processes, high-rate telemetry ingestion, anything where you genuinely have sustained parallel writers. You will hit the single-writer ceiling and feel it. The third is when you actively want features that live in a full server — rich role-based permissions, logical replication, sophisticated full-text and vector search at scale, LISTEN/NOTIFY, stored procedures in a real procedural language. If your app leans on those, use the tool built for them, and while you are there consider tuning that Postgres so it uses the hardware you gave it.

Everything outside those three cases is fair game for SQLite, and that is most of a homelab.

Troubleshooting

database is locked / SQLITE_BUSY under normal use. You have no busy_timeout set, or the app opens it with one. Set PRAGMA busy_timeout = 5000 on every connection. If it persists, something is holding a write transaction open far too long — hunt for a transaction that begins, does slow unrelated work, and only then commits.

Writes feel slow on a Raspberry Pi or SD card. Two culprits. First, confirm WAL mode actually stuck: PRAGMA journal_mode; should return wal, not delete. It silently refuses to enable on some network filesystems. Second, cheap SD cards are dreadful at the small synchronous writes databases make; synchronous = NORMAL helps enormously, and moving the database to a USB SSD helps more.

The -wal file grows to gigabytes and never shrinks. Checkpoints are not completing, usually because a long-lived read transaction is pinning the WAL — a connection that opened a transaction and never finished it. Find and close it. You can force a checkpoint with PRAGMA wal_checkpoint(TRUNCATE);, but the leaked reader will just let it grow again until you fix the real cause.

Corruption after a copy or a crash. If you copied the file live with cp, that is the bug; use .backup. To assess damage, run PRAGMA integrity_check; — a clean database returns ok. If it reports errors, recover what you can with .recover from the CLI, which reconstructs a new database from the readable pages.

Migrations from another tool “lost” the foreign keys. Foreign key enforcement is off by default in SQLite for historical compatibility. If your schema relies on it, every connection must issue PRAGMA foreign_keys = ON, and many libraries do not do this for you.

Is it worth it?

For a single-machine app with a household’s worth of users, SQLite in WAL mode earns its place as the better engineering choice, and reaching for a server buys you complexity you will never use. There is no daemon to supervise, no port to firewall, no connection pool to size, no separate backup process to babysit; the database lives beside the application, starts when it starts, and moves when you rsync the directory to a new host. The reliability is settled, the performance under a realistic homelab load is excellent once you turn on the three pragmas that matter, and the failure modes are few and well understood.

Reach for Postgres when you truly have multiple machines writing, sustained parallel write contention, or a hard dependency on server-only features. Everywhere else, resist the reflex to spin up a database container because the app “feels important”. The most important database in your homelab is probably already SQLite. Configure it like you mean it, back it up like it matters, and let it do the job it is quietly very good at.