PostgreSQL: Force Autovacuum for running Aggressive

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.

What is Multi Version Concurrency Control (MVCC)

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:

Increase the size of worker to check table more:

Decrease the value of thresholds and auto analyze to trigger the sooner:

Make autovacuum less interruptable:

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz