PostgreSQL - Vacuum Processing

Vacuum processing is a maintenance process that facilitates the persistent operation of PostgreSQL. Its two main tasks are removing dead tuples and the freezing transaction ids.

Vacuum parameters:

[enterprisedb@edb-saigon ~]$ less /u01/edb/as16/data/postgresql.conf

...

#------------------------------------------------------------------------------

# AUTOVACUUM

#------------------------------------------------------------------------------

#autovacuum = on                                                                    # Enable autovacuum subprocess?  'on' & requires track_counts to also be on.

#autovacuum_max_workers = 3                                        # max number of autovacuum subprocesses & (change requires restart)

#autovacuum_naptime = 1min                                           # time between autovacuum runs

#autovacuum_vacuum_threshold = 50                               # min number of row updates before vacuum

#autovacuum_vacuum_insert_threshold = 1000                # min number of row inserts before vacuum; -1 disables insert vacuums

#autovacuum_analyze_threshold = 50                                # min number of row updates before analyze

#autovacuum_vacuum_scale_factor = 0.2                          # fraction of table size before vacuum

#autovacuum_vacuum_insert_scale_factor = 0.2               # fraction of inserts over table size before insert vacuum

#autovacuum_analyze_scale_factor = 0.1                           # fraction of table size before analyze

#autovacuum_freeze_max_age = 200000000                        # maximum XID age before forced vacuum & (change requires restart)

#autovacuum_multixact_freeze_max_age = 400000000    # maximum multixact age before forced vacuum & (change requires restart)

#autovacuum_vacuum_cost_delay = 2ms                              # default vacuum cost delay for autovacuum, in milliseconds; & -1 means use vacuum_cost_delay

#autovacuum_vacuum_cost_limit = -1                                   # default vacuum cost limit for autovacuum, -1 means use vacuum_cost_limit

...

employees=> select name from pg_settings where category = 'Autovacuum';

employees=> show autovacuum ;

 autovacuum 

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

 on

(1 row)

Some important concepts related each others:

• MVCC
• VACUUM
• HOT

Ref:

• PostgreSQL VACUUM Guide and Best Practices | EDB (enterprisedb.com)
• Deep dive into PostgreSQL VACUUM garbage collector | Google Cloud Blog
• PostgreSQL Vacuuming Command to Optimize Database Performance (percona.com)
• Monitoring PostgreSQL VACUUM Processes | Datadog (datadoghq.com)
• VACUUM Processing :: Hironobu SUZUKI @ InterDB