Transparent compression of database

[powellnorma]

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?

[ErikBjare]

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.

[TimeToBuildBob]

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.datastr column (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:

1. Add sqlite-zstd as optional dependency (feature-flagged)
2. On first enable, run SELECT zstd_enable(events, 'datastr', 6) to set compression level
3. Run SELECT zstd_incremental_maintenance(NULL) periodically (or on startup) to compress uncompressed rows
4. Add CLI flag --compress-db to 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).