100000 TPS over a billion rows: the unreasonable effectiveness of SQLite

Summary

100000 TPS over a billion rows: the unreasonable effectiveness of SQLite02 Dec 2025SQLite doesn't have MVCC! It only has a single writer! SQLite is for phones and mobile apps (and the occasional airliner)! For web servers use a proper database like Postgres! In this article I'll go over why being embedded and a single writer are not deficiencies but actually allow SQLite to scale so unreasonably well.PreludeFor the code examples I will be using Clojure. But, what they cover should be applicable to most programming language.The machine these benchmarks run on has the following specs:MacBook Pro (2021)Chip: Apple M1 ProMemory: 16 GBThese benchmarks are not meant to be perfect or even optimal. They are merely to illustrate that it's relatively easy to achieve decent write throughput with SQLite. Usual benchmark disclaimers apply. Defining TPSWhen I say TPS I don't mean writes/updates per second. I'm talking about transactions per second, specifically interactive transactions that are common when building web applications. By interactive transactions I mean transactions where you execute some queries, run some application code and then execute more queries. For example:BEGIN; UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice'; -- some application code runs UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob'; COMMIT; Transactions are useful because they let you rollback the state of your changes if your application encounters a problem.The benchmark harnessTo simulate requests we spin up n virtual threads (green threads) that each execute a function f this is analogous to handlers on a web server and will give us similar contention. Worth noting that this is high burst. I.e we will reach n level concurrent requests as fast as the system can spin up the virtual threads.(defmacro tx-per-second [n & body] `(let [ids# (range 0 ~n) start# (. System (nanoTime))] (->> ids# ;; Futures are using virtual threads so blocking is not slow (mapv (fn [_#]...