---
title: "SQLite压缩文本历史原型：用zstd压缩JSON数组实现高效版本存储"
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","压缩","版本历史"]
---

# SQLite压缩文本历史原型：用zstd压缩JSON数组实现高效版本存储

## AI 点评

> 这则内容最有价值的地方在于把AI语音讨论和原型开发串在了一起，让我们看到AI不只是回复问题，还能直接动手验证一个临时冒出的技术想法。对正在做本地优先或轻量级应用的人来说，这个压缩方案值得直接抄作业；对其他人而言，它也是一次'想法到实现'的快速示范。

## 速览

Simon Willison在一次遛狗时萌生新想法：将文档所有历史版本打包成JSON数组，再整体用zlib或zstd压缩存入SQLite，从而避免逐行存储带来的巨大冗余。他与ChatGPT语音模式讨论后，又让GPT-5.6 Sol Pro用Python构建原型。实验显示：1000次模拟修订产生20.4MB原始文本，经Zstandard压缩后仅80.3KB。为避免每次编辑都要解压重压整个数组，原型将历史拆分为多行，每行最多128个修订或3MB未压缩JSON。这一方案为关系数据库中的版本追踪提供了极简而高效的参考。

## 关键点

- 提出将全部历史版本存入单个JSON数组并整体压缩的存储方案，显著降低冗余。
- GPT-5.6 Sol Pro 用 Python 开发原型，38分钟完成实现。
- 实验数据：20.4MB原始修订文本经Zstandard压缩后仅80.3KB。
- 为避免每次编辑重压全量数据，历史被拆分为多行，每行限128个修订或3MB未压缩JSON。

## 全文

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

---

引用前请核对原文;本文由星际流动整理:https://feed.astralor.com/items/itm_0317e3154096f4d39ef28920