SQLite compressed text-history prototypes
- ID
- 12585
- Status
- summarized
- Published
- 10 Aug 2026, 6:05 AM
- Fetched
- 10 Aug 2026, 6:54 AM
- Provider
- Simon Willison
- Category
- developer-ai
- Original URL
- https://simonwillison.net/2026/Aug/9/sqlite-text-history-prototype/
- Source URL
- https://simonwillison.net/atom/everything/
Summary
- Score
- 6.5
- Created
- 10 Aug 2026, 6:54 AM
- Tags
- Audience
- developersdatabase_learners
What happened
Simon Willison prototyped two SQLite approaches for storing text revision histories: WholeBlobHistoryStore, which rewrites one compressed blob per edit, and ChunkedHistoryStore, which seals compressed chunks to scale better for long histories. Both store prior text versions and timestamps in compressed JSON arrays (zlib or zstd), skip unchanged replacements by default, and use BEGIN IMMEDIATE for atomic writes. The motivation is avoiding the naive approach of storing a full row per edit, which balloons storage for long documents.
Why it matters
If you store edit histories in SQLite for apps like collaborative editors or AI conversation logs, these two patterns give you a concrete tradeoff to evaluate: WholeBlob is simpler but rewrites the entire compressed blob on every edit, while Chunked scales better for long histories at the cost of more complexity. Worth benchmarking both against your actual edit patterns before committing.
Discussion angle
When does WholeBlob's simplicity break down — at what document size or edit frequency does rewriting the full compressed blob per edit become a real performance or storage problem worth switching to ChunkedHistoryStore?