Release History
Requirements

Introduction

Quick onboard

Deployment

Data Modeling

Connecting

Write data

Migration

Query

Operations and Maintenance

Performance Tuning

Troubleshooting

Reference Guide

SQL Reference

FAQ

Automatic Vacuuming Configuration Parameters

This document describes the configuration parameters in the Automatic Vacuuming category.

Caution!
To ensure system stability and security, manually modifying these parameters should be done with extreme care.

autovacuum

Enables or disables the autovacuum launcher background process.

  • This parameter is disabled by default. However, for autovacuum to function properly, track_counts must also be enabled. If track_counts is set to off, autovacuum will not take effect independently.
  • Autovacuum can be disabled for individual tables by altering table storage parameters.
  • Note: Even if this parameter is disabled, the system will still launch autovacuum processes when necessary to prevent transaction ID wraparound.
Data Type Default Value Setting Scope
boolean off master; system; reload

autovacuum_analyze_scale_factor

Specifies a fraction of the table size to be added to autovacuum_analyze_threshold when determining whether to trigger an ANALYZE.

  • The default is 0.1 (10% of table size). This value can be overridden for individual tables by modifying table storage parameters.
Data Type Default Value Range Setting Scope
floating point 0.1 0.0 ~ 100.0 segments; session; reload

autovacuum_analyze_threshold

Sets the minimum number of inserted, updated, or deleted tuples in a table that triggers an ANALYZE.

  • This threshold can be overridden for individual tables by modifying table storage parameters.
Data Type Default Value Range Setting Scope
int 50 0 ~ INT_MAX segments; system; reload

autovacuum_freeze_max_age

Determines the maximum age (in transactions) that a table’s relfrozenxid can reach before a VACUUM is forced to prevent transaction ID wraparound.

  • Note: Even if autovacuum is disabled, the system will launch autovacuum processes to prevent wraparound.
  • Vacuuming also allows removal of old files from the pg_xact subdirectory, which is why the default is set relatively low at 200 million transactions.
  • This parameter can only be set at server startup. However, it can be reduced for individual tables via table storage parameters.
Data Type Default Value Range Setting Scope
int 200000000 100000 ~ 200000000 segments; system; restart

autovacuum_max_workers

Sets the maximum number of autovacuum worker processes that can run simultaneously (excluding the autovacuum launcher process).

  • This parameter can only be set at server startup.
Data Type Default Value Range Setting Scope
int 3 1 ~ 262143 segments; system; restart

autovacuum_multixact_freeze_max_age

Determines the maximum age (in multitransactions) that a table’s relminmxid can reach before a VACUUM is forced to prevent multitransaction ID wraparound.

  • Note: Even if autovacuum is disabled, the system will launch autovacuum processes to prevent multixact wraparound.
  • Vacuuming multixacts also allows removal of old files from the pg_multixact/members and pg_multixact/offsets subdirectories, which is why the default is set relatively low at 400 million multitransactions.
  • This parameter can only be set at server startup. However, it can be reduced for individual tables via table storage parameters.
Data Type Default Value Range Setting Scope
int 400000000 10000 ~ 2000000000 segments; system; restart

autovacuum_naptime

When the autovacuum parameter is enabled, this sets the minimum delay (in seconds) between autovacuum runs on any given database.

  • During each autovacuum cycle, the background process checks databases and issues VACUUM or ANALYZE commands for tables as needed, based on system catalogs and auxiliary statistics.
Data Type Default Value Range Setting Scope
int 60 1 ~ (INT_MAX/1000) master; system; restart

autovacuum_vacuum_cost_delay

Sets the cost delay value (in milliseconds) used in automatic VACUUM operations.

  • If vacuum_cost_delay is specified, its value is used.
  • If no unit is provided, the value is assumed to be in milliseconds. The default is 2.
  • This setting can be overridden for individual tables by modifying table storage parameters.
Data Type Default Value Range Setting Scope
floating point 2 -1 ~ 100 segments; system; reload

autovacuum_vacuum_cost_limit

Sets the cost limit used in automatic VACUUM operations.

  • If -1 is specified (the default), the value of vacuum_cost_limit is used.
  • Note: This value is proportionally distributed among currently running autovacuum worker processes (if multiple workers are active), so the sum of individual worker limits does not exceed this value.
  • This setting can be overridden for individual tables by modifying table storage parameters.
Data Type Default Value Range Setting Scope
int -1 -1 ~ 10000 segments; system; reload

autovacuum_vacuum_scale_factor

Specifies a fraction of the table size to be added to autovacuum_vacuum_threshold when determining whether to trigger a VACUUM.

  • The default is 0.2 (20% of table size).
  • This setting can be overridden for individual tables by modifying table storage parameters.
Data Type Default Value Range Setting Scope
floating point 0.2 0.0 ~ 1.0 segments; system; reload

autovacuum_vacuum_threshold

Sets the minimum number of updated or deleted tuples in a table that triggers a VACUUM.

  • This threshold can be overridden for individual tables by modifying table storage parameters.
Data Type Default Value Range Setting Scope
int 50 1 ~ INT_MAX segments; system; reload

log_autovacuum_min_duration

Logs each autovacuum action if the operation runs for at least the specified duration (in milliseconds).

  • Setting this parameter to 0 logs all autovacuum actions.
  • -1 (the default) disables logging of autovacuum actions.
  • For example, setting it to 1000 logs all autovacuum and analyze operations that run for 1 second or longer.
  • Additionally, when this parameter is set to any value other than -1, a message is logged if an autovacuum action is skipped due to lock contention or a concurrently dropped relation.
  • Enabling this parameter is useful for monitoring autovacuum activity.
  • This setting can be overridden for individual tables by modifying table storage parameters.
Data Type Default Value Range Setting Scope
int -1 -1 ~ INT_MAX segments; system; reload


track_counts

Enables collection of statistics about database activity.

Data Type Default Value Setting Scope
boolean true master; session; reload; superuser
← Previous
Runtime statistics
Next →
Database table