work on autovacuum postgreSQL parameter

 In This blog, we are discussing the auto vacuum parameter on a small scale. we will understand the below parameters and will see how to modify them on the cluster level. 

PARAMETER DETAILS:

AUTOVACUUM_MAX_WORKERS : This parameter will show you how many Autovacuum process is working on cluster level.f there are four databases with autovacuum_max_workers set to 3, 

then, the 4th database has to wait until one of the existing worker process gets free.

AUTOVACUUM_VACUUM_THRESHOLD : The autovacuum_vacuum_threshold parameter controls the minimum number oftuple update or delete operations that must occur on a table before autovacuum vacuums it.

AUTOVACUUM_VACUUM_SCALE_FACTOR: The autovacuum_vacuum_scale_factor configuration parameter controls how aggressive theautovacuum process should be when vacuuming a table.

AUTOVACUUM_ANALYZE_THRESHOLD :  The autovacuum_analyze_scale_factor parameter controls how aggressive the autovacuum process should be when analyzing table.

AUTOVACUUM_VACUUM_COST_LIMIT: The autovacuum_vacuum_cost_limit parameter controls the amount of CPU and I/O resources that an autovacuum worker can consume.

how to work on parameter modification on cluster 

postgres=# show autovacuum_max_workers;

 autovacuum_max_workers

------------------------

 3

(1 row)

postgres=# show autovacuum_vacuum_threshold;

 autovacuum_vacuum_threshold

-----------------------------

 50

(1 row)

postgres=# show autovacuum_vacuum_scale_factor;

 autovacuum_vacuum_scale_factor

--------------------------------

 0.2

(1 row)

postgres=# show autovacuum_analyze_threshold;

 autovacuum_analyze_threshold

------------------------------

 50

(1 row)

postgres=# show autovacuum_vacuum_cost_limit;

 autovacuum_vacuum_cost_limit

------------------------------

 -1

(1 row)

postgres=#

[postgres@PG-V16 ~]$ cat  reset_the_par_values.sql

alter system set autovacuum_max_workers = 6;

alter system set autovacuum_vacuum_threshold = 100;

alter system set autovacuum_vacuum_scale_factor = 0.3;

alter system set autovacuum_analyze_threshold = 100;

alter system set autovacuum_vacuum_cost_limit = 100;

[postgres@PG-V16 ~]$

[postgres@PG-V16 ~]$ psql -p 5433 -d postgres -U postgres -f reset_the_par_values.sql

ALTER SYSTEM

ALTER SYSTEM

ALTER SYSTEM

ALTER SYSTEM

ALTER SYSTEM

[postgres@PG-V16 ~]$ cat validate_vacuum_par_values.sql

show autovacuum_max_workers;

show autovacuum_vacuum_threshold ;

show autovacuum_vacuum_scale_factor ;

show autovacuum_analyze_threshold ;

show autovacuum_vacuum_cost_limit ;

[postgres@PG-V16 ~]$ psql -p 5433 -d postgres -U postgres -f validate_vacuum_par_values.sql

 autovacuum_max_workers

------------------------

 6

(1 row)

 autovacuum_vacuum_threshold

-----------------------------

 100

(1 row)

 autovacuum_vacuum_scale_factor

--------------------------------

 0.3

(1 row)

 autovacuum_analyze_threshold

------------------------------

 100

(1 row)

 autovacuum_vacuum_cost_limit

------------------------------

 100

(1 row)

[postgres@PG-V16 ~]$