# Pattern 06 — SQLite WAL mode + synchronous=NORMAL

## The pain

You picked SQLite because you didn't want to run a separate database process on your $5 VPS. Then you started writing concurrently from a Flask request handler and a background collector, and SQLite locked up. Or you noticed every `INSERT` was taking 10+ ms even though the disk is fast. Or both.

The default SQLite mode is **rollback journal + synchronous=FULL**, which is correct for embedded use cases (mobile, desktop) but wrong for a server-class workload with multiple concurrent writers and an SSD. The fix is two PRAGMAs.

## When to use it

- Single-host deployment, single SQLite file.
- Multiple processes or threads write to the same database.
- You're on an SSD (which is every VPS made after 2018).
- You can tolerate losing the last few transactions in the event of a hard kernel crash (a power loss to the box). Almost everyone can.

## The code

In your DB connection helper, set both PRAGMAs immediately after connecting:

```python
import sqlite3
from contextlib import contextmanager

DB_PATH = "/var/data/myapp.db"

@contextmanager
def conn():
    c = sqlite3.connect(DB_PATH)
    c.row_factory = sqlite3.Row
    c.execute("PRAGMA journal_mode=WAL")
    c.execute("PRAGMA synchronous=NORMAL")
    try:
        yield c
        c.commit()
    finally:
        c.close()
```

That's it. Two lines. They're idempotent — running them on every connection is fine.

## What each PRAGMA does

**`journal_mode=WAL`** switches SQLite from rollback-journal mode to write-ahead-log mode. The implications:

- Concurrent reads no longer block writes, and a single writer no longer blocks readers. (In rollback-journal mode, any writer takes an exclusive lock that blocks all readers.)
- Writes go to a `<dbname>-wal` sidecar file and are checkpointed back to the main DB file periodically (default every 1000 pages).
- The mode persists across processes and across opens — once you set it, the database file *is* in WAL mode until you switch back. You don't need to set it on every connection (but it's harmless to do so).

**`synchronous=NORMAL`** tells SQLite to call `fsync()` on the WAL file at every commit, but NOT at every page write within a transaction. The implications:

- Throughput goes from ~100 commits/sec to ~10,000 commits/sec on a typical SSD.
- In the event of a power loss, you may lose the last fully committed transaction (the one whose `fsync()` was in flight when the power went out). The DB itself never corrupts — only that one transaction is lost.
- The default `synchronous=FULL` calls `fsync()` after every page write, which is paranoia for SSD-class storage and was originally written for spinning rust where `fsync()` cost was much smaller relative to seek cost.

## When NOT to use it

- **You're on an HDD with no battery-backed write cache**, AND you absolutely cannot afford to lose the last committed transaction. This is a real concern in financial settlement systems and almost nowhere else. Use `synchronous=FULL` instead.
- **You're using SQLite as a multi-machine shared database** (e.g. mounted on NFS). Don't do this. WAL on networked filesystems is broken because WAL relies on shared memory mapping that NFS does not honor. Use Postgres.
- **You have a single writer and high-throughput readers and you don't care about reader/writer concurrency**. WAL mode adds a small write overhead from maintaining the WAL file. If you're write-once, read-many, plain rollback journal is marginally faster.

For the other 99% of use cases, set both PRAGMAs and move on.

## Throughput in practice

I run a collector that does ~6,800 `INSERT OR REPLACE` operations every 5 minutes (one per perpetual futures contract across 20 exchanges). With WAL + synchronous=NORMAL on a Hetzner $5 VPS NVMe:

- Insert batch (6,800 rows in one transaction): **~80 ms**
- Concurrent reads from the API server during the insert batch: **never blocked, never even noticeably slower**
- Database file size after 10 days: 80 MB

With the default rollback-journal mode, the API server would intermittently return 500s when the collector was committing because of lock contention. With WAL, that's gone.

## Further reading

- SQLite official WAL documentation — https://www.sqlite.org/wal.html
- "When to use WAL mode" (from sqlite.org) — https://www.sqlite.org/wal.html#when_to_use_wal_mode
- Charles Leifer, "SQLite for the modern web" — https://charlesleifer.com/blog/going-fast-with-sqlite-and-python/
- The `synchronous` PRAGMA spec — https://www.sqlite.org/pragma.html#pragma_synchronous

## The summary

Two lines. Set them once. Forget about SQLite concurrency for the rest of your life:

```python
c.execute("PRAGMA journal_mode=WAL")
c.execute("PRAGMA synchronous=NORMAL")
```