Use auto-vacuum-AnalyticDB(AnalyticDB)-阿里云帮助中心

AnalyticDB for PostgreSQL runs autovacuum as a background process to reclaim dead rows and prevent two problems: query performance degradation from data bloat, and transaction ID (XID) wraparound failures. Without autovacuum, tables that receive frequent updates or deletes accumulate dead tuples that inflate table size and slow down sequential scans.

Prerequisites

Before you begin, ensure that you have:

An AnalyticDB for PostgreSQL instance running a minor version released on or after May 27, 2021

To update the minor version of your instance, see Update the minor version of an instance.

How autovacuum works

The autovacuum process periodically scans tables with heavy insert, update, or delete activity, and checks the XID age of each table. It then runs VACUUM or VACUUM FREEZE as needed to reclaim dead rows or reduce XID age.

Trigger formula

Autovacuum triggers on a table when:

dead rows > table rows × scale factor + row threshold

With AnalyticDB for PostgreSQL's default values (autovacuum_vacuum_scale_factor = 0.5, autovacuum_vacuum_threshold = 10000):

A 100,000-row table triggers autovacuum when dead rows exceed 100,000 × 0.5 + 10,000 = 60,000 (60% dead).
A 20,000-row table triggers when dead rows exceed 20,000 × 0.5 + 10,000 = 20,000—that is, when every row is dead.

The row count used in the formula comes from the table's last analysis, not the current live count.

Lock behavior

Autovacuum holds a ShareUpdateExclusiveLock (level 4) on the table it processes. If a business query needs a conflicting lock, autovacuum releases its lock immediately so that business traffic is not blocked.

Resource usage

Autovacuum consumes CPU and I/O resources during execution. It does not affect query performance unless the instance is already at a resource bottleneck.

Monitor autovacuum

Check running autovacuum workers

In V7.0:

SELECT * FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';

In V6.0:

SELECT * FROM pg_stat_activity WHERE query LIKE 'autovacuum%';

Find tables that need vacuuming

The following query returns tables where dead rows exceed the autovacuum trigger threshold, ordered by bloat ratio:

SELECT
    schemaname,
    c.relname,
    c.reltuples,
    n_dead_tup,
    last_autovacuum,
    (n_dead_tup::float / c.reltuples::float) AS bloat_ratio
FROM pg_stat_all_tables s, pg_class c
WHERE c.oid = s.relid
  AND c.reltuples > 10000
  AND (n_dead_tup::float > 10000 + c.reltuples::float * 0.5)
ORDER BY (n_dead_tup::float / c.reltuples::float) DESC;

The hardcoded values match the instance-level defaults: 0.5 for autovacuum_vacuum_scale_factor and 10000 for autovacuum_vacuum_threshold. Update these values if you have changed the instance-level parameters.

Configure parameters

Instance-level parameters

Parameter	Description	Default	Unit
`autovacuum_naptime`	Minimum interval between two autovacuum scheduling cycles.	60	Seconds
`autovacuum_vacuum_scale_factor`	Dead row ratio threshold for triggering autovacuum. Lower values trigger autovacuum more frequently.	0.5	Ratio (0–1)
`autovacuum_vacuum_threshold`	Minimum number of updated or deleted rows for triggering autovacuum.	10,000	Rows
`autovacuum_freeze_max_age`	Maximum XID age before autovacuum forces a VACUUM FREEZE to prevent transaction ID wraparound.	200,000,000	XIDs
`autovacuum_max_execute_workers`	Maximum number of concurrent autovacuum tasks.	max(3, cpucores/2)	Workers

Important

Modifying instance-level parameters is not necessary in most cases. If a change is required, submit a ticket.

Table-level parameters

Table-level parameters override instance-level settings for individual tables. Two common cases where the instance-level defaults do not suit specific tables:

Small tables with frequent writes may trigger autovacuum hundreds of times per day under default settings, consuming resources that would otherwise maintain larger tables. Raise autovacuum_vacuum_scale_factor for those tables to reduce frequency.
Very large tables (billions of rows) require an enormous number of changes before the default threshold is reached. Lower the scale factor for those tables so autovacuum runs before bloat accumulates.

Modify a table-level parameter

ALTER TABLE <table_name> SET (<autovacuum_option> = <value>);

Example 1: Raise the dead row ratio threshold to 80% for the orders table to reduce autovacuum frequency.

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.8);

Example 2: Disable autovacuum for the staging table.

ALTER TABLE staging SET (autovacuum_enabled = false);

Important

Setting autovacuum_enabled = false disables autovacuum for the table entirely, which can lead to unchecked data bloat. Instead, raise autovacuum_vacuum_scale_factor to a high value to reduce autovacuum frequency without disabling it.

Reset a table-level parameter

To restore a table's parameter to the instance-level default:

ALTER TABLE <table_name> RESET (<autovacuum_option>);

Example: Restore the dead row ratio threshold for the orders table.

ALTER TABLE orders RESET (autovacuum_vacuum_scale_factor);

Configurable table-level parameters

Parameter	Description
`autovacuum_vacuum_scale_factor`	Dead row ratio threshold for triggering autovacuum.
`autovacuum_vacuum_threshold`	Minimum number of updated or deleted rows for triggering autovacuum.