SQLite as a repository: compression erases the history tax

Text versioning in relational databases traditionally has two painful ends. Either you store each diff separately and version reconstruction requires application logic, or you store a full copy of the document on every save and the database quickly bloats. Simon Willison explored a third way: storing the entire history of a document as a single compressed binary column in SQLite.

The brutal efficiency of compression on redundant data

The principle is straightforward. The history is a single JSON array where a full new copy of the text is appended on each save. To keep it from eating the disk, the array is run through the Zlib or Zstandard (ZSTD) algorithm before saving. Because the previous and new versions share the vast majority of characters, the compression is extremely effective. In an experiment with 1,000 document revisions, 20.4 MB of raw text shrank to 80.3 KB. The database keeps timestamps in an uncompressed column next door for fast querying.

Hitting the limit of one large blob

The "everything in one blob" architecture has a ceiling, however. With every new save, the database must decompress the entire history, append the new record, and compress everything again. This is computationally expensive for long histories. Willison's experiment therefore introduced a second prototype that chunks the history. Once a blob reaches 128 revisions or 3 MB of uncompressed data, it is sealed and new text starts pouring into the next row.

Conflicts in a distributed environment

The simplicity of the solution hides one catch for production deployment. Conflict resolution. The prototype currently relies on a coarse BEGIN IMMEDIATE lock to serialize writes. For a local SQLite instance, this is an acceptable solution, but in distributed systems it would be a major bottleneck.

The path to offline-first applications without git

The success of this pattern paves the way for offline-first applications that need local history without a Git dependency. It allows keeping the complete history directly with the data without the risk of rapid depletion of storage space on end devices.

Lilith's verdict

Handing a compression algorithm a pile of nearly identical text is an old trick, but wrapping it directly in a database column is a beautifully lazy architecture that could bury complex versioning libraries.