Optimizing Database Size and Performance

When optimizing for database size and performance, database vacuuming can be utilized in addition to retention settings.

Retention Days Setting

The audit_retention_days configuration parameter in the properties.yml file specifies the number of days in which to retain data in the audit log database and exec_history log database. Records older than the specified number of days will be deleted.

For example, if the retention days is set to 30 by default, data created on the first day will be deleted on the 31st day.

Managing Unused Space and Reuse

Assume the database size reaches 90GB in 30 days, with a daily addition of 3GB of data.

On the 31st day, 3GB of data is removed. The database size will not shrink; instead, its
associated pages are added to the free list, marking them as available for future use.
Therefore, if new records are added at the same rate as deletions, the database size will
remain constant, never exceeding 90GB.

Vacuuming

If you change the retention days to a smaller number but maintain the same addition and
deletion rate, and you want the database size to be smaller or are experiencing
performance issues due to database size, you can vacuum the database.

To vacuum a database, run the following SQLite command in your container:

sqlite3 /path/to/database.db VACUUM