sqlite is not a toy database

the reputation problem

sqlite gets dismissed. "it's not a production database." "it doesn't scale." "use postgres for anything real."

this is wrong. sqlite is the most widely deployed database engine in the world, running in every browser, every mobile OS, and most embedded systems. dismissing it reveals a misunderstanding of what it is.

what sqlite actually is

sqlite is a library, not a server. your application links against it and it reads/writes a single file on disk. there is no network, no auth, no connection pooling — because there is no server.

this is a feature, not a limitation.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -64000;  -- 64MB page cache
PRAGMA temp_store = MEMORY;

with WAL mode and these pragmas, sqlite handles thousands of reads per second and hundreds of writes per second on commodity hardware.

the b-tree

sqlite stores all data in a single file using a B-tree structure. each table is a B-tree. each index is a B-tree. pages are 4096 bytes by default.

the file format is so well-designed that it's used as an archival format — the US Library of Congress recommends it for digital preservation.

when to actually use postgres

• you need multiple writers from different machines simultaneously
• you need row-level security managed by the database
• your dataset exceeds available disk on a single machine

everything else? sqlite is probably fine. your SaaS tool probably doesn't need postgres until you have 100,000 active users.