Compressed text-history prototypes tested for revisions in SQLite

Summary

Simon Willison prototyped two SQLite schemas (WholeBlobHistoryStore and ChunkedHistoryStore) that store all previous text versions as a Zstandard-compressed JSON array. He achieved a compression ratio of approximately 254:1 (20.4 MB → 80.3 KB) over 1,000 simulated revisions.

Why it matters

Storing full revision histories in a relational database without a dedicated versioning engine is a common challenge for small and medium-sized applications. Demonstrating that thousands of nearly identical text snapshots can be reduced to tens of kilobytes using zstd in a single BLOB column makes SQLite a viable option for lightweight document versioning, eliminating the need for CouchDB, Git, or a separate history service.

Highlights

• Due to high string repetition between versions, a document with 1,000 revisions was compressed from a raw size of 20.4 MB to just 80.3 KB using Zstandard.
• ChunkedHistoryStore limits each row to 128 revisions or 3 MB of uncompressed JSON to prevent rewriting the entire blob on every edit.
• Both prototypes serialize writers using BEGIN IMMEDIATE and skip unnecessary replacement operations, keeping the write path simple and atomic.

Critical take

The compression ratio highlighted in the title is likely an illusion resulting from consecutive revisions that are very similar to each other. When documents undergo significant rewrites, the repetition that zstd exploits largely disappears, and real-world savings become much more modest. The article remains at the level of a 38-minute AI-assisted prototype; it does not address issues such as read latency for deep history, chunk invalidation, or efficient diffing between two arbitrary versions. Therefore, the work is more of a proof of concept than a production-ready design.