SQLite as the Only Database You Need

We run a telemetry aggregation service at the edge — small instances deployed close to users in Stockholm, Oslo, and Copenhagen. Each instance receives about 5,000 events per second, buffers them for 30 seconds, and forwards aggregates to a central warehouse. For two years we used PostgreSQL for the local buffer. It worked, but it was overkill. Connection pools, vacuum tuning, and the occasional lock contention were operational overhead we did not need.

Last year we replaced PostgreSQL with SQLite. The migration took two days. The result: simpler code, lower latency, and zero connection management.

SQLite's WAL mode is the key. In WAL, readers do not block writers and writers do not block readers. Our service has one writer — the ingestion goroutine — and multiple readers — the aggregation and forward goroutines. With WAL they operate concurrently without locks. We have not seen a single database is locked error since the switch.

The Go driver, modernc.org/sqlite, is a pure Go implementation. No CGO, no external dependencies, cross-compilation works out of the box. We embed the database file on a tmpfs mount for the hot buffer and replicate to disk every 10 seconds via Litestream. If the instance crashes, we lose at most 10 seconds of buffered data, which is acceptable for our use case.

db, err := sql.Open("sqlite", "file:/dev/shm/buffer.db?_journal=WAL&_busy_timeout=5000")
if err != nil {
    log.Fatal(err)
}

Performance is better than PostgreSQL for this workload. SQLite handles 50,000 writes per second on our hardware without breaking a sweat. The query planner is simpler but predictable — we know exactly which indexes are used because we can run EXPLAIN QUERY PLAN in CI and fail the build if it changes.

"The best database is the one you do not have to operate. SQLite is the closest thing to no database at all."

The limitations are real. SQLite does not handle concurrent writes from multiple processes well. It is not a drop-in replacement for a multi-user OLTP system. But for single-process, high-throughput, local-state workloads — edge buffers, session stores, configuration caches — it is often the right choice.

We have since expanded SQLite to two other services: a configuration cache and a local job queue. Both are simpler and more reliable than their PostgreSQL predecessors. I am not suggesting you replace your primary database. But if you are running a service that fits SQLite's constraints, the simplicity is worth considering.