That design doc explicitly talks about what is, essentially, compression of duplicate values in the same column. Many column-oriented databases do this.
With SQLite you’re looking at third party extensions that compress the data, still in row-oriented format, and might rather inefficiently recover some benefit. But WAL probably does help with the write amplification above and beyond this.
journald-style logs really want a column store IMO. It would be highly entertaining to try something like ducklake with SQLite as the catalog — the whole stack is pretty lightweight and there’s support for inlining inserts in the catalog to avoid creating silly numbers of Parquet files.
VictoriaLogs could be a good alternative: it stores logs in column-oriented compressed format, so they occupy small amounts of disk space, and it consists of a single small executable without external dependencies, which stores the logs into a single directory on a local filesystem.
That design doc explicitly talks about what is, essentially, compression of duplicate values in the same column. Many column-oriented databases do this.
With SQLite you’re looking at third party extensions that compress the data, still in row-oriented format, and might rather inefficiently recover some benefit. But WAL probably does help with the write amplification above and beyond this.
journald-style logs really want a column store IMO. It would be highly entertaining to try something like ducklake with SQLite as the catalog — the whole stack is pretty lightweight and there’s support for inlining inserts in the catalog to avoid creating silly numbers of Parquet files.
VictoriaLogs could be a good alternative: it stores logs in column-oriented compressed format, so they occupy small amounts of disk space, and it consists of a single small executable without external dependencies, which stores the logs into a single directory on a local filesystem.
That would make for a fun experiment for a syslog service. I encourage anyone who wants to give it a shot!
Clickhouse seems to be a popular logstore these days. And is column-oriented.
Isn't it at least somewhat heavy weight though?
Yes it's true even with journal_mode=WAL. The same pages are touched (minimum 1 full page plus one per index), only the atomicity layer is different.
Yes, WAL by definition means it writes the data at least twice.