Twelve Years in the Making: Practical Lessons from Distributed SQLite

In the world of distributed databases, SQLite always seems like an awkward fit—it's lightweight, embedded, and runs on a single machine, which appears to run counter to "distributed." Yet Philip O'Toole, in his article Twelve years of distributed SQLite, looks back on a twelve-year exploration showing how SQLite can be transformed into a highly available, strongly consistent distributed system. The article earned 276 points and 42 comments on Hacker News, sparking deep discussion about distributed database design.

Why Choose SQLite as a Distributed Foundation

SQLite's advantages lie in zero configuration, serverless operation, full transactional integrity (ACID), and extremely low latency. O'Toole points out that many application scenarios don't require complex distributed queries—they need simple, reliable data storage. By combining SQLite with the Raft consensus algorithm, he built rqlite—a distributed SQLite system written in Go. rqlite uses SQLite as its storage engine and replicates logs via the Raft protocol to achieve multi-node data consistency and fault tolerance.

The core insight behind this design is: SQLite's maturity and stability far exceed most homegrown storage engines. Rather than reinventing the wheel, it's better to build a distributed layer on top of SQLite. O'Toole mentions in the article that rqlite has been running stably for years, handling various failure scenarios in production environments.

The Raft consensus algorithm is a distributed consistency protocol for managing replicated logs, proposed by Diego Ongaro and John Ousterhout in 2014, designed to be easier to understand and implement than Paxos. Raft decomposes the consistency problem into three relatively independent sub-problems: Leader Election, Log Replication, and Safety. In Raft, cluster nodes exist in three states: leader, follower, and candidate. The leader receives client requests and replicates operations as log entries to other nodes; followers passively respond to requests from leaders and candidates; when the leader fails, followers trigger an election via heartbeat timeout, and the candidate that wins the majority vote becomes the new leader. Raft guarantees that as long as a majority of nodes in the cluster are alive, the system can continue operating and maintaining consistency. Compared to Paxos, Raft's strong leader model and clear log matching properties make it a popular choice for building distributed systems—etcd, Consul, TiKV, and others all use Raft. rqlite leverages Raft to replicate SQL statements as log entries to all nodes, thereby achieving strongly consistent multi-replica SQLite.

The Architectural Evolution of Distributed SQLite

Over twelve years, rqlite's architecture has undergone multiple iterations. Early versions ran SQLite instances directly on each node and replicated SQL statements via Raft. But this approach had performance bottlenecks—all write operations had to be serialized through the leader node, and SQL statement replication could produce non-determinism (for example, using random numbers or time functions).

Later, rqlite shifted to state machine replication based on the Raft log, ensuring all nodes execute the same SQL statements in the same order. O'Toole emphasizes that avoiding non-deterministic operations is a key challenge for distributed SQL. To this end, rqlite introduced read-write separation: read requests can be executed on follower nodes, while write requests must be forwarded to the leader. This significantly improves read throughput while maintaining strong consistency.

Another important evolution is snapshotting and log compaction. The Raft log grows continuously, and rqlite compacts the log by periodically generating SQLite database snapshots, reducing storage and recovery time. O'Toole reveals that the snapshot mechanism has been optimized multiple times and can now perform near-transparent online compaction.

State Machine Replication (SMR) is a core technique for achieving fault tolerance in distributed systems. The basic idea is: if all replicas start from the same initial state and execute the same deterministic operations in the same order, they will eventually reach the same state. In rqlite's case, the SQLite instance on each node is a state machine, and the Raft log defines the order of operations. As long as log entries are applied in the same order on all nodes, the SQLite databases on each node will remain consistent. However, SMR requires operations to be deterministic—the same input must produce the same output and state changes. Functions like RANDOM(), CURRENT_TIMESTAMP, and datetime('now') in SQL statements produce non-deterministic results; if these statements are replicated directly, different nodes may get different values, causing state divergence. Therefore, rqlite needs to evaluate these non-deterministic functions into concrete constants on the leader node before writing them to the log, or intercept and rewrite such functions at the SQLite layer. This is the technical root of the article's emphasis that "avoiding non-deterministic operations is a key challenge for distributed SQL."

Lessons and Experiences from Production Environments

Over twelve years of operations, O'Toole has summarized several key lessons:

• Network partitions are the norm: Distributed systems must gracefully handle node disconnection. rqlite automatically switches leaders through Raft's election mechanism, but timeout parameters need to be configured properly to avoid frequent elections.
• Monitoring and observability are crucial: rqlite exposes rich metrics, including Raft state, replication lag, and SQLite performance. O'Toole recommends users monitor these metrics to detect potential issues early.
• Backup strategies cannot rely on a single mechanism: Although Raft provides redundancy, logical errors (such as accidentally dropping a table) will still be replicated to all nodes. Therefore, regular backups of SQLite files remain necessary.

Additionally, O'Toole mentions that feedback from rqlite's community users has helped uncover many edge cases, such as memory pressure under large transaction scenarios and connection pool management under high concurrency. These practical experiences cannot be derived from theory—they can only be accumulated through long-term iteration.

Network Partition refers to the phenomenon in distributed systems where nodes cannot communicate due to network failures. When a partition occurs, the cluster may be split into multiple subsets, where nodes within each subset can communicate with each other but subsets cannot communicate across. For Raft-based systems, network partitions directly trigger leader election: if the partition containing the leader cannot communicate with a majority of nodes, the followers on the majority side will time out and initiate an election, producing a new leader; while the partition containing the old leader cannot obtain a majority vote and thus cannot commit new log entries, avoiding Split-Brain. rqlite automatically handles this scenario through Raft's election mechanism, but the configuration of timeout parameters is crucial: an Election Timeout that is too short leads to frequent elections, affecting availability; too long leads to slow failover. It is generally recommended to set the election timeout to several times the heartbeat interval and adjust it based on network latency characteristics. Furthermore, after a network partition heals, minority nodes need to synchronize missing log entries from the leader, and rqlite's log replication mechanism automatically completes this catch-up process.

Implications for Developers

The twelve-year journey of distributed SQLite offers a unique perspective: simplicity can be a competitive advantage for distributed systems. rqlite doesn't pursue complex distributed transactions or cross-shard queries; instead, it focuses on providing a strongly consistent replicated SQLite service. This focus makes the system easy to understand, deploy, and operate.

At the end of the article, O'Toole mentions that he is still continuously improving rqlite and plans to explore more optimization directions, such as SQLite-based WASM extensions and more efficient log replication. For developers building distributed systems, his advice is: start from actual requirements, choose mature foundational components, and be prepared for long-term maintenance.

Twelve years of persistence not only refined a project but also revealed a plain truth about distributed system design—reliability and simplicity are often more valuable than fancy features.

One-line Summary

Philip O'Toole spent twelve years building rqlite, using Raft to extend SQLite into a strongly consistent distributed system.

Paragraph Summary

In Twelve years of distributed SQLite, Philip O'Toole reviews the twelve-year journey of transforming SQLite into the distributed system rqlite. Written in Go and using SQLite as its storage engine, rqlite replicates logs via the Raft protocol to achieve multi-node strong consistency and fault tolerance. The article notes that SQLite's maturity and stability far surpass homegrown storage engines, making it a more pragmatic choice to build a distributed layer on top of it. Architecturally, rqlite evolved from directly replicating SQL statements to state machine replication based on the Raft log, and introduced read-write separation and snapshot compaction to improve performance. Production experience shows that network partitions are the norm, and monitoring, backups, and timeout configuration are crucial. The core insight is: simplicity and reliability are the cornerstones of long-term success for distributed systems.