Why SQLite over Postgres (sometimes)

This is part of “Why this over that”, a series of short posts about technology choices I’ve actually made, with the reasoning written down and the reversal conditions stated honestly. Real decisions, not benchmarks.

I am, by any reasonable definition, a Postgres person. I’ve built Postgres-compatible servers, I write a whole series about its wire protocol, and I’m building a flight recorder for its incidents. And yet when I built Synq, my WhatsApp assistant, the database is SQLite, and it wasn’t a compromise. It was the right call, and the reasoning generalises.

The question that decides it

One question does most of the work: how many computers need to see this data at once?

Postgres is a server because it answers “many”: many app instances, many concurrent writers, one authoritative box they all talk to over the network. That architecture costs you a process to run, credentials to manage, a network hop on every query, connection pools (and their pathologies), backups as a discipline, and upgrades as an event.

Synq’s answer is “one.” One user, one server process, one box. Paying Postgres’s distributed-access tax for a single-process system is buying a bus to commute alone.

What SQLite actually gives you

Zero operations. The database is a file. It exists because the app opened it. There is no service to monitor, restart, or secure.
Backups you can reason about. Copy the file. For continuous protection, stream WAL changes to object storage with Litestream and you have point-in-time recovery for pennies, no replica to babysit.
Latency that changes your design. A query is a function call into your own process, microseconds instead of a network round trip. N+1 stops being a crime; whole classes of caching become unnecessary.
Deployment is copying. The app and its data move together. Dev-prod parity is total: production is the same file format as your laptop.

And it is not a toy: real SQL, real transactions, real durability, deployed on billions of devices, with a test suite that shames most databases.

Where the line actually is

Reversal conditions, stated honestly. Reach for Postgres when:

More than one process writes concurrently. SQLite serialises writers; WAL mode makes readers-plus-one-writer smooth, but heavy multi-writer concurrency is Postgres territory by design.
The app scales horizontally. Two app instances behind a load balancer means the file model is over (unless you adopt a replicated-SQLite layer, at which point you’re operating a database server again with extra steps).
You need the ecosystem. Extensions, logical replication, row-level security, the tooling universe: that gravity is real.
Many humans query it ad hoc. A shared analytical brain wants a server.

The failure mode in both directions is the same: defaulting. Postgres-by-default gives your weekend project a DevOps department. SQLite-by-default gives your growing SaaS a write-contention ceiling. The tool isn’t the identity; the access pattern is the decision.

The takeaway

Count the computers. One process owning the data: SQLite, and spend the saved complexity budget on the product. Many processes sharing the data: Postgres, and spend it on operating Postgres well.

Also in this series: Why SSE over WebSockets, Why Go for the service, Rust for the tool, and Why markdown files over a CMS.