Setting up autovacuum in PostgreSQL
Autovacuum is needed for:
Removing irrelevant linesmaking room for new data.
Statistics Updates for query optimizer.
Preventing transaction overflows (the so-called wraparound).
A poorly configured autovacuum can lead to performance degradation or even database crashes if transactions overflow. Therefore, tuning autovacuum is a matter of balancing performance and maintaining data integrity.
Basic parameters of autovacuum settings
Let's look at all the main parameters that affect the operation of the auto vacuum, and how they can be optimized for a specific load.
autovacuum_max_workers
This parameter specifies the maximum number of autovacuum processes that can run simultaneously. By default, PostgreSQL has a value of 3. This means that three tables can be vacuumed at a time, no more.
Increasing this parameter can be useful in systems with a large number of actively updated tables, where you need to quickly clean up data. But more workers means more load on system resources, especially on the disk subsystem.
Example of setup:
ALTER SYSTEM SET autovacuum_max_workers = 10;When to increase? If you have a large database with many actively changing tables, increasing the number of workers will speed up the vacuuming.
autovacuum_naptime
autovacuum_naptime sets the interval between autovacuum cycles, i.e. how often PostgreSQL will check whether any tables need to be vacuumed. By default, this interval is 60 seconds.
The smaller the interval, the more often the system will check tables and run autovacuum, which is useful for databases with frequent changes. However, too small a value may result in the process taking up too many resources, including CPU time and I/O operations.
Example:
ALTER SYSTEM SET autovacuum_naptime="30s";For less loaded systems, you can set a longer interval:
ALTER SYSTEM SET autovacuum_naptime="5min";autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay
These settings control how intensively the autovacuum works and how it will interact with the rest of the database processes.
autovacuum_vacuum_cost_limit — This is the limit on the number of vacuuming operations the process can perform before it pauses. The higher the value, the more work the autovacuum can perform in one cycle. However, too high values can overload the system.
Example:
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;autovacuum_vacuum_cost_delay — this is the time (in milliseconds) for which autovacuum pauses after reaching the limit of operations. The longer the delay, the less autovacuum will load the system, but the longer it will take to clean the database.
Example:
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 20;autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor
These parameters determine at what percentage of changes in the table the autovacuum and statistics analysis should be launched:
autovacuum_vacuum_scale_factor sets the percentage of table row changes after which vacuuming is triggered. The lower the value, the more often vacuuming will be triggered.
Example:
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05;autovacuum_analyze_scale_factor works similarly, but to run statistics analysis. The more often the data is analyzed, the more accurately the query optimizer will work.
Example:
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.1;These parameters can be adjusted for different types of tables. For example, for tables that are frequently updated, you can set more aggressive values so that autovacuum runs more often.
vacuum_freeze_min_age and vacuum_freeze_table_age
These settings control the freezing of old transactions, which is important to prevent transaction ID overflow.
vacuum_freeze_min_age defines the age of transactions (in number of completed transactions) after which rows are marked as “frozen” during vacuuming.
Example:
ALTER SYSTEM SET vacuum_freeze_min_age = 50000000;vacuum_freeze_table_age defines the age of the table (in number of completed transactions) after which it will be forced to vacuum to freeze rows.
Example:
ALTER SYSTEM SET vacuum_freeze_table_age = 150000000;These settings can be configured to vacuum up old transactions early, before transaction ID overflow issues begin.
autovacuum_freeze_max_age
This parameter specifies the maximum transaction age after which the table will be forced to be vacuumed to freeze rows, regardless of other conditions.
Example:
ALTER SYSTEM SET autovacuum_freeze_max_age = 200000000;It is important to remember this parameter, as transaction overflows can cause data integrity issues.
Setting up an autovacuum for different types of loads
Let's talk about how to set up autovacuum for different types of systems – OLTP and OLAP.
OLTP
OLTP systems typically have a high level of transactional load – these are systems where data is constantly being updated and deleted.
Recommended settings:
ALTER SYSTEM SET autovacuum_max_workers = 4;
ALTER SYSTEM SET autovacuum_naptime="10min";
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 4000;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 10;
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.2;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.3;OLAP
OLAP systems are designed to perform queries to large volumes of data, but data changes are not that frequent. In such systems, the emphasis is on performance when reading, and vacuuming can be made more aggressive, since the main load on the disk system occurs when performing analytical queries.
Recommended settings:
ALTER SYSTEM SET autovacuum_max_workers = 4;
ALTER SYSTEM SET autovacuum_naptime="10min";
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 4000;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 10;
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.2;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.3;Conclusion
Autovacuum is an important tool for keeping your database in working order.
Finally, I would like to recommend a free webinar about the specifics of implementing queries in PostgreSQL – CTE, JOIN, execution plans, optimization, recursion. Registration is available at the link.
