This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In one of our Postgres reporting servers, Autovacuum is not running frequently as per the expectations.
Postgres is based on MVCC architecture which keeps different versions of the row.
A Vacuum commands used to remove dead tuples from the disk which improve the overall performance of the PostgreSQL Server.
When we have enabled Autovacuum related parameters, It will perform vacuum automatically whenever a database has no load and tables should not have any exclusive lock.
This is fine with default Autovacuum setting, but My reporting system is very loaded with tons of bulk operations.
In this situation, Autovacuum will not run frequently so we should change the default value of Autovacuum related parameters and we should make It more aggressive.
Below have required changes to force the Autovacuum parameters for running frequently.
First enable the log for Autovacuum process:
|
1 |
log_autovacuum_min_duration = 0 |
Increase the size of worker to check table more:
|
1 2 |
autovacuum_max_workers = 6 autovacuum_naptime = 15s |
Decrease the value of thresholds and auto analyze to trigger the sooner:
|
1 2 3 4 |
autovacuum_vacuum_threshold = 25 autovacuum_vacuum_scale_factor = 0.1 autovacuum_analyze_threshold = 10 autovacuum_analyze_scale_factor = 0.05 |
Make autovacuum less interruptable:
|
1 2 |
autovacuum_vacuum_cost_delay = 10ms autovacuum_vacuum_cost_limit = 1000 |
