CONCEPT Cited by 1 source
Bolt-vs-SQLite storage choice¶
Definition¶
The design decision between a key-value embedded store (e.g. BoltDB) and a full-SQL embedded store (e.g. SQLite) for a service's state. Both run in-process, both durable, both single-file; the difference is the query surface they expose. Pick by the blast radius of an ad-hoc query against your data, not by feature breadth.
Two sides¶
Case for key-value (Bolt)¶
A key-value store with buckets and explicit code paths to
mutate data cannot be foot-gunned by an ad-hoc query. You
cannot UPDATE flyd_machines SET state='stopped' WHERE ...
across a whole fleet by accident or under pressure, because
the interface does not admit SQL.
JP Phillips on flyd's choice:
I still believe Bolt was the right choice. I've never lost a second of sleep worried that someone is about to run a SQL update statement on a host, or across the whole fleet, and then mangled all our state data. And limiting the storage interface, by not using SQL, kept flyd's scope managed.
On the engine side of the platform, which is what flyd is, I still believe SQL is too powerful for what flyd does. (Source: sources/2025-02-12-flyio-the-exit-interview-jp-phillips)
Two arguments bundled together:
- Blast-radius safety — no SQL means no fleet-rewriting ad-hoc statement.
- Scope discipline — without query flexibility, features that want SQL stay out of flyd. The store shapes the service.
Case for SQL (SQLite)¶
Consumers of the data want to ask rich questions. An indexer wants joins. A router wants filters with ORDER BY. A dashboard wants aggregates. If the store is also the read-side query layer for other services, a KV API is a straitjacket.
corrosion2 — Fly.io's own SWIM- gossip state-distribution layer — uses SQLite for exactly this reason: "any component on our fleet can do SQLite queries to get near-real-time information about any Fly Machine around the world."
The decomposition¶
| Dimension | Key-value (Bolt) | SQL (SQLite) |
|---|---|---|
| Role | Engine / authoritative | Read-side / observation |
| Ad-hoc mutation | Impossible (by design) | Trivial — UPDATE works |
| Query flexibility | None | Full SQL |
| Scope pressure | Contains | Expands |
| Consumer surface | The owner process | Anyone with DB access |
When to pick each¶
- Bolt-shaped when the service owns its state exclusively, mutations are authored by the service's own code, and the blast radius of an accidental bulk update is unacceptable. flyd is the canonical wiki instance.
- SQLite-shaped when the service's job is to publish state to other services that want to query it ad-hoc. corrosion2 is the canonical wiki instance.
The two shapes can coexist in one architecture — flyd is Bolt-shaped (authoritative per-worker state) while corrosion2 is SQLite- shaped (read-side state distribution), in the same Fly.io stack.
Per-instance SQLite as a third corner¶
A per-instance SQLite ([[patterns/per-instance-embedded- database]]) reshapes the Bolt-vs-SQLite choice: you keep SQL's query power but bound the blast radius to one instance (Machine / object / tenant). JP Phillips's 2025-02-12 counterfactual for flyd — "I'd maybe consider a SQLite database per-Fly-Machine. Then the scope of danger is about as small as it could possibly be" — is the clearest wiki framing of this as a third option, not a compromise between the two.
Seen in¶
- sources/2025-02-12-flyio-the-exit-interview-jp-phillips — JP Phillips defends Bolt over SQLite for flyd; same interview introduces per-Machine SQLite as an alternate design with schema-management as the open problem.