---
title: "SQLite Compressed Text History Prototype: Efficient Version Storage via zstd-Compressed JSON Array"
source: "Simon Willison 博客（Atom）"
source_tier: "t2"
url: "https://simonwillison.net/2026/Aug/9/sqlite-text-history-prototype/#atom-everything"
permalink: "https://feed.astralor.com/items/itm_0317e3154096f4d39ef28920"
published: "2026-08-09T22:05:00.000Z"
score: 73
channel: "tech"
tags: ["GPT-5.6 Sol Pro","JSON","SQLite","Zstandard","Compression","Version History"]
---

# SQLite Compressed Text History Prototype: Efficient Version Storage via zstd-Compressed JSON Array

## AI commentary

> What's most valuable here is how AI voice discussion and prototype development are chained together, showing AI can not only answer questions but also actively validate a spur-of-the-moment technical idea. For anyone building local-first or lightweight apps, this compression scheme is worth copying directly; for others, it's a fast demonstration of going from idea to implementation.

## At a glance

Simon Willison had a new idea during a dog walk: bundle all historical versions of a document into a JSON array, then compress the whole thing with zlib or zstd before storing it in SQLite, avoiding the huge redundancy of per-row storage. After discussing the concept with ChatGPT voice mode, he asked GPT-5.6 Sol Pro to build a Python prototype. The experiment showed that 1,000 simulated revisions generated 20.4 MB of raw text, which compressed to just 80.3 KB with Zstandard. To avoid decompressing and recompressing the entire array on every edit, the prototype splits history into multiple rows, each capped at 128 revisions or 3 MB of uncompressed JSON. This offers a remarkably simple yet efficient reference for version tracking in relational databases.

## Key points

- Proposed storing all historical versions in a single JSON array and compressing it as a whole, significantly reducing redundancy.
- GPT-5.6 Sol Pro implemented a Python prototype in 38 minutes.
- Experimental result: 20.4 MB of raw revision text compressed to just 80.3 KB with Zstandard.
- To avoid recompressing the entire array on each edit, history is split into rows capped at 128 revisions or 3 MB of uncompressed JSON.

## Full text

Research: SQLite compressed text-history prototypes I'm perennially interested in options for storing revision histories in relational databases. 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. The new GPT‑Live voice mode in the ChatGPT iPhone app has got really good, so I discussed the prototype with that. You still can't share URLs to voice conversations, but here's what I said copied from the transcript as a proper stream of consciousness: I have an interesting idea for a scheme for saving all previous versions of a piece of text that's constantly edited in a SQLite database um column in as efficient a way as possible. Okay, so I built these kinds of systems in the past, and it's always difficult to come up with a efficient way to do this. 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. So, what I've now thinking, is um compression would work really well, right? If you Bundle all of those different um Every every version of this document all the way back to the start if you were to apply a good compression algorithm to them that should basically wipe out huge amounts of the redund- the um redundant text, right Um, so what I'd thinking is how about really, really simple mechanism 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, and so you probably have two columns, right? You'd have a column that's this magic JSON array of text You have a second column which is a JSON array of timestamps and that doesn't need to be compressed at all, right? A timestamp can just be a uh- it's an array of integers, right? Unix integers But that's the whole scheme. Then I stopped voice mode and typed the following text prompt to GPT-5.6 Sol Pro: Use Python and Build experimental prototypes around this idea It churned away for 38 minutes and delivered this answer plus the files you see in this folder. 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. Tags: compression, sqlite, speech-to-text

---

Verify against the original before citing; curated by Astral Feed: https://feed.astralor.com/items/itm_0317e3154096f4d39ef28920