SQLite compressed text-history prototypes
Simon Willison developed SQLite compressed text-history prototypes to address the challenge of storing revision histories for text in relational databases. The traditional method of saving each prior version as a new row can lead to significant data accumulation, particularly for long documents. Willison's new approach involves storing all previous versions of a document within a single SQLite BLOB column, compressed using either zlib or zstd. This BLOB contains a JSON array of the historical text versions, complemented by a separate, uncompressed JSON array for timestamps. Two specific prototypes were developed: `WholeBlobHistoryStore`, which rewrites a single compressed historical blob with each edit, and `ChunkedHistoryStore`, designed to improve scaling for extensive histories by sealing compressed chunks. Both prototypes preserve prior text and timestamps, skip unchanged replacements by default, and serialize writers using `BEGIN IMMEDIATE` for atomic updates. Willison discussed the initial concept with the GPT-Live voice mode in the ChatGPT iPhone app and later used GPT-5.6 Sol Pro to build experimental prototypes in Python. A simulation involving 1,000 revisions to a document, which amounted to 20.4 MB of raw text, demonstrated that the Zstandard-compressed JSON array reduced the storage footprint to 80.3 KB. To optimize performance further, Sol suggested breaking the history into multiple rows, each containing a maximum of 128 revisions or 3MB of uncompressed JSON, to avoid decompressing and recompressing the entire array on every edit.
This research offers developers a more efficient method for storing document revision histories in SQLite, significantly reducing storage requirements.