SQLite Is All You Need

I read Prefer STRICT tables in SQLite by Evan Hahn last week, and it got me thinking. Here is someone treating SQLite as a database you put real, typed, production data into. Which raises the obvious question: how far does that actually go? So we found out. We built a social network on SQLite, 50,000 users and a million posts in a single 343MB file, put it behind a Node API, made every table STRICT on Evan's advice, and hammered it until the numbers stopped being flattering. This post is what came back: the benchmarks, the config, the code, and the places it falls over. The short version: one file, one process, and the heaviest page in the app served 315 million requests a day on a laptop. For almost anything you are building, SQLite in WAL mode is enough, and the Postgres container you spun up out of habit was never needed. If you want the evidence rather than the argument, skip straight to the benchmarks. The limits are here too, and they are real. What we built A social app is a good stress test because the hard query is unavoidable. A home timeline has to join what you posted against who I follow, sort by time, and count likes. You cannot cache your way out of it on the first request, and it gets slower as the graph grows. So: Chirp, a social network that lives in one file. TableRowsusers50,000posts1,000,000follows2,498,799likes4,999,764Total343 MB in one file Every table is STRICT, which we will come back to. The whole backend is one Node process talking to chirp.db sitting next to it. No database server, no connection string, no port 5432, no container. The entire production configuration is five pragmas:

That is the whole setup. There is no step where you provision anything. The numbers Everything below ran on an Apple M1 laptop, 8 cores, 16GB of RAM, on Node 22 with better-sqlite3 (SQLite 3.49.2).

Not a server. A laptop. First, end to end over real HTTP: a real Node server, real sockets, real JSON serialization, 50 concurrent connections, autocannon on the other end. This is the whole stack, not the database in isolation. Every figure is the median of four runs, and run-to-run variance is around 10%. Endpointreq/sp50p99ErrorsGET /post/:id (point read)51,4270ms1ms0GET /u/:handle (profile)47,7760ms2ms0GET /timeline/:id (the heavy one)3,54313ms27ms0Mixed: 95% timeline reads, 5% writes3,65413ms27ms0 Sit with the last row for a second. That is the worst query in the app, the one that joins two and a half million follows against a million posts and counts likes on every result, served alongside live writes, from a single Node process, on a laptop. 3,654 requests per second is 315 million requests a day. On the heaviest endpoint. The point read would do 4.4 billion. If your product serves more than 315 million timeline loads a day, you are not in the 99.99%, and you already know who you are. Everyone else is arguing about connection poolers for a workload that fits in a file. Underneath the HTTP layer, the raw query numbers look like this. Each benchmark runs in a fresh process against a fresh copy of the database, three times, and we take the median. Queryops/sp50p99Point read (post by id)232,0110.004ms0.007msProfile page (two aggregates)175,8500.005ms0.007msHome timeline (20 posts + like counts)4,2470.232ms0.337msInsert a post (one transaction

each)23,4590.012ms0.089msLike a post (one transaction each)12,6180.016ms0.243msInsert posts (batched, 100 per transaction)32,217 rows/s Every write there is a real, committed, durable transaction. Not a batch, not a buffer, not a queue. Twenty-three thousand committed transactions a second, on a laptop, with foreign keys on. WAL is the part that matters The old objection to SQLite is that it locks. One writer takes the database, everyone else waits. That objection is about the rollback journal, which has been the wrong default for most apps since 2010. Write-Ahead Logging changes the shape of the problem. The writer appends to a log instead of mutating pages in place, so readers keep reading the last committed snapshot while a write is in flight. Readers do not block the writer. The writer does not block readers. We tested it instead of asserting it. Seven reader threads run the timeline query. First alone, then next to a writer doing a realistic 1,000 writes per second, then next to a writer going flat out. We ran the identical test in WAL and in the old rollback journal mode so the difference is visible. journal_mode = WAL Scenarioreads/sp99 readworst readSQLITE_BUSYReaders only17,5811.53ms7ms0Readers + 1,000 writes/s2,7924.40ms17ms0Readers + writer flat out (14,838 w/s)2,8545.16ms30ms0 journal_mode = DELETE (the rollback journal, the thing people remember) Scenarioreads/sp99 readworst readSQLITE_BUSYReaders only18,4391.23ms6ms0Readers + 1,000 writes/s497133.85ms794ms0Readers + writer flat out (2,806 w/s)227586.02ms1,762ms0 Same query, same data, same machine.

One pragma. With a writer running at a thousand writes a second, WAL serves 5.6x the read throughput and holds its 99th percentile at 4.40ms. The rollback journal collapses to 133ms at p99, with individual reads stalling for nearly eight hundred milliseconds. That is the SQLite people complain about, and it is a database from a decade ago. Under WAL, across every scenario, zero SQLITE_BUSY errors. Not "few." Zero. Where the numbers stop If this post only had the good tables in it, you should not trust it. Here is what we found that does not flatter SQLite. Reads stop scaling once anything writes. Seven reader threads with no writer do 17,581 reads/s. Add a writer doing only 1,000 writes/s and reads drop to 2,792. That is a 6.3x fall, and it does not get meaningfully worse if you go to 14,000 writes/s, which tells you it is not about write volume. It is about cache invalidation. Those readers were getting their speed from a 256MB memory-mapped window and a warm page cache. Every commit invalidates mapped pages, so readers fall back to real I/O and re-validation. We confirmed it by sweeping the config: with mmap off, the read-only number drops from 17,069/s to 6,034/s and the penalty from a concurrent writer mostly disappears. The 17,581 figure is a read-only artifact. The honest mixed-workload number is around 2,800 reads/s of the heaviest query per machine, and that is the one we built the argument on. One writer, globally. SQLite takes a single write lock for the whole database. Writes do not run in parallel, they queue. At 23,000 committed transactions a second that queue drains fast, but it is a queue, and no amount of hardware makes it two queues. One machine. There is no failover. If the box dies, you are down until it comes back, and your recovery time is however long it takes to restore a file. For a lot of products that is a completely acceptable trade for the operational simplicity.

For some it is not, and if you are in a regulated industry with an uptime SLA, you already know which one you are. Reach for Postgres when you have many writers contending on the same rows, when you need read replicas or automatic failover, when you need a real analytics engine over hundreds of millions of rows, or when your team genuinely needs the extension ecosystem. Those are real reasons. "We might scale one day" is not one of them, and it is the reason most of these containers exist. "But you tested on a laptop" Fair. An M1 is not a fast machine by 2026 standards, but it is not a $6 VPS either, and the whole argument falls apart if these numbers only exist on Apple silicon. We did not rent the boxes and re-run this, so what follows is an estimate, and it is labelled as one. But it is a more constrained estimate than it looks, because of something our own numbers already told us. The Node server is single-threaded. That 3,543 req/s came from one core. And remember the concurrency result: seven reader threads next to a writer did 2,792 reads/s, which is less than one thread on its own managed. Once writes are in the mix, this workload plateaus at roughly one core's worth of throughput no matter how many cores you throw at it. So the question "how fast is this on a cheap VPS" collapses into "how fast is one core on that VPS." That is a question you can answer from published single-core scores, within a sensible margin. MachinevCPU / RAMRough $/moEst. timeline req/sEst. requests/dayApple M1 (measured)8 / 16GBn/a3,543315MHetzner CAX21 (Ampere Arm)4 / 8GB~€7~1,600 to 1,900~140M to 165MHetzner CPX31 (AMD)4 / 8GB~€13~1,900 to 2,300~165M to 200MHetzner CCX13 (dedicated AMD)2 / 8GB~€13~2,000 to

2,400~175M to 205MDigitalOcean Basic1 / 2GB~$12~1,300 to 1,600~110M to 140MDigitalOcean Premium AMD2 / 4GB~$28~1,800 to 2,100~155M to 180M Assume plus or minus 30% on every estimated row, and treat the prices as approximate list prices that will drift. The method: an M1 performance core scores roughly 1.7x to 2.3x a typical cloud core on single-threaded work, with Ampere's Arm cores at the lower end and current AMD EPYC cores at the higher end. The 343MB database fits in page cache on every box in that table, so none of them are going to disk on reads. Writes will be somewhat worse than the ratio suggests, because fsync on cloud NVMe is slower than on a laptop SSD, and that lands in the write tail rather than the read path. Even taking the worst row and the pessimistic end of its range: a $12 droplet serves something like 110 million timeline requests a day. The heaviest page in the app. On the cheapest box on the list. That is the entire argument, and it survives leaving Apple silicon behind. What to actually buy Since the workload is one core plus page cache, the shopping list is short and slightly counterintuitive. Buy single-core speed, not core count. A 2 vCPU box with fast cores beats an 8 vCPU box with slow ones for this workload. This is the opposite of how people usually size a database server, and it is because you are not running one. Buy enough RAM to hold the database. Your working set wants to live in page cache. A 343MB database barely registers, and any of these boxes will happily keep a several-gigabyte database resident. When your database no longer fits in RAM, that is a real signal, and it is the first one worth acting on. Insist on local NVMe. Never put SQLite on network storage. This is the one that will actually hurt you.