Summary
There have been cases where the database size (specifically pg_largeobject) keeps growing even when VACUUM is done every day. Your database keeps growing until it reaches VACUUM FULL.
However, this does not mean that VACUUM cannot work on pg_largeobject. The command treats all the tables equally in PostgreSQL. The reason why it seems to not work is related to the parameter, "max_fsm_pages", on postgresql.conf.
The Free Space Map (FSM) is where postgreSQL keeps track of pages that have free space available for use. Each time it needs space in a table, it will look into the FSM first. If it cannot find any free space, then it will fall back to adding the information to the end of the table.
FSM has limited size (max_fsm_pages). It is used for VACUUM to find out how many and which pages to look for when it frees up the space. In InterScan Messaging Security Suite (IMSS), the "max_fsm_pages" parameter is set to "20000" by default. This means that every time it VACUUM, it can free 20000*8K = 160MB, at most. If the purged size exceeds 160MB every day, the other dead tuples will not be freed by VACUUM and the DB size will keep growing.
The VACUUM FULL is not limited by "max_fsm_pages" and "max_fsm_relations", but the parameters require an exclusive lock on each table while VACUUM FULL is processing them. Therefore, frequent use of VACUUM FULL can have an extremely negative effect on the performance of the current database.
PostgreSQL suggests that the database is frequently updated by operations, like update, delete, insert, etc., and using VACUUM will be more efficient than VACUUM FULL. Also, by expanding the FSM to cover all the dead tuples, you may never need to run VACUUM FULL again, except on holidays. FSM is kept in the memory by postgreSQL and one FSM occupies 6bytes of memory.
To resolve this issue, adjust the value of the "max_fsm_pages" parameter:
- Download the vacuum_pglargeobject_va.sh tool.
- Extract and execute vacuum_pglargeobj.sh to run VACUUM FULL.
- After VACUUM FULL is complete, open the /var/imss/pgdata/postgresql.conf file.
- Look for the "max_fsm_pages" parameter and set its value to "100000".
- Save and close the file.
- Use the following command to restart all the services, including postgreSQL:
/opt/trend/imss/script/imssctl.sh restart
VACUUM will take effect on all the tables after changing the value of the parameter.
