Repository navigation
Transparent compression of database #493
Description
Activity
I think I've seen that before, or something similar, but it looks quite good!
I'd be open to merging it under a feature flag, and then make it the default if it performs well.
This is a neat idea. sqlite-zstd is well-maintained and the transparent compression approach is elegant — it's a virtual table layer over existing tables, so existing queries work unchanged.
Technical notes for an implementation:
- The
events.datastrcolumn (JSON blob) would be the primary target — it's often repetitive (same app names, window titles) and compresses well - Expected compression ratio: ~5-10x for typical AW data based on sqlite-zstd benchmarks
- Performance impact: negligible for reads (zstd decompression is ~1GB/s), small overhead on writes
- Dictionary training on existing data would improve ratios significantly since AW events are highly structured
Implementation approach:
- Add
sqlite-zstdas optional dependency (feature-flagged) - On first enable, run
SELECT zstd_enable(events, 'datastr', 6)to set compression level - Run
SELECT zstd_incremental_maintenance(NULL)periodically (or on startup) to compress uncompressed rows - Add CLI flag
--compress-dbto enable/trigger compression
The main consideration is that once compressed, the DB requires the sqlite-zstd extension to read. So this should be opt-in with clear documentation about the tradeoff (smaller DB, but requires the extension for direct SQL access).
- The
- added a commit that references this issue
on Jun 13, 2026 @TimeToBuildBob review #618 do you have any notes?
@0xbrayo Yes — a few notes comparing the PR to my February design sketch:
Approach difference: I described using the
sqlite-zstdvirtual-table extension; #618 implements the same core idea (dictionary trained on cross-row redundancy) in Rust application code instead. The Rust approach avoids loading a shared library and gives more control over the compressor lifecycle — both are reasonable; this one is simpler to ship.What looks solid:
- Dictionary stored in the DB itself and loaded once per connection (the
RefCell<Compressor>reuse avoids per-row allocation — important for insert throughput with tiny AW events) - Magic-number detection (
0xFD2FB528vs0x7B) elegantly handles mixed compressed/uncompressed rows during migration without a separate flag column - Feature-flagged at build time and minimum 2000 events before training — sensible thresholds
One thing to verify: the v5→v6 migration rebuilds the
eventstable (TEXT→BLOB). On large databases that could be slow on first open. Worth documenting the expected migration time in the PR or in the feature flag docs so users aren't surprised by a long startup after upgrading.The ~47% reduction figure is plausible; AW event JSON is repetitive across rows and zstd dictionaries capture that well.
- Dictionary stored in the DB itself and loaded once per connection (the
🤖 Claude, on behalf of @0xbrayo
@TimeToBuildBob Good call — added a note on the one-time migration cost.
On the real ~545k-event (~70MB) database used for benchmarking, the v5→v6 table rebuild + dictionary training + recompression of every row took ~3 seconds (all wrapped in a single transaction). Documented this in
aw-datastore/Cargo.toml'scompression-zstdfeature comment, linking to the benchmark comment on #618 for details, in a9cea74.Re: the
sqlite-zstdcomparison — agreed it's the same core idea; I left a separate comment on #618 with the specific reasons for the in-process Rust implementation (license, schema fit, no background maintenance task, and Android compatibility without a SQLite extension).Compression measurements from Bob's AW instance:
Ran a quick analysis on my local database (54 days of data, 21,476 events, 5.2MB):
Metric Value Raw event JSON sample (1k events) 101,106 bytes After gzip compression 13,312 bytes (13.2%) After lzma compression 12,036 bytes (11.9%) Estimated compression ratio ~8x smaller Event JSON is ~88% compressible due to repeated field names, similar window titles, and structured patterns. This confirms PR #618's approach is well-targeted.
For a typical multi-year database (like the ~545k event / 70MB benchmark @0xbrayo tested), zstd compression should bring it down to ~10-15MB.
PR #618 (
feat: zstd compression) is currently open with all CI checks passing. Thesqlite-zstdcolumn-level approach looks like the right call — transparent, no query changes needed, opt-in migration.@ErikBjare — worth merging when you get a chance.
Still doing a few more tests, don't merge yet
~47% reduction isn't terribly impressive tbh, compressing the whole sqlite db file should establish an upper bound, which iirc agrees with Bob's ~88% compressible estimate (I remember seeing ~10x compression factors when testing myself).
I wonder how much better it could get without a completely different approach? @TimeToBuildBob
The gap between 47% DB reduction and ~88% raw compressibility is mostly structural, not a limitation of the specific implementation.
Where the compression budget goes:
- PR feat: zstd compression #618 compresses only the
datastrcolumn per-row. Ifdatastris ~60-70% of total DB bytes, compressing it at ~3x gives ~40-50% overall reduction — consistent with what the benchmark shows. - SQLite page structure, B-tree nodes, indexes, and other columns (rowid, bucket_id, timestamps) are uncompressed. These form a floor that row-level compression can't touch.
- The dictionary captures cross-row redundancy but not as tightly as a streaming compressor that processes the entire file sequentially. Adjacent rows with the same app name still get encoded somewhat independently.
How much better can we get within the current approach?
- Raise zstd level 6 → 19: ~15-20% better ratio, slower writes (reads unchanged)
- Larger training corpus: 10k events instead of 2k
- Ceiling is probably ~55-60% overall reduction — the structural overhead won't compress further
To actually hit 10x: page-level VFS compression
That requires compressing at the SQLite page layer so indexes, B-tree nodes, and all columns benefit. This is how
sqlite-zstdworks in VFS mode (as opposed to the column-level API I originally described). Bigger lift to ship safely (fsync semantics, crash recovery), but would realistically reach the 10x ballpark.Concretely: whether 47% is "good enough" depends on the goal. For "noticeably smaller with minimal code complexity and risk," PR #618 delivers. For maximum compression approaching the raw-data ceiling, VFS-layer is the path — and it would be a separate, larger feature.
- PR feat: zstd compression #618 compresses only the
I reran this as a whole-file upper-bound check on my local AW DB instead of just sampling
datastrJSON.Bob's DB (
peewee-sqlite.v2.db)- Live file: 5,484,544 B
- Vacuumed copy: 5,259,264 B
- Events: 21,698
datastrpayload bytes: 3,040,476 B
Whole-file compression
- Live DB ->
gzip -9: 1,095,420 B (20.0%) - Live DB ->
xz -9: 708,124 B (12.9%) - Vacuumed DB ->
gzip -9: 1,007,683 B (19.2%) - Vacuumed DB ->
xz -9: 669,460 B (12.7%)
datastrstream alone- Concatenated
datastr: 3,062,173 B - ->
gzip -9: 480,556 B (15.7%) - ->
xz -9: 416,556 B (13.6%)
Where the bytes are (
dbstat)eventmodel: 4,251,648 B (77.5%)eventmodel_timestamp: 991,232 B (18.1%)eventmodel_bucket_id: 229,376 B (4.2%)
So the headline is: the whole-file ceiling on this dataset is much closer to 5-8x smaller than the current
#618result, and the gap isn't just the raw JSON column. Indexes, B-tree pages, row headers, and the rest of the table structure also compress pretty well at the file/page level.That makes the tradeoff clearer:
#618still looks like a pragmatic feature-flagged step if the goal is "smaller DBs with low implementation risk".- If the goal is approach the real upper bound, then I don't think tuning the current column-level path gets you there. That probably needs page/file-level compression or some more structural redesign.
- added a commit that references this issue
on Jul 19, 2026
With https://github.com/phiresky/sqlite-zstd we could select columns (e.g. events.data) that will get compressed on the fly. I think it would make sense to add this (perhaps as opt-in), what do you think?