After compaction, the three biggest tables of a 14 GiB statev2.sqlite shrink to 37-42% with per-row zstd and a trained dictionary

[bompus]

Compaction (#19435) removes superseded history, but my database is still 13.99 GiB after t3 doctor --fix, and three tables hold most of it: orchestration_events (7.71 GB), orchestration_v2_projection_turn_items (3.08 GB) and orchestration_v2_projection_nodes (0.80 GB). Storing each row's payload_json as a zstd blob compressed with a small trained dictionary makes those tables 37-42% of their size in a prototype on copies of my own data. I am asking whether you want this direction before I write any code, because it changes how payloads are stored and queried.

Where this sits next to existing work

• fix(server): compact superseded history in the background #19435 and t3 doctor (feat(server): t3 doctor reclaims database space #19436) delete events a newer event supersedes. On my copy, t3 doctor --fix removed only 2,301 more events. My event count had already dropped from 2.39M on 2026-10-11 to 2.29M, so I believe the server I run compacted this database before the snapshot; I have not checked which build did it. Compression would shrink what compaction keeps; the two do not overlap.
• Keep the active database small by moving archived conversations into compressed storage #15122 proposes moving archived conversations into compressed chunks in a separate archivev2.sqlite. Compressing rows in place would apply to every conversation, archived or not, and needs no archive step. Both could exist, or one could make the other unnecessary; that is your call. A commenter on Keep the active database small by moving archived conversations into compressed storage #15122 measured archived threads at 10.2% of the payload bytes on one 21.5 GB install, so a row-level approach would also reach the other 90%.
• [Feature]: Add supported retention and compaction for unbounded local SQLite growth #6897 (closed by the compaction work) is where several of us posted database sizes. Mine was 14.2 GB then and is 14 GB now, after compaction.

Method

• Source: VACUUM INTO snapshot of my live statev2.sqlite (read-only), then t3 doctor --fix run on the copy at current main. Table counts after that: 2,293,051 events, 479,679 turn items, 364,739 nodes.
• From each of the three tables I took 10 windows of 10,000 consecutive rows spread evenly across it, so 100,000 rows per table.
• Dictionaries were trained on 20 other windows of 300 rows each from the same tables (zstandard Python package, COVER trainer, level 3). The training rows are not among the measured rows, but they come from the same database, so a database with different traffic could do worse.
• Variants: plain text (today); zstd level 3 with no dictionary; zstd level 3 with a 16, 32, 64 or 110 KB dictionary. Node 26 zlib.zstdCompressSync with the dictionary option, one row per blob, nothing else in the row changed.
• Each variant went into a fresh table, was vacuumed, and every row was decompressed and compared with the original (all round trips matched). One run per variant on one machine, so treat differences of a few percent as noise.

File size (percent of the plain table; raw payload_json size in brackets)

Table	plain	zstd-3	+ 16 KB dict	+ 32 KB dict	+ 64 KB dict	+ 110 KB dict
events (217 MB)	313 MB	162 MB (52%)	134 MB (43%)	132 MB (42%)	130 MB (41%)	128 MB (41%)
turn_items (472 MB)	600 MB	273 MB (45%)	230 MB (38%)	226 MB (38%)	222 MB (37%)	220 MB (37%)
nodes (100 MB)	218 MB	138 MB (63%)	93 MB (43%)	92 MB (42%)	92 MB (42%)	92 MB (42%)

A dictionary matters most for small rows: on nodes it takes the table from 63% to 42%. Beyond 16-32 KB the size gain is small and encoding gets much slower.

If those ratios held across the whole tables, the three would drop from about 11.6 GB to about 4.7 GB, saving roughly 6.9 GB of the 14 GB file. That is an extrapolation from 100,000-row samples, not a measurement of the full tables.

Time (32 KB dictionary; per row on the write side, per read on the read side)

Table	encode	decode 500 consecutive rows, plain → compressed	point lookup, plain → compressed
events	44 µs	1.0 ms → 5.1 ms	7 µs → 15 µs
turn_items	61 µs	1.8 ms → 8.0 ms	8 µs → 17 µs
nodes	45 µs	0.9 ms → 5.2 ms	7 µs → 15 µs

I did not measure end-to-end effects on thread open, projection rebuilds or event append under load, and I did not measure WAL or checkpoint behavior. Whether the size win outweighs the added CPU on the paths users feel is the open question.

The catch, and why this needs a decision

• SQL that reads inside these payloads stops working on a blob. A search of apps/server/src finds 156 json_extract calls (migrations included), for example in ProjectionStore.ts, ThreadSearch.ts and EventSink.ts. I did not count how many touch these three tables. Each would need a decompress step, a generated column holding the extracted value, or a rewrite.
• A trained dictionary becomes part of the data format. Rows need to record which dictionary they used, and a retrained dictionary has to leave old rows readable.
• The SQLite file stays a plain SQLite file, but other tools lose the ability to read payloads directly.

What I am not proposing

• Compressing the whole database file or pages (that would be a different tool, such as an SQLite compression VFS, which I did not test).
• Any change to the wire format.

Question

Given compaction and #15122, is smaller storage on these three tables worth the query and format cost to you? If so, which would you prefer to start with: compress only the append-only events table, add generated columns first so the queries survive, or leave storage as is? I can bring a patch for whichever you pick, and I would start with a migration plan and tests for the dictionary and old rows.