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:
log_autovacuum_min_duration = 0
Increase the size of worker to check table more:
autovacuum_max_workers = 6
autovacuum_naptime = 15s
Decrease the value of thresholds and auto analyze to trigger the sooner:
autovacuum_vacuum_threshold = 25
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_threshold = 10
autovacuum_analyze_scale_factor = 0.05
Make autovacuum less interruptable:
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.