VACUUM Regularly

posted Aug 25, 2014, 12:17 PM by Sachchida Ojha   [ updated Aug 25, 2014, 12:21 PM ]
VACUUM reclaims physical space on disk from deleted or updated rows or aborted load/insert operations. For concurrency control, a DELETE or UPDATE operation performs a logical delete of the row from the database. These rows still occupy physical space on the disk but are not visible. 

Run VACUUM to reclaim space after failed loads or large UPDATE and DELETE operations. Also, VACUUM append-only tables periodically, the VACUUM will run almost instantaneously. 

Logically deleted rows (also referred to as expired rows) are tracked in the free space map. The free space map must be adequately sized to accommodate these rows. If the free space map is not large enough, space occupied by the rows that overflow the free space map cannot be reclaimed by a regular VACUUM command. VACUUM FULL is needed to reclaim space by rows that overflowed the free space map. 

A VACUUM FULL is an expensive operation and may take an exceptionally long time to finish therefore it is preferable to perform a CTAS operation then rename 
and drop the original table. 

The max_fsm_pages GUC 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. The default is 200,000. The max_fsm_relations GUC sets the maximum number of relations for which free space will be tracked in the shared memory free-space map. This should be set to a value larger than the total number of tables + indexes + system tables. It costs about 60 bytes of 
memory for each relation per segment instance. The default is 1000.
Comments