Confession: for about four years my default answer to “what database should I use?” was just “Postgres.” I didn’t even pause. Postgres for the side project. Postgres for the production app. Postgres for the tiny internal tool that three people use on a Tuesday. I’d spin up a managed instance, eat the $25/month, wire up the connection pool, and call it done.
Then last summer I sat down to ship a small thing for a client. The thing was going to handle maybe 40 writes a day and a few hundred reads, and I caught myself reaching for the same setup. Managed Postgres. Connection pool. Two failover replicas I didn’t need. And I just stopped. I asked a question I should’ve been asking for years: does this actually need Postgres?
A lot of the time the answer is no. Sometimes the answer is yes, and the question reveals why. Either way, the fact that I’d stopped thinking about it was the problem. So I went back to first principles. I read the SQLite docs end to end. I tried Litestream for a few projects. I ran one production workload on each side. Here’s what I actually use now, six months later.
My one-line rule
If the data fits on one machine, and the machine can do all the writes, I start with SQLite. If either of those things is going to stop being true within the next year, I start with Postgres.
That’s it. Everything else in this post is the unpacking.
The reason it works is that “one machine doing all the writes” covers more apps than most of us admit. A blog. An internal dashboard. A B2B tool with 200 users. A booking system for a single venue. A CRM for a 10-person sales team. Most of the things I’ve shipped in the last two years fit cleanly inside SQLite, and most of them would have hummed along on a $5 droplet with a single binary and a file.
The SQLite team has a page literally called “Appropriate Uses For SQLite” that says the same thing in more words. The rule of thumb they give is roughly “if your application traffic is under 100K hits a day, SQLite is fine,” and they note plenty of sites do many times that. Their numbers are conservative on purpose, and the conservative number is still big.
Where Postgres earns the slot
Postgres earns it for me when one of these is true:
The first is concurrent writers from independent processes. SQLite’s writes are serialized at the database level. WAL mode lets readers and one writer go in parallel, but the moment I have two web workers both writing to the same table on a hot path, I’m going to start seeing contention. Could I shove all writes through a single queue? Sure. But at that point I’m building a Postgres-shaped thing with extra steps.
The second is needing the server itself to be the source of truth across machines. If I’m running app servers in three regions and they all need to read and write the same data, the SQLite-as-file model gets ugly fast. Litestream and rqlite and Turso are real, and I’ve used them, but each adds a layer of “you’d better understand how this fails.” Postgres on a single primary with a read replica is boring and well-documented.
The third is rich types. If I’m leaning hard on jsonb, full-text search with tsvector, arrays, ranges, or geographic types via PostGIS, SQLite gives me a much thinner story. SQLite has JSON functions and FTS5, and they’re fine, but they’re not what Postgres has. I wrote about how I actually use jsonb in my Postgres jsonb post — if the patterns there match your workload, just pick Postgres and move on.
A concrete shape: the “small SaaS” case
Let me get specific because this is where I used to default badly. Picture a SaaS app: maybe 50 paying customers, each with a few users, total user count under 500. Reads vastly outnumber writes. Background jobs that fire every few minutes. A normal CRUD shape with maybe 20 tables.
Old me, in 2023:
# docker-compose.yml
services:
app:
image: my-app:latest
depends_on: [db]
db:
image: postgres:16
volumes:
- pgdata:/var/lib/postgresql/data
pool:
image: edoburu/pgbouncer
Three services to keep happy. A connection pool to tune. Backups via pg_dump cron job. Restore drills I never actually ran.
New me, in 2026:
# app.py
import sqlite3
from contextlib import contextmanager
DB_PATH = "/data/app.db"
def init_db():
conn = sqlite3.connect(DB_PATH)
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA synchronous=NORMAL")
conn.execute("PRAGMA foreign_keys=ON")
conn.execute("PRAGMA busy_timeout=5000")
conn.commit()
conn.close()
@contextmanager
def db():
conn = sqlite3.connect(DB_PATH, timeout=5)
conn.row_factory = sqlite3.Row
try:
yield conn
conn.commit()
except Exception:
conn.rollback()
raise
finally:
conn.close()
Four PRAGMAs and a context manager. The first three are not optional, by the way. journal_mode=WAL is what makes SQLite stop blocking readers during writes. synchronous=NORMAL is a sensible default when you have a battery-backed disk or you’re okay losing the last second of writes on a hard crash. busy_timeout is what stops you from getting random “database is locked” errors when two requests land in the same millisecond.
Pair that with Litestream replicating the file to S3 every second and I have a database, point-in-time recovery, and an off-box backup for the price of one binary. The Litestream docs are tight and the getting-started guide is the only thing I needed to read.
What changed for me technically
A few things had to land before I trusted SQLite for “real” work.
WAL mode I already mentioned. Without it, SQLite is genuinely worse for concurrent web apps. With it, a single writer and many readers can run in parallel without blocking each other, which is what most web traffic actually looks like.
PRAGMA synchronous=NORMAL was the second one. The default FULL does an fsync on every transaction, which is brutal on throughput and overkill for most apps. NORMAL only fsyncs at WAL checkpoints. The trade-off, from the SQLite docs on PRAGMA synchronous, is that you can lose the last transaction or two on a power loss but the database itself can’t corrupt.
Strict tables came in 3.37 and they fixed the type-affinity foot-gun. Use CREATE TABLE foo (id INTEGER PRIMARY KEY, email TEXT NOT NULL) STRICT; and SQLite will actually enforce that email is text, not silently accept a number. If you’re writing fresh code in 2026 and not using STRICT, you’re opting into a sharp edge for no reason.
And the ecosystem caught up. Bun ships with a native SQLite driver. Drizzle works against SQLite with the same query API as Postgres. Rails has had it forever. If you’re on the JS side, the move from pg to bun:sqlite is maybe ten lines.
What hasn’t changed: when I still pick Postgres without thinking
A couple of real cases.
When a client asks for “the standard stack” because they want to hire help later, Postgres is the safer answer. The pool of devs who can be productive on Postgres on day one is bigger than the pool who’d be comfortable debugging a Litestream replication lag at 2am.
When the schema is going to lean on Postgres-specific things like jsonb indexes, generated columns with rich expressions, materialized views, listen/notify for pub-sub, or pgvector for embeddings, I just pick Postgres. Trying to bend SQLite into those shapes is the kind of work where you save money on hosting and lose it back on engineer-hours.
When the writes really do come from many processes. Background workers, webhook handlers, scheduled jobs, an admin panel, a public API. If they all write independently and contention isn’t theoretical, SQLite WAL will hit its limit and you’ll spend an afternoon debugging “database is locked” before doing the migration anyway.
When you’re not sure. If I’m honestly not sure whether the app is going to grow beyond one machine in the next year, Postgres is the cheaper insurance. A migration from Postgres to SQLite later is rare (and easy if you wanted it). A migration from SQLite to Postgres later is common and annoying.
The hosting math, briefly
This isn’t the main reason to pick either side, but it’s a real factor.
For a small app, a managed Postgres at AWS RDS or Render starts around $25-40/month for something usable, and that’s before you’ve added a replica. A single-binary app with SQLite on a $5/month VPS, replicated to a $0.50/month S3 bucket via Litestream, will hold up for a workload that would have cost you $40 in managed Postgres. Over a year that’s a couple of hundred dollars of pocket money on a project that might not be earning much.
I’m not religious about this. I cover the build side of small commercial projects on my portfolio’s work page, and most of them would happily run on either. But the cost gap is real, and it makes the “default to Postgres” reflex worth checking.
What I’d do this week if I were starting fresh
Pick the smallest internal tool you have running on managed Postgres. Look at the write rate. If it’s under, say, ten writes per second at peak, copy the schema, point it at SQLite with WAL mode and synchronous=NORMAL, run it on a $5 box with Litestream pointed at S3, and let it sit for a month. You’ll learn more about your actual workload from that one experiment than from another ten blog posts about benchmarks.
If it breaks, you’ve learned where your write contention actually is, which is information you’d want for the Postgres setup anyway. If it doesn’t break, you’ve got a tiny database that runs forever for less than the price of a coffee.
I default to SQLite now. Not always. Not even most of the time on client work. But often enough that I notice the times I don’t, which is the actual win. Defaults that you’ve never questioned are worse than defaults you’ve thought about and kept.
