See official documentation for more details
Each update or delete query creates dead tuples. And running vacuum recovers disk space occupied by these tuples. However, it creates a substantial amount of I/O traffic, which can cause poor performance for other active sessions.
There are two types of vacuum: autovacuum or vacuum full. In the default configuration, autovacuuming is enabled and the related configuration parameters are appropriately set.
- can run in parallel with production database operations
- does not recover as much space full vacuum
- cannot be done in parallel with other use of the table
- runs much more slowly
- is sometimes able to recover space that autovacuum cannot
Since vacuum full is much more costly, the usual goal of routine vacuuming is to standard vacuum often enough to avoid full vacuum.
Vacuum can be configured to be more or less aggressive
The vacuum threshold is defined as:
vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples
Insert threshold is defined as:
vacuum insert threshold = vacuum base insert threshold + vacuum insert scale factor * number of tuples