Bloat is wasted space that accumulates in tables due to PostgreSQL's MVCC concurrency model. Any
DELETE on a table causes dead rows (or tuples) to accumulate over time. Tables that have more modifications will typically have more bloat than less often modified tables.
In addition to wasted disk space, large amounts of bloat can slow down queries or impact your database performance since queries need to look through a large number of dead rows.
To prevent further bloat from accumulating, Postgres provides a built in mechanism to clean up dead rows by running
VACUUM manually or having the autovacuum process run automatically for your tables.
If a table has a high bloat factor, which is the ratio of bloat in the table, or a large amount of wasted space, a recommendation will be created for the table. Take action to control the bloat for the affected table or ignore the recommendation to remove it from your recommendation dashboard.
VACUUMs need to be run periodically to prevent further bloat from accumulating but regular
VACUUMs do not remove the existing bloat from the table.
VACUUM marks dead rows as being available for re-use but does not release the disk space back to the operating system. Alternatively,
VACUUM FULL rewrites the entire table on disk which does free up the dead space.
VACUUM FULL <table-name>can be a dangerous operation since it requires a lock on the table that prevents all reads and writes during the table rewrite. For large tables, this can cause outages for services using the table. If
VACUUM FULLis needed, downtime for your database may be required.
VACUUM manually looks like:
psql> VACUUM <table-name>;
autovacuum_vacuum_threshold sets the minimum number of dead rows that are required to trigger an autovacuum for a table. The default is 50 dead rows.
autovacuum_vacuum_scale_factor sets the minimum fraction of the table that must consist of dead rows to trigger an autovacuum for a table. The default is 20% of the table must be dead rows.
These two settings work together to set the actual dead row threshold for autovacuum to trigger.
autovacuum_vacuum_threshold is added to the number of rows in the table multiplied by the
dead_row_threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number_of_table_rows
autovacuum_vacuum_threshold is set to 50,
autovacuum_vacuum_scale_factor is set to 0.2 and the number of rows in the table is 10,000, then autovacuum won't run until 2,050 dead rows accumulate.
If your table is not being autovacuumed frequently enough, consider reducing the
autovacuum_vacuum_scale_factor to a value lower than 0.2.
psql> ALTER TABLE <table-name> SET (autovacuum_vacuum_threshold = 25); psql> ALTER TABLE <table-name> SET (autovacuum_vacuum_scale_factor = 0.1);