SQLite in Production

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

---

SQLite is the most deployed database engine in the world. It runs on every smartphone, inside every web browser, in most televisions, and in countless embedded systems. By some estimates, there are over a trillion active SQLite databases in use today. Yet for years, the conventional wisdom in web development was that SQLite is fine for prototyping but not for production.

That’s changing. A growing number of companies and frameworks are betting on SQLite as a production database for web applications, backed by new tooling for replication, distribution, and edge deployment. The tradeoffs are real, but they’re not what most developers think.

In this post, we’ll cover:

• Who’s using SQLite in production - Companies running real workloads on it
• WAL mode and concurrency - How SQLite handles multiple readers and writers
• The tooling ecosystem - Litestream, LiteFS, Turso, rqlite, and more
• Performance characteristics - What the numbers actually look like
• When SQLite breaks down - The genuine limitations
• When to use PostgreSQL instead - Where a client-server database is the better choice

Who’s using SQLite in production

SQLite in production isn’t a theoretical exercise. Several well-known companies run it for significant workloads.

Expensify has been running SQLite in production since 2012, processing expense reports for millions of users. Their architecture uses SQLite on a cluster of servers with replication handled at the application level. David Barrett, Expensify’s CEO, has spoken publicly about this architecture and considers it a competitive advantage—fewer moving parts, simpler operations, lower costs.

Fly.io went all-in on SQLite for edge deployments. Their blog post I’m All-In on Server-Side SQLite by Ben Johnson made the case that SQLite, combined with Litestream for replication, is a viable architecture for server-side applications. Fly.io hired Ben Johnson specifically to build LiteFS, their distributed SQLite file system.

Tailscale uses SQLite for its coordination server. Their control plane stores network configuration in SQLite databases, taking advantage of its simplicity and the fact that each coordination server node can operate independently.

Basecamp (37signals) announced that their new Rails deployment tool, Kamal, uses SQLite. DHH wrote in 2023 about how solid-state drives have eliminated SQLite’s historical performance disadvantage for reads, and that for many applications, a single-server deployment with SQLite is more than enough.

The Rails ecosystem has embraced this shift directly. Rails 8, released in late 2024, made SQLite a first-class production database option with built-in support for cache, queue, and cable storage backed by SQLite—no Redis required.

WAL mode and concurrency

SQLite’s default journal mode uses a rollback journal that locks the entire database during writes. This means a single writer blocks all readers. In 2010, Write-Ahead Logging (WAL) mode changed this dramatically.

In WAL mode, writes go to a separate log file instead of modifying the database directly. Readers continue reading from the main database file, checking the WAL for any newer versions of pages they need. This means readers never block writers, and writers never block readers. Multiple readers can operate concurrently with a single writer.

Enabling WAL mode is a single pragma:

PRAGMA journal_mode=WAL;

Most frameworks and ORMs now set this by default for production SQLite configurations. Rails 8, for instance, enables WAL mode automatically.

The critical limitation remains: SQLite supports only one writer at a time. If two connections try to write simultaneously, one of them will get a SQLITE_BUSY error (or wait, if you’ve configured a busy timeout). In WAL mode, this is less painful than it sounds—writes are fast because they just append to the log, so the lock is held briefly. A well-tuned SQLite database on modern SSDs can handle thousands of write transactions per second.

-- Set a busy timeout so writers wait instead of immediately failing
PRAGMA busy_timeout=5000;

The tooling ecosystem

The biggest shift in SQLite’s production story isn’t SQLite itself—it’s the ecosystem of tools built around it.

Litestream

Litestream, created by Ben Johnson, is a streaming replication tool for SQLite. It runs as a background process that continuously replicates your SQLite database to S3, Azure Blob Storage, Google Cloud Storage, SFTP, or another file path. If your server dies, you restore from the replica and lose at most a few seconds of data.

Litestream works by intercepting SQLite’s WAL. It opens a long-running read transaction that prevents automatic checkpointing, captures WAL frames as they’re written, replicates them to the configured destination, and then triggers checkpointing itself. Your application uses standard SQLite libraries with no code changes.

# Install litestream
brew install benbjohnson/litestream/litestream

# Replicate a database to S3
litestream replicate /path/to/db.sqlite s3://my-bucket/db

LiteFS

LiteFS is a FUSE-based file system that replicates SQLite databases across a cluster of machines. Unlike Litestream (which is primarily for disaster recovery), LiteFS provides real-time replication across multiple application nodes. Each node gets a full local copy of the database, so reads are always fast and local.

LiteFS uses a primary/replica model. One node is the primary and handles writes; replicas forward write requests to the primary and receive replicated changes. This pairs naturally with edge deployment platforms like Fly.io, where you want your database close to your users globally.

Turso and libSQL

Turso is a database platform built on libSQL, an open-source fork of SQLite created by Glauber Costa and the Turso team. libSQL extends SQLite with features the upstream project won’t accept: native replication, embedded replicas, ALTER TABLE improvements, encryption at rest, and vector search.

The key differentiator is embedded replicas. Your application embeds a local libSQL database that syncs with a remote Turso instance. Reads hit the local replica (microsecond latency), while writes sync to the remote primary. This gives you SQLite’s speed for reads with the durability of a remote database.

import { createClient } from "@libsql/client";

const db = createClient({
  url: "file:local.db",
  syncUrl: "libsql://my-db-my-org.turso.io",
  authToken: "...",
});

// Reads are local, writes sync to remote
await db.sync();

Turso also announced Limbo, a complete rewrite of SQLite in Rust with native async support. It’s still early but signals the direction of the project.

rqlite

rqlite, created by Philip O’Toole, is a distributed relational database built on SQLite and the Raft consensus protocol. It provides a fully replicated SQLite cluster where writes are committed through Raft consensus, guaranteeing strong consistency. rqlite exposes an HTTP API, so it works with any language.

mvsqlite

mvsqlite takes an entirely different approach: it runs SQLite on top of FoundationDB, giving you distributed MVCC (multi-version concurrency control) for SQLite. This means lock-free concurrent reads and writes, time-travel queries (checkout any historical snapshot), and FoundationDB’s battle-tested distributed storage underneath.

Cloudflare D1

Cloudflare D1 is a serverless SQLite database that runs on Cloudflare’s edge network. It’s designed for Cloudflare Workers—your database is colocated with your serverless functions, eliminating the network round trip to a central database. D1 handles replication, backups, and scaling automatically.

Performance characteristics

SQLite’s performance profile is different from client-server databases in ways that matter for production decisions.

For reads, SQLite is extraordinarily fast. There’s no network round trip, no protocol overhead, no connection pooling. A simple primary-key lookup takes microseconds, not milliseconds. On a modern SSD, a 4 vCPU server can sustain over 100,000 reads per second from a single SQLite database without any special tuning.

For writes in WAL mode, throughput depends on your hardware and transaction patterns. Small transactions (single INSERT or UPDATE) can sustain thousands per second. Batch writes are where SQLite really excels—wrapping 10,000 inserts in a single transaction is dramatically faster than executing them individually, because each transaction requires an fsync to disk.

-- Slow: 10,000 individual transactions
INSERT INTO events (name) VALUES ('click');
-- ... repeated 10,000 times

-- Fast: one transaction for all inserts
BEGIN;
INSERT INTO events (name) VALUES ('click');
-- ... repeated 10,000 times
COMMIT;

The comparison to PostgreSQL isn’t straightforward because they optimize for different things. PostgreSQL handles concurrent writes better and provides more sophisticated query planning for complex queries. SQLite wins on read latency (no network hop) and simplicity (no separate server process). For single-server applications with moderate write loads, SQLite often outperforms PostgreSQL on total throughput because it eliminates network and protocol overhead.

When SQLite breaks down

SQLite’s limitations are well-documented, and it’s worth being honest about them.

The single-writer constraint is the most significant. If your application needs high write concurrency from multiple processes or servers, SQLite will bottleneck at the write lock. WAL mode and busy timeouts help, but they don’t eliminate the fundamental limitation. If you’re processing thousands of concurrent write transactions from independent users, you need a database designed for that workload.

Multi-server deployment is the other major gap. SQLite databases are single files. If you need multiple application servers, each server can’t just connect to the same SQLite file over a network filesystem—that’s explicitly not supported and will corrupt your data. Tools like LiteFS and rqlite solve this, but they add complexity. If your architecture already requires multiple application servers with shared state, PostgreSQL is simpler.

Complex analytical queries can hit SQLite’s limitations. It has a simpler query planner than PostgreSQL, no parallel query execution, limited window function optimization, and no support for common table expression (CTE) materialization hints. For reporting dashboards or ad-hoc analytics, PostgreSQL or even DuckDB may be better choices.

The extension ecosystem is sparse compared to PostgreSQL. There’s no equivalent of PostGIS for geospatial queries, no pgvector for vector search (though libSQL is adding this), and no comparable ecosystem of contributed extensions. If you need specialized data types or query capabilities, check whether SQLite supports them before committing.

When to use PostgreSQL instead

PostgreSQL is the better choice when your application needs concurrent writes from multiple connections, multi-server deployment without extra tooling, complex analytical queries, a rich extension ecosystem (pgvector, PostGIS, pg_cron), or strong role-based access control at the database level.

The decision often comes down to deployment architecture. If you’re running a single server (or using edge deployment with per-region databases), SQLite is viable and simpler. If you’re running a traditional multi-server deployment behind a load balancer, PostgreSQL is the path of least resistance.

For teams already using PostgreSQL, the local-first approach offers an interesting middle ground: keep PostgreSQL as your server database and use tools like ElectricSQL or PowerSync to sync data to local SQLite on client devices. You get the best of both worlds—PostgreSQL’s power on the server and SQLite’s speed on the client. For more on this architecture, see our post on local-first databases.

Wrapping up

SQLite in production is no longer a fringe idea. The combination of WAL mode for concurrency, Litestream for replication, LiteFS for distribution, and Turso/libSQL for edge deployment has turned SQLite into a legitimate production database for the right workloads.

The key takeaways:

• Enable WAL mode and set a busy timeout—these two pragmas transform SQLite’s concurrency story
• Use Litestream for disaster recovery on single-server deployments
• Consider LiteFS or Turso for multi-region or edge deployments
• SQLite excels for read-heavy, single-server applications with moderate write loads
• PostgreSQL remains the better choice for high write concurrency, multi-server deployments, and complex queries

The “right workload” for SQLite is broader than most developers assume. If your application can run on a single server—and with modern hardware, that covers more applications than you’d think—SQLite eliminates an entire category of operational complexity. No connection pools, no separate database process, no network latency, no database server to maintain. For some teams, that simplicity is the most valuable feature of all.