Say you are using SQLite in production and someone will tell you it is a development database. That reputation comes from a real place, and it is also mostly wrong for the shape of application most businesses actually run. Five restaurant platforms we operate in Rabat sit on SQLite, and the decision has cost us nothing so far. This is the honest version of when that works.
What these applications actually do
A restaurant platform serves a public menu, takes reservations, runs QR ordering at the table and drives an owner back office. The read volume is modest by internet standards: a few hundred people looking at a carte on a Friday evening. The write volume is smaller still: reservations, orders, status changes. There is exactly one application server. There is no analytics warehouse hanging off the side.
That workload profile is the whole argument. SQLite is not slow. On a single machine it is frequently faster than a client-server database, because there is no network hop and no connection pool: a query is a function call into a file the process already has open. The reason people reach for PostgreSQL is rarely raw speed at this scale. It is concurrency, replication and operational tooling.
The write lock is the real constraint
SQLite allows many concurrent readers and one writer at a time. In write-ahead-logging mode, readers do not block the writer and the writer does not block readers, which removes most of the pain. What remains is that two writes serialise. For a restaurant taking an order every few minutes, serialising writes is not a constraint you can measure. For a marketplace taking a hundred orders a second, it is the end of the conversation.
The practical test is not "how big is my data" but "how many concurrent writes at peak". Data size matters far less than people expect: SQLite is comfortable with databases in the gigabytes. It is sustained write concurrency that decides it.
What you give up, stated plainly
You lose horizontal scaling. There is one file on one machine, so you cannot add a second application server later without changing database. You lose managed replication and point-in-time recovery unless you add tooling for it. You lose the deep operational ecosystem: the connection poolers, the query analysers, the hosted backup services that come free with a managed PostgreSQL instance.
None of those are hypothetical, and if the product roadmap points at multi-region or a second app server, choosing SQLite is choosing a migration later. We say that to clients before we build, not after.
What you gain, which is more than speed
Deployment becomes trivial. There is no database server to provision, secure, patch or pay for. A backup is a file copy, and a restore is a file copy in the other direction, which means the disaster recovery story is something a non-technical owner can actually follow. Local development is identical to production because there is no environment-specific database configuration to drift.
That last point matters more than it sounds. A whole class of bug, the one where staging and production disagree because their databases were configured by different people on different days, simply does not exist.
The rules we follow
Turn on write-ahead logging, always. Set a busy timeout so a concurrent write waits briefly instead of failing immediately. Keep the database file outside the deploy directory so a deployment can never overwrite it, which is the single most dangerous mistake available here. Back up on a schedule to somewhere off the machine, because a file on one server is one disk failure from gone.
One more, learned the hard way across five deployments: never alter a table in the same migration that creates it. Write the schema you want, and add new columns in a separate, later migration that checks for their existence first. Mixing the two produces a migration that works on a fresh database and fails on every existing one, which you discover in production and not before.
How to decide
Ask three questions. Will there be more than one application server, ever? Do you need concurrent writes in the tens per second at peak? Does anything require replication or point-in-time recovery? If all three are no, SQLite will serve you well and cost you less to operate. If any is yes, use PostgreSQL and do not look back.
The failure is not choosing SQLite. It is choosing it without asking those questions, then discovering the answer under load. We size this per project as part of backend and cloud work, and we would rather talk a client into PostgreSQL on day one than migrate them in year two.