SQLite 万能论：它凭什么把搜索、队列、缓存和向量库都收进一个文件

Dr. Raphael Bauer penned an excellent article (PostgreSQL for Everything) on the value of using PostgreSQL to power your enterprise. I’ve taken the liberty of correcting a couple of errors, mainly he should of chosen SQLite 😊 (this is mostly a joke, I ❤️ PostgreSQL, its great tech.)

Contrary to popular belief, the answer to everything is NOT 42. It’s SQLite. (Fine. It might also be sqlite3.)

SQLite will outlive most of what you are running right now.

Back then the accepted wisdom was that SQLite was a toy. A file. Something you shipped inside a phone app so you didn’t have to write a config parser. Real applications got a real database with a real port number and a real daemon and a real 3am page.

In my humble opinion, the power of SQLite comes from three sources:

It is rock-solid and stable.

It is easy to run, install and scale. Mostly because there is nothing to run.

It massively simplifies your IT setup by being not only an RDBMS, but also a full-text search engine, a document store, a cache, a vector index, and a file format.

Let’s have a closer look.

SQLite is boring old technology. First release: 2000. It is also, by a margin that isn’t close, the most widely deployed database engine on the planet. It’s in your phone. It’s in your browser. It’s in your car. It’s in the plane you flew here on. There are more running copies of SQLite than there are running copies of everything else combined, and it isn’t a contest.

Ironing out bugs in database systems takes time. SQLite had that time, and then kept going. The test suite has 100% branch coverage under MC/DC, the same standard used for avionics software. There is roughly 500 times more test code than library code. The project has a stated support commitment through the year 2050, which is a longer planning horizon than your company’s mission statement.

It’s also in the public domain. Not open source. Public domain. No license, no CLA, no attribution clause, no vendor with a Series C and a change of heart.

True, SQLite is old. But it keeps quietly shipping modern features: window functions, RETURNING, strict tables, generated columns, jsonb. Every release is a small, well-tested, backwards-compatible improvement, which is the least exciting and most valuable thing a database can be.

Installing SQLite locally is easy in the sense that you have already done it. It is bundled with every major Linux distribution, ships inside Python, Ruby, PHP, Go, Rust, .NET, Android and iOS, and is sitting on your Mac right now whether you asked for it or not.

Running tests against a database identical to production is not a Test containers problem here. It is :memory:. Your test suite spins up a fresh database in microseconds, per test, in parallel, with no Docker daemon and no port collisions. The thing you test against is the thing you ship, because it’s the same library compiled into the same binary.

If you want to run SQLite on a server: you already are. It came with the OS.

Scaling is the part where people expect the article to get quiet, so let’s not:

Vertical: a modern NVMe drive and a machine with 128GB of RAM will serve a shocking amount of traffic when your database round trip is a function call instead of a network hop. No connection pool. No TLS handshake. No pgbouncer. Nanoseconds instead of milliseconds.

Replication and backup: Litestream streams your WAL to S3 continuously. LiteFS gives you distributed reads. Both are small, single-binary, and boring.

Hosted: Turso, Cloudflare D1, rqlite, and friends will happily sell you SQLite with a control panel if you miss having a control panel.

That makes SQLite one of the most widely supported pieces of software in existence. For you this means less maintenance and more time building features for clients.

Running SQLite in the cloud is zero clicks, because it’s a file next to your application. But it gets better. SQLite can replace a whole shelf of systems you’d otherwise be running.

SQLite ships with FTS5, a full-text search engine built into the library you already have linked. Tokenizers, prefix queries, phrase queries, NEAR, boolean operators, custom ranking with BM25, and snippet/highlight functions for rendering results.

Two things worth appreciating here. First, there is no sync problem, because there is no second system. Your search index is updated in the same transaction as your data, by definition, forever. Every “why is the search index stale” incident you have ever had was caused by architecture you didn’t need.

Second, it’s fast in a way that surprises people. Simon Willison’s Datasette runs faceted full-text search over multi-gigabyte SQLite files and returns in milliseconds, on a small VM, for free.

Is FTS5 going to do multilingual analysis chains and distributed sharding across 40 nodes? No. Do you have 40 nodes? Also no.

More on the topic: SQLite FTS5 documentation

SQLite has excellent support for storing and querying JSON. The JSON functions are built in, -> and ->> operators work the way you’d hope, and since 3.45 there’s jsonb, a binary representation that skips the reparse on every access.

The part people miss: you can index into JSON. Create a generated column from a JSON path, index the generated column, and you have a fast lookup on a field that doesn’t exist in your schema. Schemaless writes, indexed reads, one file.

So the pitch is: document storage, ACID transactions, no separate server, no replica set, no sharding config, no mongod, and the thing on disk is a single file you can copy. Is there a need for MongoDB anymore? There was a good article about a large publication switching off Mongo. Notably, nobody has ever written the reverse article.

Events, queues and persistent logs matter more every year. Kafka, RabbitMQ and SQS all provide that. Maintaining them is annoying, bespoke, and requires a skillset you have to hire for.

Good news: a table works fine.

BEGIN IMMEDIATE; UPDATE jobs SET status = 'running', worker = ? WHERE id = (SELECT id FROM jobs WHERE status = 'pending' ORDER BY id LIMIT 1) RETURNING *; COMMIT;

BEGIN IMMEDIATE takes the write lock up front, RETURNING hands you the claimed row, and the transaction guarantees exactly one worker gets it. In WAL mode readers never block, so your dashboard querying queue depth doesn’t fight your workers.

Here is the honest caveat, because you deserve one: SQLite has a single writer. There is no SKIP LOCKED because there is nothing to skip. Concurrent consumers serialize on the write lock, and if your enqueue rate is genuinely in the tens of thousands per second, you will feel it.

But notice what happened. In the PostgreSQL version of this argument, the queue is a table in your database. In this version, the queue is a table in your database that is also in your application process. The message never leaves the machine. There is no broker, no consumer group rebalance, no “why did the partition assignment change during deploy.”

My tip: start with SQLite as your queue. When it stops performing, you will have real numbers instead of a vibe, and you can go buy Kafka with confidence. You’ll be surprised how long that takes.

Time series data is special. Lots of points arriving fast, then aggregation, statistics, rollups.

There is no TimescaleDB here, so let’s be straight about it. What SQLite gives you instead:

Partitioning by file. One database per day, week or tenant. Archiving is mv. Deleting old data is rm, which runs in constant time and doesn’t vacuum. Querying across them is ATTACH plus a UNION ALL view. This is crude and it is also extremely effective.

Rollup tables written by trigger or by the same code path that does the insert. You were going to build continuous aggregates anyway.

Batched writes. One transaction, ten thousand inserts, one fsync. SQLite will do several hundred thousand rows per second this way on ordinary hardware, because there’s no network protocol in the path.

Column-oriented when you need it. For the analytical half, point DuckDB at your SQLite file directly. It reads it natively. You get vectorized OLAP over the same file your app writes to, no ETL.

The specialized systems are genuinely amazing and if you are ingesting a million points a second you should go use one. Most people saying “time series” mean a few million rows a day, which is a Tuesday for a file on an SSD.

sqlite-vec is a single-file, dependency-free extension that turns SQLite into a vector database. It’s written in C, runs anywhere SQLite runs, including the browser via WASM, and stores vectors in ordinary tables.

This is the part where SQLite has an unfair advantage. Your embeddings, your source documents, your metadata and your full-text index are in the same file, so hybrid search is a join, not a distributed query across three services with three different consistency models. Filter by tenant and date and keyword and vector similarity, in one statement, transactionally.

Also, and this matters more than it sounds: your entire RAG index is a file. You can email it. You can put it in a Docker image. You can ship it to a laptop that’s offline. Try that with your managed vector cluster.

Caching is important. Most applications reach for Redis to hold sessions and hot data. A cache is by definition allowed to lose data and be regenerated from source.

So why run a second server for that? SQLite gives you several options depending on how much durability you want to trade away:

PRAGMA journal_mode = WAL; PRAGMA synchronous = OFF; -- it's a cache, live a little

Or skip the disk entirely with :memory:, or PRAGMA temp_store = MEMORY, or an in-memory database shared across your connections via file:cache?mode=memory&cache=shared.

Expiry is a column and a DELETE ... WHERE expires_at < unixepoch() on a timer, which is what Redis is doing for you anyway, just further away and with its own eviction policy you had to go read about.

And here’s the kicker: a Redis GET over localhost is on the order of 100 microseconds. A SQLite point lookup against a warm page cache is on the order of 1 microsecond. You did not remove a dependency to be slower. You removed a dependency and got faster, because the fastest network call is the one that’s a function call.

Redis is excellent software. It is also a separate process, a separate failure mode, a separate memory budget, a separate thing to secure, and a separate line item.

You would think reading a small blob from a file is faster than reading it from a database. It is not, and this is not an opinion, it’s a benchmark the SQLite project published and titled with admirable directness: 35% Faster Than The Filesystem.

For blobs under roughly 100KB, SQLite reads and writes faster than individual files on disk, and uses about 20% less space on top of it. The reason is that the file system charges you an open() and a close() and a directory traversal per item, while SQLite charges you one already-open file handle and a B-tree seek.

You also get, for free: atomic multi-blob updates, no partial writes on crash, no filename escaping bugs, no “what happens when a directory has 4 million entries,” no rsync taking six hours because of inode count, and a backup story that is one file.

Store the payload in a BLOB column, serialize with something compact if you’re feeling fancy, deserialize on the client. The SQLite team themselves suggest that SQLite is a better fopen(), and they meant it as a design goal, not a joke.

Hierarchical data in SQL via recursive queries is doable but historically painful to read, maintain and debug.

SQLite has full recursive CTE support, and its documentation on the subject is genuinely one of the better pieces of technical writing in the field. Closure tables, materialized paths and adjacency lists all work well. There’s no LTREE, so materialized paths are a TEXT column plus a GLOB index, which is less elegant and roughly as fast.

For real graph work, simple-graph implements a property graph on top of plain SQLite tables in a few hundred lines of SQL. Nodes, edges, traversal.

The general principle applies here more than anywhere: your graph is probably ten thousand nodes. Ten thousand nodes fits in L3 cache. You do not need Neo4j. You need an index and a coffee.

Most “microservices” today are: a model, a query, and JSON out.

SQLite turns any query into JSON with json_object() and json_group_array(). That’s your serialization layer, gone.

But SQLite goes further than the original argument does, because SQLite runs inside your process. The microservice isn’t replaced by a stored procedure, it’s replaced by a function call. There is no service to deploy, no health check, no retry logic, no circuit breaker, no distributed trace to correlate, and no p99 dominated by network jitter.

Datasette is the proof of concept taken to its conclusion: point it at a SQLite file and you get a JSON API, a web UI, faceted search and a plugin ecosystem, with no code. Litestream handles the durability. That’s a production data service in two binaries and a file.

There are pros and cons and I am not going to pretend the cons are zero. But the number of services in this industry that exist purely to put a network hop in front of a query is not small.

The SQLite documentation itself includes a Mandelbrot set renderer written as a recursive common table expression. In the manual. As an example of the query syntax. Casually.

People have also implemented Conway’s Game of Life, sudoku solvers, and maze generators in pure SQLite CTEs. There’s a chess engine. Someone got Doom’s fire effect running in a query.

Crazy. Probably not to be taken too seriously. But you have to respect a database whose official docs contain fractals.

The list above is not exhaustive. SQLite is a remarkably flexible piece of software, it loads extensions, and there is very likely one for whatever you’re about to go install a server for.

Here’s the thing the original argument gets right and SQLite gets righter: simplicity is what lets you move fast. Every system in your stack is a thing to deploy, monitor, secure, upgrade, back up, pay for, and explain to the new hire. PostgreSQL cuts that list down. SQLite cuts it to zero, because the database isn’t a system, it’s a file and a function call.

Yes, there is a ceiling. One writer, one machine. When you hit it you’ll know, and you’ll go get PostgreSQL, and that will be a good day because it means people are using your thing.

Until then, when the next requirement shows up, ask: can’t SQLite just do this? And do we really need that shiny new technology X?

SQLite might not be the answer to everything. But it is the answer to a lot more than you might think, and it is already installed.