Just a File
I was fed up with PostgreSQL's vacuum. Early twenties, still green as an engineer.
A friend at an ad agency had asked me to rebuild a consumer-facing forum. This was the flip-phone era in Japan. The forum was niche, but it pulled around ten million page views a month. PostgreSQL was a pain to operate. Vacuum tuning, connection pool management, replication setup. I wanted something lighter. That's when I found SQLite.
It spoke SQL. A single file, self-contained. No installation, no daemon. I tried it in my dev environment and was impressed. Created tables, ran queries, JOINs worked. Almost everything I'd been doing in PostgreSQL just worked. No vacuum, no connection management. What more did I need.
Integration tests all passed. I shipped it the way I always did.
If you've been in this industry long enough, you already know what happened.
It died in seconds. Well, it took me five minutes to notice.
SQLite has no concept of row-level locking. Every write locks the entire database file. Fine for a single user. But on a forum where users write concurrently, file locks collided instantly. Writes backed up, timeouts cascaded, and the whole thing was unusable within minutes.
My integration tests had run with a single user. I'd never written a concurrent write test. Of course not—I was the only user in my dev environment. Tests passing and production working are different things.
I rebuilt it with the same PostgreSQL I'd been so tired of. Vacuum was a chore, but at least the forum stayed up.
Nearly twenty years later, SQLite's territory has only expanded. DuckDB is getting attention. SQLite is embedded in browsers. It runs on cloud edge nodes. Distributed SQLite like Turso has appeared. My younger self would be surprised.
But the use case shifted. That's all.
Browser local storage. Edge caches. Embedded app data stores. All of these assume single-process access. SQLite's design philosophy hasn't changed.
When I see young engineers use SQLite and conclude "it speaks SQL, so it's a database," I wince. Speaking SQL and being an RDBMS are different things. Transaction isolation levels, row-level locking, replication, connection management. An RDBMS carries all of that. SQLite doesn't. Not carrying it is what makes it light.
An RDBMS and a file that speaks SQL. Between them lies a gap roughly the size of the forum that went down that day.