When running vacuum on a table you encounter the message, "WARNING: relation "xxxx" contains more than "max_fsm_pages" pages with useful free space"
Complete Error Message:
VACUUM ANALYZE pg_catalog.pg_attribute; WARNING: relation "pg_catalog.pg_attribute" contains more than "max_fsm_pages" pages with useful free space (seg19 sdw3:1028 pid=316679) HINT: Consider compacting this relation or increasing the configuration parameter "max_fsm_pages".
The actual cause is indicated in the definition of the parameter. As per the Greenplum/postgres guide (here), the max_fsm_pages is defined as,
Sets the maximum number of disk pages for which free space will be tracked in the shared free-space map. Six bytes of shared memory are consumed for each page slot. This setting must be at least 16 * max_fsm_relations. The default is chosen by initdb depending on the amount of available memory, and can range from 20k to 200k pages. This parameter can only be set at server start.
In other words, if your database has a lot of bloats, and during Vacuum (without FULL) it hits the limit of max_fsm_pages capacity to record those free space, it warns you about it. Since these free spaces are now not recorded, Vacuum will not help in marking those dead space as free and the bloat on the object still remains.
To understand more on how the Vacuum and Vacuum Full works, we would recommend to read the article here.
- Increase the parameter "max_fsm_pages" to a higher value than the current settings.
- Run "Vacuum Full" on the object so that unused space is released to the OS and not recorded on the FSM pages.
NOTE: An exclusive lock is issued when you run "vacuum full" which might lead to unpredictable output time as it reshuffles the data from the bottom of the table to the place where the pages are free.
- As "Vacuum Full" yields unexpected time for larger tables, it is better to redistribute the table (non-catalog tables) which also helps in removing fragmentation (bloat) and it's much faster. This option is only valid for user tables.
ALTER TABLE <schema-name>.<table-name> set with (reorganize=false) distributed randomly; ALTER TABLE <schema-name>.<table-name> set with (reorganize=true) distributed by (<distribution-column>)
- To release the bloat of an index, use this command:
Reindex table <schema-name>.<table-name>;