SQLite compressed text-history prototypes

Perpetually intrigued by methods of storing revision histories in relational databases, the author conceived a novel approach during a dog walk: compressing the full text of every prior version into a large JSON array of strings using zlib or zstd compression. The concept was discussed with the GPT‑Live voice mode in the ChatGPT iPhone app. The reporter relayed their thoughts as a stream of consciousness, outlining the prototype idea.

To implement the scheme, the reporter built systems in the past where every previous copy of the document added a significant amount of data to the database with each edit. The new idea was to compress all versions of the document, including the initial one, into a single JSON array and store it in a BLOB (binary large object) column. This would minimize redundant text and significantly reduce the database size.

The reporter proposed two columns: one for the JSON array of text and another for a JSON array of timestamps, which could be stored as integers. This approach would allow for efficient storage and retrieval of revision histories.

After further discussion with GPT‑5.6, the reporter developed an experimental prototype using Python. The prototype simulated 1,000 revisions to a document, resulting in 20.4 MB of raw revision text that compressed to a mere 80.3 KB using Zstandard compression. To optimize the system, the reporter suggested breaking the history into multiple rows, with each row containing a maximum of either 128 revisions or 3 MB of uncompressed JSON. This would prevent the overhead of decompressing and recompressing the entire array on every edit.

The reporter concluded by acknowledging the success of the prototype, stating that it worked remarkably well. They also mentioned building the prototype using Python and building experimental prototypes around the idea. The whole process took 38 minutes, delivering the solution and the files seen in the folder.