Vacuuming

Why vacuum tables regularly?

1. To recover or reuse disk space occupied by updated or deleted rows.

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

3. To update visibility map, to speed index-only scans.

4. To protect data loss due to transaction id wraparound or multixact ID wraparound.

Postgres vacuum increases I/O traffic, which can cause poor performance but it can be adjusted with some parameters.

Steps to Vacuum:
Understand vacuum, Design for vacuum, monitor the vacuum and then tune the vacuum.

ps -ef | grep vacuum

Two types of vacuum:

1. Standard vacuum:
   Works parallel with DB operations.

2. Full vacuum:
   Requires exclusive lock on DB.

Reindexing

B-trees completely empty is reclaimed for reuse.

Log File Maintenance

postgresql.conf has a log file path parameter, we can rotate the log file after a certain size using the inbuilt facility logging_collector or some external log rotation utility. We can also redirect pg logs to syslog and let it handle itself.