Operating SQLite at Scale — Real Patterns

The patterns that show up over and over

SQLite-at-scale isn't 'one giant database'; it's almost always 'many SQLite databases'. The patterns that recur:

Per-tenant databases — each customer/user gets their own file. Backup, migration, even deletion become per-tenant operations. Scales horizontally with the number of tenants.
Read replicas via Litestream — one writer, N read-only replicas. Pull-based, eventually consistent.
Hot/cold split — current data in a small SQLite file, archived data in a separate file you ATTACH when needed.
Edge replicas — Turso / D1 push the same logical database to many regions; reads are local, writes route to a primary.
Sharded by user-id hash — for products that don't fit the per-tenant pattern but still want SQLite's deployment simplicity.

Self-reference: Pippa is the simplest case — one user (Dad), one database. The patterns above don't apply yet. They would if Pippa ever served other Dads — but the local-first design means each instance would just have its own database, which is itself the per-tenant pattern.

Code

ATTACH for hot/cold split·sql

ATTACH DATABASE '/path/to/archive.db' AS archive;

-- Query across both
SELECT id, body FROM messages
UNION ALL
SELECT id, body FROM archive.messages;

-- Move old rows to archive
INSERT INTO archive.messages SELECT * FROM messages
WHERE created_at < datetime('now', '-1 year');

DELETE FROM messages WHERE created_at < datetime('now', '-1 year');

DETACH DATABASE archive;

Exercise

Pick one product or service you've worked on that uses a large shared Postgres. Sketch how it would look as per-tenant SQLite databases: where would the boundary be (per-customer? per-org? per-user?), what becomes harder (cross-tenant analytics?), what becomes easier (deletion, backup, migration). Decide whether the tradeoffs net out positive.

Operating SQLite at Scale — Real Patterns

The patterns that show up over and over

Code

External links

Exercise

Progress

Comments 0