Distributed SQLite: Choose Recovery Before Choosing a Cluster

SQLite does not become the wrong database the moment an application needs resilience. But “distributed SQLite” is not one feature you can turn on: it describes several different answers to what should happen when a machine disappears. A new retrospective on twelve years of rqlite has brought that distinction back into focus. For developers and self-hosters weighing another database service against another cluster, the useful question is not whether SQLite can scale. It is which failure you are trying to survive, and how much operational machinery that promise actually requires.

The retrospective reached 202 points on Hacker News when checked for this article. That is a signal of developer interest, not evidence of production adoption or a performance benchmark. The news hook is a project looking back, rather than a new release. Its relevance now is architectural: SQLite-based systems have multiplied, while the language used to describe their reliability often makes unlike designs sound interchangeable.

Twelve years, several different problems

In the retrospective, rqlite creator Philip O’Toole traces the project to September 2014: SQLite paired with a replicated Raft log. His central distinction is more useful than a winner-takes-all history of databases. A system that coordinates writes across a majority, a system that continuously copies data for later recovery, and a system that merges independent changes are solving different problems. Calling all three distributed storage does not make their guarantees equivalent.

That distinction should change how a team starts its evaluation. “We need replication” is an implementation preference masquerading as a requirement. “We can lose at most a minute of edits and must recover within thirty minutes” is something an operator can test. “The service must accept writes after one server fails” is a different requirement. “Two disconnected offices must both keep editing” is different again. A product shortlist drawn up before those sentences are written will often optimize the wrong thing.

The original retrospective is linked below for its historical account. This article’s recommendation is an operational interpretation: choose the failure contract first, then the replication mechanism. That approach avoids turning a modest application into a distributed-systems project merely because a database category has become fashionable.

Recovery and availability are different purchases

Consider a small internal inventory application. If its server dies overnight, users can tolerate a documented restore in the morning. Streaming SQLite changes to separate storage may be a reasonable starting point. Litestream is built around this single-server recovery model. The attraction is not a magical elimination of risk; it is a smaller running system with an explicit recovery procedure instead of an always-on voting cluster.

This choice still needs two measurements. The recovery point objective describes how much recent committed work may be lost. The recovery time objective describes how long it takes to restore useful service. Neither number can be inferred from the existence of a backup job. Replication lag, storage credentials, database size, application deployment, and the time needed to reconstruct secrets all affect the result. A backup that nobody has restored is an untested dependency, not a demonstrated recovery capability.

Now change the inventory application into the configuration store for several running services. Losing writes for the duration of a manual restore might block deployments or leave operators unable to correct a bad setting. Here, rqlite’s consensus-based design may justify its extra moving parts. Its official documentation describes a database built for replicated availability rather than write scaling. That is a meaningful fit for compact, important state, but not an argument that every application should use it.

There is also a business distinction. Automatic continuity has recurring costs even when nothing fails: additional machines, monitoring, upgrades, and incident procedures. Manual recovery has costs concentrated around failure and drills. The cheaper option depends on the value of uninterrupted service and the team’s ability to execute recovery, not simply the monthly price of three virtual machines.

A quorum needs independent places to fail

Raft requires a majority of voting members to make progress on writes. In a conventional three-voter deployment, two members constitute that majority, so one voter can fail without removing the quorum. Adding replicas does not automatically multiply write capacity; the leader and replication path still govern committed writes. The practical benefit is a defined response to losing a member, not unlimited horizontal throughput.

A homelab can obscure that distinction. Three database containers running on one mini PC are three processes but one physical failure domain. They can help demonstrate leader replacement after a process exits. They cannot keep the application online after that computer loses power. Three virtual machines backed by one storage appliance may similarly share the failure that matters most. Availability claims should name the physical and administrative dependencies they assume will remain alive.

Geographic separation introduces another trade-off. Keeping voters apart may reduce shared infrastructure risk, but successful writes still depend on communicating with a majority. Network delay and partitions become part of the database’s normal behavior. A remote node is not free resilience if it makes the application’s write latency unpredictable or turns ordinary connectivity trouble into an incident. Benchmark the intended topology, not three local processes on a developer laptop.

For a small team, the right answer may be three independently hosted nodes, a managed database, or no automatic failover at all. A simpler design with an exercised restore can be more dependable than a cluster whose replacement procedures nobody understands. That is not an argument against clustering. It is an argument for including operator competence in the architecture rather than treating it as an unlimited resource.

Compatibility ends where the application meets the database

rqlite uses SQLite, but an application does not interact with it as though it were simply opening a local database file. Its documented interface is HTTP, with separate endpoints for execution and querying and a unified endpoint for mixed requests. This matters before migration: an application built around an embedded SQLite driver may require meaningful adaptation. Familiar SQL does not imply interchangeable connection behavior, transaction handling, or failure semantics.

The API documentation makes two details especially important. Related statements can be executed atomically using the request’s transaction option; applications should not assume they can manage a long conversation with arbitrary BEGIN and COMMIT commands across independent requests. The documentation also warns that HTTP success does not by itself prove SQL success. Clients must inspect the response for database errors. A dashboard that records only HTTP status codes can therefore report healthy requests while application operations fail.

Retries deserve the same attention. A timeout tells the client that it did not receive a timely result; it does not necessarily tell it whether a write committed. Retrying an operation such as “increment this balance” can duplicate its effect unless the application supplies an appropriate uniqueness or idempotency design. This is a general networked-database problem, but it can surprise developers moving from a local function call to a remote database service.

Before switching storage, prototype one complete business operation rather than a trivial SELECT. Include a multi-statement change, a rejected statement, a timeout, and a retry. Check whether the application can distinguish an acknowledged success from a definite failure and an unknown outcome. Those distinctions often reveal more migration work than the schema conversion does.

Read freshness belongs in the application contract

Not every reader needs the same view of recent writes. A status display can often tolerate delayed information; an operation that authorizes a consequential change may need stricter guarantees. rqlite exposes selectable read consistency, so clients should make that choice deliberately rather than assuming that replication automatically gives every query the same semantics. Use the official consistency reference when choosing request parameters, especially around leader changes and replica reads.

The operational implication is broader than a database setting. Write down where stale information changes a decision. A cached list of service names and a check that a resource remains available may look like equally ordinary SELECT statements in code, yet their correctness requirements differ. Review the workflow, not just the query. Stronger database reads also do not fix an application that separately checks a condition and later updates it without protecting the combined operation.

Replica-based read scaling introduces its own boundary: lower-latency access is useful only if the information is fresh enough for the job. Monitoring should therefore distinguish successful answers from sufficiently current answers. If the product cannot describe what should happen when fresh data is unavailable, operators will end up improvising that policy during a network incident.

What This Means: a practical evaluation checklist

For developers, the takeaway is to treat replicated SQLite as an architectural choice rather than a drop-in durability upgrade. For IT leaders, it is to demand measurable failure behavior instead of counting replicas. For homelabbers, it is to match the experiment to a real need: learning consensus is a valid goal, but it is not the same goal as minimizing maintenance for a family photo index or personal dashboard.

A useful evaluation can stay small while still being rigorous:

• Define acceptable data loss and downtime separately, using numbers the application owner understands.
• Identify shared power, storage, network, account, and deployment dependencies before counting independent replicas.
• Measure normal and degraded write latency with representative transactions on the intended hardware and network.
• Test the actual client path, including response-body errors, timeouts, retries, and ambiguous write outcomes.
• Restore an off-cluster backup into an isolated environment and verify meaningful application data.
• Document who can replace a failed member, rotate credentials, and reverse a problematic deployment.

These checks expose a commonly missed risk: replication is not protection against every kind of loss. An authorized deletion, a faulty migration, or a compromised application can send damaging changes through a perfectly healthy cluster. Separate retained backups and restricted credentials remain necessary. High availability keeps a service running through certain failures; it does not guarantee that the service is doing the right thing.

For an initial proof of concept, use synthetic data and record the experiment’s scope. Stop a follower and observe application behavior. Then stop the leader and measure the interruption. Separately test what happens when a majority is unavailable: the expected result for consensus writes is refusal to proceed, not a second writable island. A successful test verifies that the system preserves its intended boundary as well as that it resumes service.

Afterward, run the restore drill without relying on the cluster’s live files. Bring up the application against the restored data, inspect a recent known record, and measure elapsed time. Record which credentials and infrastructure definitions were required. This produces an actionable recovery runbook rather than a screenshot proving that a database container started.

Looking forward: judge the promise, not the category

Distributed SQLite will remain a useful category precisely because it contains different answers. Some applications need a durable local database and a dependable recovery path. Others need shared state that survives losing a host without waiting for a restore. Offline-first applications need a plan for reconciling divergent changes. None of these requirements becomes obsolete because another approach wins attention on a developer forum.

What readers should watch next is not merely another benchmark or a larger feature list. Look for clearer documentation of acknowledgment semantics, recovery procedures, application integration, and behavior under partial failure. Evaluate managed offerings by the same questions, while recognizing that outsourcing operations can be a rational purchase. Self-hosting removes a service provider from the dependency chain; it does not remove the work the provider would otherwise perform.

The enduring lesson of rqlite’s twelve-year story is that a small storage engine can support serious infrastructure when its surrounding guarantees are explicit. The best design is not the one with the most replicas or the fewest services. It is the one whose behavior during failure matches the application’s needs, and whose operators can demonstrate that match before an outage forces the issue.

Sources and further reading

The original twelve-year retrospective

Hacker News discussion

rqlite features and use cases

rqlite HTTP API and transactions

rqlite read consistency reference

rqlite cluster guidelines

Litestream project