SQLite compressed text-history prototypes

Storing revision histories traditionally means a row per version, which can quickly balloon in size for large documents. The new idea packs every version into a single JSON array and then compresses the whole payload with a fast algorithm such as zlib or zstd before persisting it in a BLOB column. This leverages SQLite's existing storage engine while relying on the redundancy across versions to achieve high compression ratios.

Two prototype storage models are compared. The first, called WholeBlobHistoryStore, replaces the entire compressed blob each time a new edit arrives, ensuring a simple schema but requiring full decompression and recompression on every write. The second, ChunkedHistoryStore, caps each row at either a fixed number of revisions or a maximum uncompressed size, sealing chunks so later edits only affect the most recent chunk, thereby reducing the amount of data that must be re-encoded on each update.

Experimental runs with a thousand simulated edits showed that raw text of about twenty megabytes compressed down to roughly eighty kilobytes using Zstandard, demonstrating the potential for massive space savings. The chunked approach further mitigates the cost of write operations by limiting the amount of data that needs to be reprocessed, which is especially valuable as histories grow into the hundreds of thousands of revisions.

Adopting this method adds runtime overhead: every edit still requires decompressing the target chunk, appending the new version, and recompressing that chunk before committing. Random access to a single historic version now entails loading and decompressing the containing chunk, which may be slower than a direct row lookup. Additionally, SQLite imposes limits on row size, so extremely large uncompressed JSON arrays must be split early to avoid hitting those limits.

Engineers who need built-in versioning can integrate the prototype by adding a BLOB column for the compressed history and a parallel column for timestamps, then wrapping updates in a BEGIN IMMEDIATE transaction to preserve atomicity. The approach is best suited for workloads where reads are typically for the latest version or for bulk retrieval of a range of revisions, and less appropriate when frequent, low-latency access to arbitrary historic versions is required.