Figure out log rotation

[simonw]

Log rotation is a pretty important aspect of sensible logging systems.

How would this work with SQLite databases? The system would have to be smart enough to roll over to logging against a new .db file at the turn of midnight (or more/less frequently depending on configuration).

[wsargent]

I've done the archiving by copying to a new DB in blacklite:

https://github.com/tersesystems/blacklite/#rolling-archiver

[simonw]

I've done the archiving by copying to a new DB in blacklite:

https://github.com/tersesystems/blacklite/#rolling-archiver

Wow, "Logback/Log4J2 appender using SQLite with zstandard dictionary compression and rollover" - zstandard compression too! That's really neat.

[simonw]

https://tersesystems.com/blog/2020/11/26/queryable-logging-with-blacklite/ is fascinating:

Here, only 5000 entries are kept in the live database. When another entry is inserted, the oldest entries are run through dictionary compression and moved to the archive database. After 500000 entries accumulate, the archive.db file is renamed to archive.<timestamp>.db until there are 20 database files, at which point the oldest archive is deleted.