PostgreSQL Vacuum - Bacula Catalog Maintenance - Bacula Latin America & Brazil

Periodically executing the vacuum analyze and vacuum full commands in PostgreSQL is essential to maintain the performance and integrity of the database. The importance of each of these commands is explained below.

1. vacuum analyze

Updating Statistics: The vacuum analyze command goes beyond basic vacuum as it also updates the database’s statistics. These statistics are vital for PostgreSQL’s query optimizer to make intelligent decisions on how to execute queries efficiently. Without updated statistics, the optimizer may make suboptimal query plan choices.
Improving Query Performance: By updating statistics, vacuum analyze helps the database make more informed decisions on how to access data, which in turn improves query performance.

2. vacuum full

Removing Obsolete Records: PostgreSQL uses a storage mechanism called Multiversion Concurrency Control (MVCC) to control concurrent transactions. This means that records are not immediately removed when they are deleted or updated but are marked as obsolete. Vacuum is responsible for removing these obsolete records, reclaiming disk space, and improving performance.
Preventing Bloat: Bloat occurs when database tables accumulate obsolete records that have not been cleaned up. Vacuum helps prevent bloat, ensuring that the database does not unnecessarily grow and that queries are executed more efficiently.
Reorganizing Disk Space: Vacuum can also reorganize data on disk, improving the efficiency of access to records and reducing disk fragmentation.

Regarding Bacula, slow queries (for example, selecting job log entries in Bweb) have already been resolved in specific production scenarios by running a vacuum. Since Bacula is constantly recycling backups and pruning catalog information, it is recommended to run vacuum analyze weekly and vacuum full every three months.

Below are examples of Bacula Admin Jobs for automated execution.

3. Example of Admin Jobs for vacuum and vacuum analyze in Bacula

Vacuum analyze should be executed on a weekly schedule as shown in the following example.

Job {
  Name = "admin-vacuum-weekly"
  Type = Admin
  JobDefs = BackupsToDisk
  RunBeforeJob = "vacuumdb -z -q -j 2 -d bacula"
  Schedule = admin-weekly
  ...
}

Schedule {
  Name = "admin-weekly"
  Run = Mon at 14:00
}

Vacuum full should be executed every three months as shown in the following example.

Job {
  Name = "admin-vacuum-analyze-trimestral"
  Type = Admin
  JobDefs = BackupsToDisk
  RunBeforeJob = "vacuumdb -f -q -d bacula"
  Schedule = admin-trimestral
  ...
}

Schedule {
  Name = "admin-trimestral"
  Run = Jan, Apr, Jul, Oct at 14:00
}

As shown in Figures 1 and 2, the aforementioned configurations can be done graphically through Bweb.

Figures 1 and 2. Configuration of vacuum analyze and full Admin Jobs (Bweb).

Even when selecting only the “bacula” database for vacuum execution, the vacuumdb command may print warnings like the following.

2023-09-07 08:37:55 ebacula-dir JobId 40551: BeforeJob: WARNING:  skipping "pg_shdepend" --- only superuser can vacuum it
2023-09-07 08:37:55 ebacula-dir JobId 40551: BeforeJob: WARNING:  skipping "pg_authid" --- only superuser can vacuum it
2023-09-07 08:37:55 ebacula-dir JobId 40551: BeforeJob: WARNING:  skipping "pg_database" --- only superuser can vacuum it
2023-09-07 08:37:55 ebacula-dir JobId 40551: BeforeJob: WARNING:  skipping "pg_db_role_setting" --- only superuser can vacuum it
2023-09-07 08:37:55 ebacula-dir JobId 40551: BeforeJob: WARNING:  skipping "pg_tablespace" --- only superuser can vacuum it
2023-09-07 08:37:55 ebacula-dir JobId 40551: BeforeJob: WARNING:  skipping "pg_auth_members" --- only superuser can vacuum it
2023-09-07 08:37:55 ebacula-dir JobId 40551: BeforeJob: WARNING:  skipping "pg_shdescription" --- only superuser can vacuum it
2023-09-07 08:37:56 ebacula-dir JobId 40551: BeforeJob: WARNING:  skipping "pg_subscription" --- only superuser can vacuum it
2023-09-07 08:37:56 ebacula-dir JobId 40551: BeforeJob: WARNING:  skipping "pg_shseclabel" --- only superuser can vacuum it
2023-09-07 08:37:56 ebacula-dir JobId 40551: BeforeJob: WARNING:  skipping "pg_replication_origin" --- only superuser can vacuum it

These warnings can be ignored.

Starting from PostgreSQL 16, however, there is a new type of permission that can be granted to the Bacula user to mitigate these warnings, as shown in the example psql command below.