ADR 007: Adoption of SQLite WAL Mode for Single-Node Workloads

📊 Status

Accepted (Supersedes ADR 003) — 2026-10-09

👥 Deciders

• Architecture Team
• Core Infrastructure Lead

🔍 Context & Problem Statement

Our application requires sub-millisecond read latency for metadata lookups and paste resolutions. Running a standalone managed PostgreSQL cluster introduces network hops (~2-5ms latency per query) and recurring cloud infra costs. We need an embedded, zero-maintenance database engine that satisfies ACID guarantees with high concurrent read throughput.

💡 Decision Drivers

1. Zero external network latency for read-heavy workloads (95% reads, 5% writes).
2. Minimal operational complexity and memory footprint on modest VPS instances.
3. Strict ACID transactions with atomic commits.
4. Seamless automated file backups and replication.

⚖️ Considered Options

• Option 1: Standalone PostgreSQL 16 with PgBouncer connection pool.
• Option 2: Embedded SQLite with WAL mode and memory tuning pragmas.
• Option 3: Redis Stack as primary persistent data store.

✅ Decision Outcome

Chosen Option: Option 2 (Embedded SQLite with WAL mode).

Rationale:

SQLite running with PRAGMA journal_mode = WAL permits concurrent readers without locking writers. In-process memory execution brings single-row lookup times under 0.2ms. Litestream can be attached for continuous real-time S3 streaming backups.

📈 Consequences

Positive:

• Query latency dropped from 3.8ms (PG TCP) to 0.15ms (direct SQLite).
• Zero external database hosting fees.
• Instant dev environment setup with single file database.sqlite.

Negative & Mitigations:

• Write Concurrency: Only one writer active at a time.
  • Mitigation: Enable PRAGMA busy_timeout = 5000 and defer heavy batch jobs to background worker queues.