Skip to content
Search

PostgreSQL:Vacuum

lastmod 2021-05-15created 2020-04-1716 edits

A PostgreSQL database requires a periodic maintenance task called vacuum. (In English-speaking countries, the word vacuuming is already an everyday term meaning 'running the vacuum cleaner', and it was adopted as a database term. This text simply uses vacuum as is. - Translator) In most server environments this task is handled automatically by the autovacuum daemon, so there is no particular need to worry about it. This is covered in detail in section 23.1.6. You can adjust how the daemon behaves by changing the autovacuum-related configuration values. Also, some database administrators judge that running the VACUUM command manually makes for more efficient use of the database, and perform this periodic cleanup with programs such as cron or a task scheduler. To do this well, you need a good understanding of what is described here. If no particular problems arise in a typical environment using the autovacuum feature, and especially if you are an administrator with many other busy tasks, you can skim this part.

About

Vacuum optimizes storage space whose efficiency has dropped due to the MVCC architecture. Vacuum has four main roles.

  • Reclaiming storage space — cleaning up dead tuples per table
  • Preventing transaction wraparound — cleanup so that the Transaction ID values used by MVCC do not keep increasing
  • Improving Query Planner accuracy — updating statistics
  • Improving index scan performance — updating visibility map information

Therefore, Vacuum must be run periodically to keep using the database. #Autovacuum handles this role.

AutoVacuum

A PostgreSQL database requires a periodic maintenance task called vacuum. It is handled automatically by the autovacuum daemon.

The daemon runs vacuum periodically using the autovacuum parameter values configured in the DB.

How to check autovacuum process 

$ ps -eaf | egrep "/post|autovacuum"

Logging autovacuum

Modify the log_autovacuum_min_duration setting. You can check the value as follows. 

$ cat /var/lib/postgresql/data/postgresql.conf  | grep log_autovacuum_min_duration

Active Vacuum

Either modify the config file or use the query below. 

ALTER SYSTEM SET autovacuum = on;

Threshold check SQL 

SELECT c.relname, u.n_dead_tup,( c.reltuples * s2.setting::float + s1.setting::int) as threshold
FROM pg_class as c, pg_settings as s1, pg_settings as s2, pg_stat_user_tables as u
WHERE c.relname = 'plugin_data' 
AND u.relname = 'plugin_data' 
AND s1.name = 'autovacuum_vacuum_threshold' 
AND s2.name = 'autovacuum_vacuum_scale_factor';

Troubleshooting

Autovacuum free space

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.

See also

Favorite site

Guide

author Zer0 sitemap