---
title: "SQLite on the Server: WAL Tuning, Litestream Replication, and Single-Writer Concurrency for 100K req/s"
published: true
description: "A hands-on guide to replacing PostgreSQL with SQLite for indie SaaS — covering WAL mode tuning, Litestream S3 replication, and single-writer patterns in Ktor and Spring Boot."
tags: [architecture, kotlin, backend, cloud]
canonical_url: https://blog.mvpfactory.co/sqlite-server-litestream-wal-tuning-single-node-architecture
---
## What We're Building
In this workshop, I'll walk you through the exact production setup I recommend for indie SaaS projects: SQLite in WAL mode on a single Linux node, with Litestream streaming continuous backups to S3. We'll tune the pragmas, wire up single-writer concurrency in both Ktor and Spring Boot, and I'll show you where this architecture breaks down — so you build the escape hatch before you need it.
By the end, you'll have a setup capable of 100K+ reads/second with sub-millisecond latency and zero database costs.
## Prerequisites
- A Linux VPS (4-core minimum for the benchmarks discussed here)
- ext4 or btrfs filesystem
- JDK 17+ (for Ktor or Spring Boot examples)
- An S3-compatible bucket (AWS S3, Backblaze B2, MinIO)
- [Litestream](https://litestream.io/) installed on your server
## Step 1: Tune WAL Mode for Server Workloads
Let me show you a pattern I use in every project. These pragmas turn SQLite from an embedded toy into a production-ready server database:
sql
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -64000; -- 64MB
PRAGMA foreign_keys = ON;
PRAGMA wal_autocheckpoint = 1000;
The tuning decision that matters most is `synchronous = NORMAL` instead of `FULL`. On ext4, this gives you durability against application crashes while accepting a theoretical risk during OS-level crashes — in practice, I've never seen it bite anyone. On btrfs, copy-on-write semantics add another safety layer, but watch for write amplification. Mount with `nodatacow` on the database directory if write throughput matters.
## Step 2: Configure Litestream for Continuous S3 Replication
Litestream streams WAL changes to S3 with sub-second replication lag. No more cron-based `sqlite3 .backup` scripts.
yaml
dbs:
- path: /data/app.db
replicas:
- type: s3 bucket: my-saas-backups path: replica retention: 72h
Recovery is a single command:
bash
litestream restore -o /data/app.db s3://my-saas-backups/replica
Here's the minimal setup to get this working: your entire disaster recovery plan fits in a Dockerfile. That's not a simplification — it literally fits in a Dockerfile.
## Step 3: Wire Up Single-Writer Concurrency
SQLite allows exactly one writer at a time. This is the constraint you design around — make it visible in your code.
**Ktor with coroutine mutex:**
kotlin
val writeMutex = Mutex()
suspend fun executeWrite(block: (Connection) -> Unit) {
writeMutex.withLock {
dataSource.connection.use { block(it) }
}
}
**Spring Boot with HikariCP:** configure a separate read pool (size 4–8) and a write pool with `maximumPoolSize=1`. The write mutex adds microseconds of overhead. Skipping the network round-trip to PostgreSQL saves milliseconds.
## Step 4: Know the Benchmarks
Typical SaaS workloads are 80–95% reads. On a standard 4-core VPS:
| Metric | SQLite (WAL) | PostgreSQL (managed) |
|---|---|---|
| Simple reads/sec | ~100K+ | ~20–40K (network bound) |
| Writes/sec (sequential) | ~5K–10K | ~5–15K |
| Concurrent writers | 1 | Hundreds |
| P99 read latency | <0.1ms | 1–5ms |
| Monthly cost | $0 | $50–200 |
SQLite wins on reads because there's no protocol overhead — it's a function call into the same process.
## Gotchas
**Don't rely on `busy_timeout` alone.** Serialize writes explicitly at the application layer. Relying on SQLite's busy timeout to silently handle contention is a recipe for mysterious production latency spikes.
**Multiple application servers kill this architecture.** SQLite lives on one disk. The moment you need horizontal scaling, you need a networked database. Full stop.
**Heavy analytics block your writer.** Complex reporting queries will block your single writer during WAL checkpoints. If your product needs both OLTP and analytics, you'll feel the pain quickly.
**Keep your SQL standard-compliant from day one.** Avoid SQLite-specific functions. Use an ORM like [Exposed](https://github.com/JetBrains/Exposed) (Kotlin) or JDBI. When migration day comes — and for many indie SaaS products it never does — swapping to PostgreSQL becomes a connection string change plus a data migration script.
The docs don't mention this, but your concrete migration triggers should be: multiple application servers needed, write throughput consistently exceeding 5K/sec, or analytical queries degrading user-facing performance. Until you hit those, you're paying for complexity you don't need.
## Conclusion
Start with SQLite in WAL mode and Litestream for any new indie SaaS project. You eliminate operational overhead, reduce database costs to zero, and get sub-millisecond read latency out of the box. As someone who spends long hours at a desk architecting these systems ([HealthyDesk](https://play.google.com/store/apps/details?id=com.healthydesk) keeps me from fusing with my chair by reminding me to actually move between design sessions), I can tell you: the simplest architecture that meets your requirements is the right starting point.
Build the PostgreSQL escape hatch by keeping your SQL standard-compliant — but don't build PostgreSQL support you don't need yet.
Top comments (0)