rqlite 9.0: Real-time Change Data Capture for Distributed SQLite

rqlite is a lightweight, user-friendly, open-source, distributed relational database. It’s written in Go, employs Raft for distributed consensus, and uses SQLite as its storage engine.

rqlite 9.0 introduces Change Data Capture (CDC), enabling you to stream the changes occurring to the underlying SQLite database to other systems. With CDC, rqlite is no longer just a distributed database—it’s a live event source. So let’s take a look at what the latest release allows us to do, and some of the design challenges that needed solving — especially building Change Data Capture on SQLite.

What is CDC?

Change Data Capture (CDC) is a mechanism to automatically capture and deliver database change events (INSERTs, UPDATEs, and DELETEs) to external consumers. Instead of periodically polling tables for modifications, CDC continuously streams out changes as they are made to the database. Many modern databases provide CDC — now rqlite does too. Whenever a SQL statement modifies data in rqlite, CDC can push the details of that change (which table, what rows, old values, new values, etc.) to a user-defined HTTP endpoint. This makes it easy to keep other systems in sync with rqlite’s state or trigger application logic based on database events.

Enabling and Configuring CDC

Enabling CDC in rqlite is straightforward. You supply a web hook endpoint URL and rqlite will then POST all change events to that URL as they occur. For example, to enable CDC with a default configuration, launch rqlite with the -cdc-config flag pointing to your endpoint:

Example Event Payload

So what do CDC events look like? rqlite packages changes into a JSON object for each HTTP POST. The JSON contains metadata about which node sent it, and an array of one or more event groups. Each event group corresponds to a committed change (identified by the Raft index). Within each group is a list of row-level events that occurred as a result of the SQL statement. Let’s work through a quick example of CDC event generation.

rqlited -cdc-config="stdout" ~/node-data

Once this single node becomes leader, let’s create a table and insert a row. "stdout" is a special endpoint value, which means rqlite will simply print each CDC event. This can be useful for debug and testing — as well as demonstrations.

curl -XPOST 'localhost:4001/db/execute?pretty' -H 'Content-Type: application/json' -d '[
"CREATE TABLE users (id INTEGER NOT NULL PRIMARY KEY, name TEXT)",
"INSERT INTO users(id, name) VALUES(7, \"fiona\")"
]'

Here is the CDC event that results:

{
  "node_id": "localhost:4002",
  "payload": [{
    "index": 3,
    "commit_timestamp": 1757892884812603,
    "events": [{
      "op": "INSERT",
      "table": "users",
      "new_row_id": 7,
      "after": {
        "id": 7,
        "name": "fiona"
      }
    }]
  }]
}

In this example, a new row was inserted into the users table with primary key id=7. The CDC event shows op: "INSERT", the table name, and the primary key of the new row.

Because this is an insert, there is no “before” state – but the “after” object contains the data for the new row. The index: 3 means this change was part of the Raft log entry #3, which can serve as a globally unique event ID. The commit_timestamp field is a timestamp (in milliseconds since 1970) when the event was first committed. For update operations, both a before and after map would be present, showing the row’s contents before and after the update. For deletes, only a before would be provided (and after would be empty or omitted). If you configure CDC in row-IDs only mode, the before/after column data can be omitted entirely to reduce overhead – in that case you’d only see the row IDs and operation type.

The CDC JSON schema is designed to be easy to consume. Your webhook service could, for example, read the JSON and apply the changes to a caching layer or send them to a message queue. Because each event includes the table name and primary keys (and optionally the full row data), there are many possibilities for integration: you can invalidate cache entries, propagate updates to downstream systems, trigger alerts on certain table changes, and so on.

Let’s dive into the design

Building CDC on top of a Raft– and SQLite-based database presented some interesting challenges. Let’s take a look at each, and see how rqlite solves them.

Collecting the Events

The first thing rqlite needs to do is to reliably capture changes to its underlying SQLite database. To do this rqlite registers a pre-update hook and a commit hook on the SQLite database. Once a committed Raft log is applied to the SQLite database, the pre-update hook gathers details about every row affected (including both the “before” and “after” state of the row, if applicable) and tags them with the index of the Raft log containing the source SQL statement. Then, when the transaction commits, the commit hook fires and hands off an event group to rqlite’s new CDC subsystem. In this way, CDC captures changes inline with the normal commit process, ensuring no changes are missed.

Reliable at-least-once delivery

rqlite guarantees that each event is sent to the HTTP endpoint at least once. Typically, every event is sent only once, but duplicates are possible in two specific scenarios:

1. Node Restart: A Raft system rebuilds its state on restart by replaying its log. In principle, this means re-executing all the SQL statements, which naturally regenerates all the corresponding CDC events. These are the first type of duplicates rqlite must suppress.
2. Leadership Change: By design, only the cluster Leader transmits CDC events. If a Leader sends an event batch and then fails or is deposed before it can broadcast its progress to other nodes, a newly elected Leader has no way of knowing what the old Leader already sent. The new Leader will then start processing CDC events, potentially re-transmitting those same events.

It is impossible to completely eliminate duplicates in these distributed scenarios (this isn’t unique to rqlite; it’s a fundamental constraint). What rqlite does do is ensure every event is sent at least once and typically only once.

The CDC FIFO Queue

A key component in the design is the CDC FIFO queue.

When CDC is enabled each node maintains a disk-backed FIFO queue of change events. Since every node in a rqlite cluster writes to its local SQLite database, CDC events are therefore generated on each node. Each of these events is next written by the node to its FIFO queue as soon as it’s generated. In this way the CDC system on each node has its own copy of events that are to be transmitted to the HTTP endpoint. But during normal operation — and this is key — only the Leader reads from its queue, and only the Leader actually transmits CDC events to the HTTP endpoint.

On node start-up, if it’s the Leader, it will read all queued events and transmit them to the HTTP endpoint. The same thing also occurs when a new Leader is elected. In this way we can be sure that every event is sent at least once, even across a restart or Leader election.

High-water mark and duplicate suppression

rqlite also uses a high-water mark (HWM) mechanism to minimize duplicate deliveries. Whenever the Leader node successfully reads a batch of events from its FIFO and transmits that batch of events to the HTTP endpoint, it sets the highest Raft index in that batch as the new HWM. The Leader continually broadcasts the HWM to all nodes in the cluster. In response to these broadcasts each node (Leader and Followers alike)  deletes from its FIFO any events as old or older than the current high-water mark. The Leader node will also skip events it reads from the queue if they are older than the current HWM. In other words, if an event is already known to have been delivered, it will not be sent again. This reduces duplicates to zero during normal operation – once an event is delivered, all nodes very quickly learn about it and the event will never be sent again, even if one of those nodes were to become Leader at a later time.

Just an importantly, this design guarantees that no event is skipped – even if a node restarts or loses leadership, it still has a record of all changes that occurred. The queue ensures at-least-once delivery of every change event to the webhook. In fact, the CDC implementation was explicitly designed so that “every event is transmitted at least once”. Events remain safely in each node’s CDC FIFO until that node has confirmation they’ve been delivered.

Why not use the Raft log?

Some readers may be wondering if the CDC FIFO queue is actually needed. After all, doesn’t the Raft log contain all the SQL statements that changed the database? Why store a second, distinct copy of the change events? Couldn’t rqlite use the information stored in the Raft log to regenerate CDC events as needed?

In theory, yes, rqlite could, but in practice it can’t. Why? Because all practical Raft systems periodically truncate the Raft log to prevent the log from growing without bound. That means that the CDC system might need a Raft entry at a certain index, only to find the Raft system had compacted it away.

The CDC design could have chosen to block truncation of the log until the changes in the Raft log were known to have been transmitted to the HTTP endpoint, but this would mean coupling the very core of the Raft system to the availability of an external HTTP endpoint. It’s easy to see that this would be a mistake, so instead the CDC service on each node maintains its own queue distinct from the Raft log, and manages the truncation of that queue independently.

Transmission to the webhook

CDC events are delivered as HTTP POST requests to the configured webhook URL, encoded as JSON. For efficiency, the CDC service batches multiple events together before sending, up to a configurable maximum batch size or time window. If the webhook endpoint is slow or temporarily unreachable, the Leader will retry sending events based on a configurable retry policy. You can set how many retry attempts to make and whether to drop events after exceeding the limit or keep retrying indefinitely (the latter ensures delivery but may consume unbounded storage if the endpoint is down for a long time). By default, rqlite will continue retrying until the event is delivered, rather than silently dropping changes.

Exactly-once semantics?

Now we understand that rqlite’s CDC is at-least-once by design, not exactly-once (which would be ideal but is extremely difficult to guarantee with external endpoints). But if your consuming application needs to guard against duplicates, it can do so by using the unique Raft log index included with each event. Every CDC event group carries the monotonically increasing log index at which it was committed. An external consumer can track the highest index it has processed and ignore any repeated or lower-index event it sees. This way, you can effectively de-duplicate on the client side if needed. Alternatively since CDC events for a given index never change, you may be able to simply reprocess them, if your consuming system operates in an idempotent manner.

I chose this approach to keep CDC robust and simple – no external acknowledgements or complex two-phase commit with the webhook are required. Under normal circumstances, the high-water mark mechanism means you won’t see duplicates, but it’s good to know the system’s guarantees. The bottom line: every change will be delivered at least once, and typically only once, unless a Leader restarts or Leadership change occurs

Testing

The new CDC system has been extensively testing, including comprehensive unit test and system testing. There are also new end-to-end tests. Load testing was also performed, to ensure rqlite doesn’t leak resources (such as goroutines and memory) when CDC is running for an extended period.

Next Steps

Looking to future releases, further CDC improvements are planned:

• Streaming events to other systems such as Apache Kafka and Cloud-based PubSub systems.
• Emitting CDC events when the database schema itself changes e.g. when a CREATE TABLE command is executed.

Be sure to download rqlite release 9.0 and try CDC today. Check out the docs for configuration examples, and discussion is welcome on Slack.