Overview

Bloat is wasted space that accumulates in tables due to PostgreSQL's MVCC concurrency model. Any UPDATE or 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.

Recommendation

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.

VACUUMing Bloat

Run VACUUM manually for the affected table or configure the autovacuum process to run more frequently.

Regular 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.

NOTE: Running 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 FULL is needed, downtime for your database may be required.

Running VACUUM manually looks like:

psql> VACUUM <table-name>;

Configuring Autovacuum

Autovacuum can be configured with two settings: autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor.

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 autovacuum_vacuum_scale_factor.

dead_row_threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number_of_table_rows

Example: if 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.

Settings example:

psql> ALTER TABLE <table-name> SET (autovacuum_vacuum_threshold = 25);
psql> ALTER TABLE <table-name> SET (autovacuum_vacuum_scale_factor = 0.1);