As a developer, I want to define a VACUUM policy for SQLite

We have decided to use SQLite as main storage mechanism for Control Center.

SQlite is a efficient, simple, powerful, well tested database engine, but it requires us taking some decisions normally not taken when using other databases like MySQL or Postgres.

One of the most important ones is on management of internal file fragmentation, when records are deleted or updated.

At the moment, we have only one database file (logs.db), where new records are always added. Old ones are never deleted. That means that the file will always grow.

I personally don't see many issues with this fact, as SQLite has a quite compact file format, but the fact that most people use logrotate to compress/remove old log files means that they care about disk usage.

On one of our tests, we found a busy mail server generating over 300MB of compressed (gzip) logs in a single month.

That indicates that we'll need to implement a cleaning mechanism, with unfortunately will mean that the user will lose history. How this affects the application (insights storage, notifications, etc.) has to be discussed.

The VACUUM feature on sqlite rebuilds the database, removing internal fragmentation, but it comes with high cost.

We should analyze whether VACUUM is needed, when and how to be performed.

A very interesting article about VACUUM from the Geary mail client developers has much more information about VACUUM:

https://blogs.gnome.org/jnelson/2015/01/06/sqlite-vacuum-and-auto_vacuum/