PGDay UK2024

Needless to say that the vacuum is the most important process for a database system. It prevents problems like table and index bloating and emergency freezing if we have a wraparound problem. Furthermore, it keeps the visibility map up to date. On the other hand, because of incorrectly adjusted aggressive settings of autovacuum it can consume a lot of computing resources that lead to all queries to the system running longer.

An administrator of a database needs to set the settings of autovacuum to have a balance between the vacuum's useful action in the database system and the overhead of its workload. However, it is not enough for him to decide on vacuum functionality through statistical information about the number of vacuum passes through tables and operational data from progress_vacuum, because it is available only during vacuum operation and does not provide a strategic overview over the considered period.

An automation vacuum has a strategic behavior because the frequency of its functionality and resource consumption depends on the workload of the database: its workload on the database is minimal for an append-only table and it is a maximum for the table with a high-frequency updating. Besides, there is a high dependence of the vacuum load on the number and volume of indexes. Because of the absence of the visibility map for indexes, the vacuum scans the index completely, and the worst situation when it needs to do it during a bloating index situation in a small table.

We would like to explain the mechanism of gathering information about vacuum resource consumption for processing indexes and tables and storing it in the Cumulative Statistics System on per-relation basis (for every table and every index in the database). In addition, we will show how such counters will will provide us a clear view about vacuum workload on individual objects of the database and help us make a decision about its correct configuration.

Link to the project: https://commitfest.postgresql.org/48/5012/