SQLite compressed text-history prototypes

“While out on a dog walk I had a new idea: how about taking the full text of every prior version in a big JSON array of strings and then applying zlib or zstd compression to the whole thing? Surely that would compress really well due to all of the repeated strings.”

“Like the easiest way is you have a row for every previous copy of the previous previous value of the string. But if it's a long document Like20 kilobytes of data, that means that every single edit adds another 20 kilobytes of data to the database, right.”

“There is a history column on the single on this uh uh table and it's a blob, it's a BLOB so it stores binary data and then you just stick in there a Zlib or maybe even ZSTD um compressed JSON text array of all of the previous documents”

“The approach works really well! 1,000 simulated revisions to a document resulted in 20.4 MB of raw revision text that compressed to 80.3 KB as Zstandard-compressed JSON array.”

“To avoid the overhead of decompressing and recompressing the entire array on every edit Sol suggested breaking the history up into multiple rows, with each one containing a maximum of either 128 revisions or 3MB of uncompressed JSON.”