Simon Willison prototypes compressed SQLite text history

Independent developer Simon Willison has designed and tested a new method for storing document revision histories in SQLite databases using Zstandard compression. Conceived during a walk and refined using ChatGPT's voice mode, the experimental Python prototypes were generated by the GPT-5.6 Sol Pro language model. The approach aims to solve the storage bloat associated with saving every historical iteration of frequently edited text documents.

The system compares two distinct storage strategies. The first, called WholeBlobHistoryStore, rewrites a single compressed historical binary large object with every edit. The second, ChunkedHistoryStore, mitigates the performance overhead of decompressing and recompressing the entire history by sealing compressed chunks into multiple rows. Each row in this chunked model is capped at either 128 revisions or 3 megabytes of uncompressed JSON. In a simulation of 1,000 document revisions, 20.4 megabytes of raw text compressed down to just 80.3 kilobytes using a Zstandard-compressed JSON array.

Both prototypes preserve previous text and timestamps, skip unchanged replacements by default, and serialize database writers using the BEGIN IMMEDIATE transaction state to ensure atomic updates. For database administrators and software engineers, this technique offers a lightweight, highly scalable alternative to traditional revision tables. Instead of creating a new database row for every minor edit, which rapidly inflates database size, practitioners can leverage modern compression algorithms to store hundreds of versions in a fraction of the space. This drastically lowers storage costs and memory overhead while maintaining a complete, queryable audit trail directly inside a relational database.