Databases # SQLite at the Edge: LiteFS, Turso, and Why Postgres Isn't Always the Answer 2025-10-309 min read Lucio Durán Engineering Manager & AI Solutions Architect Also available in: [Español]() ## 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: 1. Replication is SQL-agnostic (works with any SQLite extension, custom functions, etc.) 2. The overhead is proportional to data changed, not query complexity 3. There's no need to parse or validate SQL on the replica side ### Setting Up LiteFS on Fly.io ```yaml # 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: ```javascript 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: ```rust // 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: ```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: 1. **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. 2. **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. 3. **Backup requires thought.** You can't just ` pg_dump `. LiteFS handles continuous backup to S3, but point-in-time recovery requires keeping WAL files. 4. **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. ## Frequently asked questions ### 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. ## Tips 1. **WAL mode is non-negotiable:** Always run ` PRAGMA journal_mode=WAL; ` before anything else. WAL (Write-Ahead Logging) allows concurrent readers during writes. Without it, a single write locks the entire database. LiteFS requires WAL mode, it won't replicate correctly with rollback journals. 2. **Turso embedded replicas latency trick:** Use ` @libsql/client ` with ` syncUrl ` and ` syncInterval ` to create a local SQLite replica that syncs with Turso's edge network. Reads hit the local file (~0.1ms), writes go to the primary (~50ms). Set ` syncInterval: 1000 ` for near-real-time with ` client.sync() ` for on-demand freshness. 3. **LiteFS TXID-based read consistency:** LiteFS exposes the current transaction ID at ` /litefs/.primary ` endpoint. After a write, capture the TXID and pass it via ` Fly-Replay: state=txid_value ` header. The replica will block until it has replicated up to that TXID, giving you read-your-writes consistency without routing all reads to primary. 4. **SQLite page size tuning:** Default page size is 4096 bytes. For large BLOB-heavy workloads, increase to ` PRAGMA page_size=65536; ` before creating the database (it's immutable after). For small-row OLTP, 4096 is optimal. LiteFS replicates at page granularity, so larger pages mean more data per replicated change. - sqlite - litefs - turso - libsql - edge-computing - distributed-databases - fly-io [Back to Blog]()