Can SQLite handle concurrent writes in production?

SQLite supports one writer at a time, even in WAL mode. For read-heavy workloads (95%+ reads), this is rarely a bottleneck. For write-heavy workloads, you need to serialize writes through a single process or use Turso/libSQL which implements server-side write coordination.

How does LiteFS replication work?

LiteFS uses a FUSE filesystem that intercepts SQLite's WAL writes. When a transaction commits, LiteFS captures the changed pages and ships them to replica nodes over HTTP. Replicas apply the pages to their local copy. Replication lag is typically 50-200ms depending on network distance.

When should I use SQLite over Postgres at the edge?

Use SQLite when your workload is read-heavy (>90% reads), your data fits on a single node (under ~100GB), you need sub-millisecond read latency, and you want zero operational overhead. Use Postgres when you need complex transactions, full-text search with custom dictionaries, or write-heavy workloads.

SQLite at the Edge: LiteFS, Turso, and Why Postgres Isn't Always the Answer

Why SQLite at the Edge Makes Sense

The fundamental insight is this: if your database is a file on the same machine as your application, reads are a function call, not a network round trip. The latency difference is extraordinary:

Postgres (same region): ~2ms per query
Postgres (cross-region): ~50-180ms per query
SQLite (local file, WAL mode): ~0.02ms per query

That's not a typo. SQLite reads in WAL mode are literally 100x faster than same-region Postgres because there's no TCP handshake, no protocol negotiation, no query parsing over the wire, no result serialization. It's a B-tree lookup on a memory-mapped file.

The catch, obviously, is that SQLite is a single-node database. It doesn't replicate. It doesn't cluster. It writes to a single file.

That's where LiteFS, Turso, and libSQL come in.

LiteFS: Transparent Replication for SQLite

LiteFS, built by the Fly.io team, is genuinely clever. It's a FUSE filesystem that sits between your application and the actual SQLite file, intercepting all write operations.

Here's how the replication works at a low level:

Application writes to SQLite
 ↓
FUSE intercepts the write syscall
 ↓
LiteFS captures changed WAL pages
 ↓
Pages are shipped to primary node via HTTP
 ↓
Primary applies pages and forwards to replicas
 ↓
Replicas apply pages to their local SQLite file

The key design decision is that LiteFS replicates at the page level, not the SQL level. It doesn't replay SQL statements, it ships the actual modified 4KB database pages. This means:

Replication is SQL-agnostic (works with any SQLite extension, custom functions, etc.)
The overhead is proportional to data changed, not query complexity
There's no need to parse or validate SQL on the replica side

Setting Up LiteFS on Fly.io

# fly.toml
[mounts]
 source = "litefs"
 destination = "/var/lib/litefs"

[processes]
 app = "/usr/local/bin/litefs mount"

# litefs.yml
fuse:
 dir: "/litefs"

data:
 dir: "/var/lib/litefs"

proxy:
 addr: ":8080"
 target: "localhost:3000"
 db: "myapp.db"
 passthrough:
 - "*.ico"
 - "*.png"

exec:
 - cmd: "node server.js"

lease:
 type: "consul"
 advertise-url: "http://${FLY_ALLOC_ID}.vm.${FLY_APP_NAME}.internal:20202"
 consul:
 url: "${FLY_CONSUL_URL}"
 key: "litefs/${FLY_APP_NAME}"

The proxy section is important, LiteFS runs a reverse proxy that automatically forwards write requests to the primary node. Your application doesn't need to know about the replication topology.

Write Forwarding

When your app running on a replica in Tokyo receives a POST request that needs to write to the database, here's what happens:

1. Request hits LiteFS proxy on Tokyo replica
2. LiteFS detects it's a write (POST/PUT/DELETE or SQL INSERT/UPDATE/DELETE)
3. LiteFS forwards the request to the primary (Virginia) via Fly-Replay header
4. Primary executes the write
5. Changed pages replicate to Tokyo (~120ms later)
6. Response returns to client

The write latency is the network round trip to the primary plus replication time. For a user in Tokyo writing to a Virginia primary, that's roughly 180-250ms. Reads, however, are still hitting the local replica: 0.02ms.

For a content site where 98% of requests are reads, this trade-off is insanely good.

Turso and libSQL: SQLite with Server Capabilities

Turso takes a different approach. Instead of transparent filesystem-level replication, they forked SQLite into libSQL and added server-level features:

libSQL additions over SQLite:
├── Server mode (sqld), listens on HTTP/WebSocket
├── Embedded replicas, local SQLite file that syncs with remote
├── Namespaces, multi-tenant database isolation
├── Vector search, native vector embeddings (VECTOR type)
├── ALTER TABLE improvements, column type changes
└── WASM-based user-defined functions

The embedded replica feature is what makes Turso interesting for edge deployments:

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

const client = createClient({
 url: "file:local-replica.db", // local SQLite file
 syncUrl: "libsql://mydb-myorg.turso.io", // remote primary
 authToken: process.env.TURSO_AUTH_TOKEN,
 syncInterval: 60, // sync every 60 seconds
});

// Reads hit the local file, ~0.1ms
const users = await client.execute("SELECT * FROM users WHERE active = 1");

// Writes go to the remote primary, ~50ms
await client.execute({
 sql: "INSERT INTO users (name, email) VALUES (?, ?)",
 args: ["Alice", "alice@example.com"],
});

// Force sync when you need fresh data
await client.sync();

The sync mechanism is more sophisticated than LiteFS. Turso tracks a vector clock per namespace and only ships the delta of changed frames since the last sync. The embedded replica maintains a local WAL that gets reconciled during sync.

Real Benchmarks

I ran these against a real-world dataset: 2 million product records, ~800MB database, running on Fly.io machines (shared-cpu-1x, 256MB RAM).

Read Performance

Query: SELECT * FROM products WHERE category = ? AND price < ? ORDER BY rating DESC LIMIT 20
Index: CREATE INDEX idx_products_cat_price ON products(category, price)

SQLite (local, WAL mode):
 p50: 0.08ms
 p99: 0.3ms
 Throughput: 42,000 queries/sec (single connection)

Turso (embedded replica, synced):
 p50: 0.09ms
 p99: 0.35ms
 Throughput: 38,000 queries/sec

Postgres (same region, pgbouncer):
 p50: 1.8ms
 p99: 4.2ms
 Throughput: 8,500 queries/sec (50 connections)

Postgres (cross-region, nearest replica):
 p50: 12ms
 p99: 45ms
 Throughput: 2,100 queries/sec

SQLite is 5x faster than same-region Postgres and 150x faster than cross-region. The throughput numbers are even more dramatic because SQLite has no connection overhead, every query is a direct function call into the B-tree.

Write Performance

Query: INSERT INTO orders (user_id, product_id, quantity, total) VALUES (?, ?, ?, ?)

SQLite (local, WAL mode):
 p50: 0.05ms
 p99: 2.1ms (WAL checkpoint spikes)
 Throughput: 18,000 inserts/sec (single writer)

LiteFS (write to primary, replicate):
 p50: 85ms (network to primary)
 p99: 210ms
 Throughput: ~200 writes/sec (limited by network RTT)

Turso (embedded replica, write to primary):
 p50: 52ms
 p99: 130ms
 Throughput: ~350 writes/sec

Postgres (same region):
 p50: 1.5ms
 p99: 5ms
 Throughput: 12,000 inserts/sec (parallel writers)

Here's where Postgres wins decisively. SQLite's single-writer constraint means it can't parallelize writes. And in an edge deployment, every write has to hit the primary over the network. If you're doing more than a few hundred writes per second, you need Postgres (or another multi-writer database).

When to Actually Use This

After running SQLite at the edge in production for over a year, here's my decision framework:

Use SQLite + LiteFS/Turso when:

Read-to-write ratio is 90%+ reads
Dataset fits comfortably on a single machine (<100GB)
You need sub-millisecond read latency globally
You want zero database operational overhead
You're on a platform that supports it (Fly.io, Cloudflare Workers with D1, etc.)

Stick with Postgres when:

Write throughput matters (>500 writes/sec sustained)
You need complex transactions across multiple tables
Dataset is large (>100GB) or growing fast
You need advanced features (JSONB, full-text with custom dictionaries, materialized views)
You have a dedicated DBA team

The hybrid approach (what I actually do for most projects):

┌─────────────────────────────┐
│ Edge (Fly.io regions) │
│ ┌───────────────────────┐ │
│ │ SQLite (via LiteFS) │ │
│ │ - User sessions │ │
│ │ - Content cache │ │
│ │ - Feature flags │ │
│ │ - Read-heavy queries │ │
│ └───────────────────────┘ │
└─────────────┬───────────────┘
 │ writes + complex queries
 ▼
┌─────────────────────────────┐
│ Central (single region) │
│ ┌───────────────────────┐ │
│ │ Postgres │ │
│ │ - Transactional data │ │
│ │ - Analytics │ │
│ │ - Full-text search │ │
│ │ - Write-heavy tables │ │
│ └───────────────────────┘ │
└─────────────────────────────┘

SQLite at the edge for the hot, read-heavy path. Postgres in a central region for the transactional, write-heavy stuff. The edge SQLite databases sync from Postgres via a CDC pipeline or periodic ETL.

Advanced: Custom libSQL Extensions

One thing that pushed me toward Turso was libSQL's WASM-based user-defined functions. You can write custom functions in any language that compiles to WASM and load them into SQLite:

// distance.rs, compiled to WASM
use libsql_bindgen::*;

#[libsql_bindgen]
pub fn haversine(lat1: f64, lon1: f64, lat2: f64, lon2: f64) -> f64 {
 let r = 6371.0; // Earth's radius in km
 let dlat = (lat2 - lat1).to_radians();
 let dlon = (lon2 - lon1).to_radians();
 let a = (dlat / 2.0).sin().powi(2)
 + lat1.to_radians().cos() * lat2.to_radians().cos()
 * (dlon / 2.0).sin().powi(2);
 let c = 2.0 * a.sqrt().asin();
 r * c
}

Then use it in SQL:

SELECT name, haversine(lat, lon, -34.6037, -58.3816) AS distance_km
FROM stores
WHERE distance_km < 10
ORDER BY distance_km;

This runs at native speed inside the SQLite query engine. No external service calls, no UDF overhead from interpreted languages. For geo queries on edge databases, this is a game-changer.

Operational Reality

Let me be honest about the rough edges. SQLite at the edge isn't all sunshine:

Schema migrations are tricky. You can't run ALTER TABLE ... ADD COLUMN with a default value on a large table without locking it. libSQL improves this, but vanilla SQLite's ALTER TABLE is famously limited.
Monitoring is immature. There's no pg_stat_statements equivalent. You have to instrument your application code or use SQLite's sqlite3_trace_v2 callback.
Backup requires thought. You can't just pg_dump. LiteFS handles continuous backup to S3, but point-in-time recovery requires keeping WAL files.
Connection pooling doesn't exist (because there are no connections). This is actually a feature, no more PgBouncer configuration nightmares, but it means your application must handle SQLite's busy timeout correctly.

Despite these issues, I'm genuinely convinced that SQLite at the edge is the right architecture for a large class of applications that are currently over-provisioning with Postgres or MySQL. The performance characteristics are too good to ignore, and the tooling has finally matured to the point where it's production-ready.

The database world is having a "right tool for the right job" moment. Postgres is exceptional, but not everything needs a client-server database. Sometimes the best database is the one embedded in your application, replicated to every edge node, serving reads in microseconds.

SQLite at the Edge: LiteFS, Turso, and Why Postgres Isn't Always the Answer