Skip to end of metadata
Go to start of metadata

PostgreSQL is an extremely powerful open source database that I use for several home made applications and third party applications like Atlassian Confluence and JIRA. I have used PostgreSQL for many years and it is my preferred RDBMS because of the performance, stability and capabilities.

PostgreSQL autovacuum daemon

PostgreSQL has the autovacuum daemon that will do some database housekeeping for the following reasons:

  1. Recover or reuse disk space occupied by updated or deleted rows.

  2. To update data statistics used by the PostgreSQL query planner.

  3. To update the visibility map, which speeds up index-only scans.

  4. To protect against loss of very old data due to transaction ID wraparound or multixact ID wraparound.

Many of the latest versions of PosgreSQL has the autovacuum enabled by default but you can check if the vacuum daemon is running with the following command:

ps aux | grep autovacuum | grep -v grep

PostgreSQL full vacuum with crontab

It might be overkill as my PostgreSQL databases are not that big but still I like to do a full vacuum sometimes.

It is easy to incorporate a crontab that can do a full database vacuum on a specific time.

# List cron jobs for postgres user
postgres@ubuntuserver:~$ crontab -l
# Create new crontab
postgres@ubuntuserver:~$ crontab -e
# Create crontab that runs a full analyze on all databases every second day at 01.00 and pipe the result to a log file
0 1 * * 1,3,5,7 "/usr/bin/vacuumdb --all --full --analyze" > /apps/logs/cron/postgresql.log 2>&1

This is just the basics but it still powerful enough to use.