James Ainslie

SQLite's new JSONB format addresses work that ordinary JSON queries repeat. A database can retain a document for months while parsing the same punctuation whenever a query needs one of its fields. Editing that document adds another conversion when the engine renders its internal representation back into text. SQLite 3.45.0 makes that internal representation suitable for persistent storage. With rqlite 8.18.0, the same facility becomes available inside a replicated relational database.

The interesting part is the decision to store the parse representation itself. JSONB does not require an application to invent a compact document encoding, and it does not replace SQL with a document API. SQLite still owns the conversion and the operations on the value. The application chooses whether retaining JSON as text remains useful, or whether the database should retain the representation its JSON functions already need.

I like rqlite because the amount of distributed systems machinery feels proportionate to the problem. SQLite supplies the relational engine, and rqlite adds replication through Raft. An improvement inside that engine is particularly appealing when it can reduce local work without adding another service to operate. JSONB fits that preference, provided its benefits are understood at the level where they actually occur.

Saving the representation between statements

The SQLite 3.45.0 release notes, dated January 15, describe a rewrite of the JSON functions around a serializable internal parse tree. Earlier processing translated JSON text into an internal representation, performed the requested operation, and generated text again. JSONB allows the database to retain the intermediate form. A subsequent operation can start with that form instead of reconstructing it from text.

SQLite's version-pinned JSONB design document explains the encoding. Every element has a header that records its type and payload size. Those headers replace punctuation that a text parser would otherwise have to interpret. An object contains pairs of elements for its field names and values. An array contains its constituent elements, each with its own header. The structure gives the reader lengths directly instead of requiring it to find every closing delimiter through a syntax scan.

The payload is less exotic than the word binary might suggest. Numeric values retain their textual representation. SQLite does not eagerly turn every integer literal into a machine integer during conversion. The design defers conversion until an operation needs it. Different string encodings similarly preserve information about escaping, so conversion work occurs where it is required. This is an encoding designed around the costs of SQLite's JSON operations.

That choice explains why JSONB can improve processing without promising a dramatic reduction in database size. The design document describes a representation usually somewhat smaller than text. Much of the original payload still exists in the same order. Removing delimiters and retaining structural information saves parsing work, but it is not a general compression algorithm. A document full of long strings still has to store those strings.

The JSON implementation in SQLite 3.45.0 also states that interpreting JSONB remains linear in the input size. JSONB does not introduce constant-time field lookup. Length information makes traversal cheaper, but an object is still a sequence of entries. This distinction matters when a performance problem comes from repeatedly searching large documents. Saving a parse is useful, while changing the number of documents examined is a separate query design problem.

Choosing storage and output separately

JSONB is a BLOB stored by SQLite. The design document treats that BLOB as opaque to applications and directs them to access it through the JSON SQL functions. The ordinary JSON functions accept the binary representation as input. Binary-producing functions allow a calculation to retain JSONB when its result will remain inside SQLite. Storage and presentation can therefore use different representations without making the application decode the bytes.

For example, the following SQL expresses the intended separation. The document enters through jsonb(), a scalar field leaves through json_extract(), and the complete document leaves as text through json().

CREATE TABLE settings (
    id INTEGER PRIMARY KEY,
    document BLOB NOT NULL
);

INSERT INTO settings VALUES (
    1,
    jsonb('{"region":"west","enabled":true}')
);

SELECT json_extract(document, '$.region')
FROM settings WHERE id = 1;

SELECT json(document)
FROM settings WHERE id = 1;

These are SQL statements for the SQLite engine included with this release. They deliberately show the representation boundary without adding an HTTP request wrapper. The stored value is binary JSON, while the final query asks SQLite to produce JSON text. Returning that text is a straightforward choice when an application needs to pass the full document to another system that understands ordinary JSON.

The distinction also keeps an application from depending on an internal encoding. SQLite documents JSONB so its developers can maintain compatibility across versions. That compatibility intention does not make the byte layout a recommended application interface. Manipulating the BLOB directly would couple application code to details that the SQL functions already handle. Keeping those operations in SQL preserves SQLite's responsibility for interpreting the stored value.

My preference is to keep document representation decisions inside the database when the database already supplies the necessary functions. That leaves application code concerned with the fields it needs and the text it sends elsewhere. There is little attraction in maintaining a second JSONB reader merely to recover functionality available through a query.

What replication changes about the benefit

The rqlite README at this release describes a fully replicated SQLite database with an HTTP API and request-level consistency choices. JSONB arrives through the embedded SQLite upgrade. It does not introduce a new rqlite document service or a separate cluster-wide indexing mechanism. The 8.18.0 changelog identifies the SQLite upgrade as the source of the new capability.

The release's store implementation submits execution requests through Raft. JSONB's local processing savings and the cost of replication are therefore separate concerns. An expression that converts text to JSONB still has to execute within the replicated database. Retaining the result helps later operations avoid reconstructing the JSON parse representation. It does not imply that writing the original document stops requiring consensus or that the network request becomes smaller merely because SQLite stores a BLOB.

The same distinction applies to reads. A query that examines stored JSONB can avoid part of SQLite's parsing work. Its chosen rqlite consistency level still determines the coordination needed to serve the query. Saving local CPU work may matter most when JSON processing is a substantial part of execution. A request dominated by network coordination has a different cost distribution, even though it uses the same SQL functions.

There is also a useful separation between syntax support and schema migration. Upgrading the embedded engine makes the JSONB functions available. Existing JSON text does not need to become binary just because the engine understands another input representation. SQLite's JSON functions continue to accept text. A schema can retain readable text where that is convenient and use JSONB for values that undergo repeated database-side processing.

This makes the change easier to evaluate on its actual merits. A document stored once and almost always returned whole has different processing needs from a document queried and modified inside SQL. JSONB principally addresses the latter work. Repeated conversion back to text at every intermediate step gives up part of its design benefit, because avoiding those conversions is why the stored format exists.

A shared name does not establish compatibility

SQLite explicitly distinguishes its JSONB from PostgreSQL's JSONB in the release's design document. The name acknowledges PostgreSQL, but the binary formats are incompatible. An application cannot move the opaque bytes between the two engines and expect either to interpret the other's representation. Ordinary JSON text remains the useful interchange form when documents cross that boundary.

This matters for how the feature is described. Calling JSONB a binary representation is accurate. Treating the name as a portable data format would create the wrong expectation. SQLite's goal is to preserve its own internal representation across database operations and future SQLite upgrades. The design is about that engine's processing costs and compatibility obligations.

I regard the narrower scope as a strength. rqlite gains a concrete improvement to JSON processing from the engine it already embeds. The application can keep its external document format while choosing a more useful stored representation. The responsibility for the bytes stays with SQLite, and the responsibility for distributed consistency stays with rqlite's existing request and replication model.