SQLite vs Postgres: A Decision You Can Defend

A database choice you cannot explain will be reversed in a panic. SQLite versus Postgres is not a personality test. It is three numbers: how many hosts write, how many writes per second you must accept without a queue, and how much operational surface you are willing to run on one VPS. Write those numbers down. The engine follows.

Both are excellent. Postgres is a server with users, roles, replication, and a planner that has seen every join shape. SQLite is a library with a file, WAL, and almost no moving parts. The wrong one is the one whose failure mode you did not budget for.

Compare the actual jobs, not the logos

On a single Netbay VPS in Lucknow DC01, Intel Xeon Platinum and High-Speed SSD, both engines run well. The difference is the process model.

SQLite lives in your process. There is no extra systemd unit, no listen address, no password rotation for a database role, no autovacuum to tune. Crash recovery is opening the file. The cost is one writer at a time and no network replica as a built-in.

Postgres lives as a daemon. Your app talks TCP or a Unix socket. You get concurrent writers, MVCC that does not stall readers the same way, LISTEN/NOTIFY, JSONB with GIN, and a replica story that is documented to death. The cost is memory, a second failure domain, minor-version upgrades, and WAL of a different kind that you must archive if you want point-in-time recovery.

If the app is one service, one host, reads mostly, and you value a file you can copy, SQLite is the defendable default. If the app is already three workers that all write, or you know a second host is coming this year, Postgres is the defendable default. Starting on SQLite and migrating later is valid when the schema stays boring (integers, text, timestamps) and you do not lean on Postgres-only features on day one.

A table you can paste into a design doc

Use this as the actual argument, not as folklore.

• Hosts that write: 1 means SQLite is eligible. 2 or more means Postgres (or a queue into one writer).
• Peak committed writes: a few hundred per second on one VPS is still SQLite-with-WAL territory for simple transactions. Sustained thousands with contention is Postgres.
• Read replicas: SQLite restore is a file. Postgres streaming replica is a running server.
• Multi-user roles and row-level security: Postgres.
• Embedded, edge, CLI, single binary: SQLite.
• Team already runs Postgres well: do not introduce SQLite as a second religion unless the embedding is the point.

JSON in SQLite is a function family on text. JSONB in Postgres is a type with indexes. If your product is a document store with ad-hoc keys, Postgres will hurt less. If JSON is a payload blob you never query, SQLite TEXT is fine.

Migration is a project, not a weekend

Moving off SQLite is dump and reload plus type cleanup. INTEGER PRIMARY KEY stays. BOOLEAN becomes integer in SQLite and boolean in Postgres; your app should already treat it as 0/1 or true/false explicitly. Timestamps as ISO text in SQLite become timestamptz if you were careful, and a mess if you mixed local time strings. DATETIME in application code, UTC in the file, is the portable rule.

.headers on
.mode csv
.once /var/lib/myapp/export/events.csv
SELECT id, account_id, kind, payload, created_at FROM events;

psql -d myapp -c "\copy events(id, account_id, kind, payload, created_at) FROM '/var/lib/myapp/export/events.csv' CSV HEADER"

Keep ids stable. Turn off triggers during load. ANALYZE in SQLite, ANALYZE in Postgres after the load. Then run the same integration tests against both. If you cannot, you do not have a migration, you have a hope.

The reverse migration, Postgres to SQLite, is rarer and usually means you over-provisioned. It is valid for a tool that must run offline. Do not do it to save RAM on a VPS that already has enough.

A decision you can defend sounds like this: we have one host, one writer, 80 writes per second at peak, and we will copy the file off-site. That is SQLite. Or: we have two app hosts, concurrent checkouts, and we need LISTEN. That is Postgres. Fashion is not a reason.

Write the three numbers, pick the engine they imply, and keep the schema portable until the numbers change. You can spin up an Ubuntu 24.04 instance on Netbay in under 60 seconds and follow along — netbayhosts.in.