Skip to content

PATTERN Cited by 1 source

Per-instance embedded database

Pattern

Colocate a single, embedded database (typically SQLite) with each logical unit of compute — one DB per actor / object / tenant / Machine / session — instead of one shared database for the whole fleet. When the logical unit is destroyed, zip up the database file and archive it (or drop it); the blast radius of any query is bounded to that one unit.

Why

  • Blast radius is structurally small. A bad query, a corrupt row, or a schema migration bug touches one instance's data, not the whole fleet. Canonical blast- radius containment.
  • Per-instance locality. Reads and writes never leave the worker hosting the instance. No network RTT on the data path.
  • Clean lifecycle. Creating the instance creates the DB; destroying the instance retires the DB.
  • Cheap archiving. On destruction, serialise the DB file to object storage. No complex extract / migration / redaction pipeline.

Why not (the hard part)

  • Schema management across a fleet of per-instance databases. If you have 100,000 live per-instance databases and you need to add a column, you need a migration strategy that either runs on-boot (slow start) or lazily (consistency cost) or with a shadow / expand-contract protocol. JP Phillips, on why he did not pick per-Machine SQLite for flyd: "The biggest hold-up I have about it is how we'd manage the schemas." (Source: sources/2025-02-12-flyio-the-exit-interview-jp-phillips)
  • Aggregate queries require fan-out. "Show me all Machines in org X with state Y" needs to query all instances. A shared SQL database answers it with an index.
  • Backup / disaster recovery at fleet scale is harder than one-managed-DB-with-snapshots.

Canonical instances in this wiki

  • Cloudflare Durable Objects — one SQLite per Durable Object. The DO runtime handles storage lifecycle, hibernation, and serialisation across eviction. The Durable-Object model pushes per-instance- database schema management onto the developer via the DO code itself.
  • patterns/runtime-provisioned-per-tenant-search-index — per-tenant search index is the per-instance-DB pattern specialised for search; same blast-radius property.
  • Hypothetical: per-Fly-Machine SQLite, as floated by JP Phillips in the 2025-02-12 exit interview. Not built at Fly.io — flyd uses a single BoltDB file per host, not a SQLite per Machine — but JP's framing is the cleanest wiki statement of the "zip the DB to object storage on Machine destroy" property the pattern gives you for free.

Adjacent patterns

  • Shared database with per-tenant isolation (row-level security, per-tenant schemas) is the opposite architectural choice: one DB, many tenants, ad-hoc queryability across tenants, larger blast radius per bad query.
  • Per-host database (one DB per physical worker) is a middle ground — smaller blast radius than shared, larger than per-instance. flyd's BoltDB sits here.

Seen in

Last updated · 200 distilled / 1,178 read