sqlite is not drastically better in this regard: it's designed as a rewritable database, not a log store, and so it's also going to have quite a big write amplification if you do lots of small writes. (Probably the best mitigation is to buffer up the log lines and write them out periodically, but for an idle linux system this might need to be pretty long to make much of a different, and then you would inevitably get complaints about logs being lost during a power failure or kernel panic)
> complaints about logs being lost during a power failure or kernel panic
I recall reading somewhere about a thing which persists data in a particular region of RAM that is guaranteed to be left alone by the kernel, and therefore will persist across a reboot. Buffering in such a region could persist data when the kernel panics but it obviously wouldn't survive loss of power.
Wouldn't opening the file O_APPEND (maybe O_DIRECT also?) and using fdatasync be better? That way we've basically implemented a WAL and skipped all the other database parts we don't need or care about.
Is that true even with PRAGMA journal_mode = WAL?
https://www.sqlite.org/wal.html
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.