To recover or reuse disk space occupied by updated or deleted rows.
To update data statistics used by the query planner.
To update visibility map, to speed index-only scans.
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:
Standard vacuum: Works parallel with DB operations.
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.
Write a comment ...