SQLite Compressed Text History Prototype: Efficient Version Storage via zstd-Compressed JSON Array
Score
73提出了一个鲜为人知但巧妙的压缩存储方案，并附有具体压缩比和分块策略，提供了可操作的技术增量。
内容源于真实开发者实践，数据清晰，过程可复现，属于高质量技术博客内容。
对于需要在SQLite中存储文本历史的开发者，给出了可借鉴的实现思路和性能基准。
发布于2026年8月，且使用了新的GPT-5.6模型，具有较强的时效性。
Simon Willison是知名开发者与博客作者，信源可信度高，且内容基于自身实验。
Base 81 × T2 Source weight ×0.9 = 73
Curation line 70Selected
Why selected
The prototype provides concrete compression efficiency metrics and implementation details that can be directly referenced or reproduced, while also demonstrating the potential of AI-assisted rapid idea validation.
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.」
Quick overview
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.
- 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
Showing the source text; the full translation is being generated.
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
Related items
84
FeaturedHow to Evaluate LLMs Before Production: Lessons from GitHub
GitHub 实战分享：如何在生产前评估 LLM
This practical guide stands out because it comes from a real security scenario, not theoretical reasoning. Teams integrating LLMs into production, especially those working on security scanning and data-sensitive engineering, should definitely read it. I recommend designing evaluation sets around your own business data rather than blindly adopting public benchmarks.
84
FeaturedRuff v0.16.0 Released: Default Rules Jump from 59 to 413, CI Pipelines Need Attention
Ruff v0.16.0 发布：默认规则从59条激增至413条，CI工作流需关注
For Python developers, this is an unmissable update signal. The significant increase in Ruff's default rules means we can no longer ignore potential issues in our code, and AI fix tools can lower the upgrade cost. Notably, Astral (OpenAI) is deeply integrating AI coding capabilities with static analysis tools, which may become the norm in future engineering practices.
81
FeaturedOpenRouter Team AI Spend Controls Setup Tutorial
OpenRouter 团队 AI 支出控制设置教程
As AI API spending balloons, OpenRouter's official governance tutorial signals that platforms are taking enterprise cost management seriously. Rather than waiting for bill shock, teams should configure these guardrails early. This is especially valuable for platform engineers and finance owners overseeing AI usage.
81
FeaturedFireside Chat with Anthropic's Claude Code Team: How AI Coding Agents Reshape Software Development
Anthropic Claude Code 团队炉边谈话：AI 编码代理如何重塑软件开发
The value of this fireside chat lies not in repeating Claude Code's features, but in revealing how Anthropic internally redefines engineering processes—shifting from 'execution-heavy' to 'taste-heavy'. For anyone in an AI product team, Cat's point that 'product taste outweighs execution' is worth pondering. Thariq's subversion of the 'never rewrite' rule also reminds us that traditional software engineering principles are being rewritten by AI capabilities.
80
FeaturedGPU Management: Why Idle GPUs Are the New Grounded Aircraft
GPU管理：闲置GPU为何成为新的停飞飞机
This blog is worth reading because it elevates GPU resource management to an economic perspective, using the grounded aircraft analogy to visualize waste. Teams using Kubernetes for GPU clusters can immediately try the recommended tools. However, users in China should note the compatibility of some tools outside AWS/GCP environments.
80
FeaturedAn Opinionated Guide to Which AI to Use to Do Stuff
一份主观指南：如何选择AI来做事情
This blog post is Simon Willison's refined take on Ethan Mollick's guide, ideal for developers and power users struggling to choose between ChatGPT and Claude and their various agent modes. It cuts through the confusion of product naming and unintuitive platform differences, offering clarity that saves hours of trial and error.
80
FeaturedPyPI Implements 14-Day Upload Window to Prevent Supply Chain Poisoning
PyPI 实施14天上传期限以防止供应链投毒
This is a critical message for the Python ecosystem. If you maintain any PyPI project, check your release workflow immediately — the 14-day window means you can no longer patch or overwrite files on old releases; you must publish a new version. For security teams, this is a best practice in supply chain security worth emulating by other package managers.
80
FeaturedLobsters Community Successfully Migrates to SQLite: A Win for Single-Server Architecture
Lobsters 社区成功迁移至 SQLite：单服务器架构的胜利
This migration case is particularly noteworthy because it challenges the conventional wisdom that small sites must adopt distributed databases. Lobsters proves that in 2026, a single server with SQLite can handle an active community. For startup teams struggling with overcomplicated tech stacks, this is an excellent reference for cost reduction and efficiency improvement.