PostgreSQL - Autovacuum Tuning and Troubleshooting

 

Introduction

This document explains important PostgreSQL autovacuum parameters, how to monitor autovacuum activity, memory tuning strategies, and steps for performing manual VACUUM FREEZE operations. All examples use generic database, schema, and table names so the guide can be reused in any environment.

1. Important Autovacuum Parameters

SELECT short_desc, name, setting, unit

FROM pg_settings

WHERE name IN (

'autovacuum_max_workers',

'autovacuum_analyze_threshold',

'autovacuum_analyze_scale_factor',

'autovacuum_vacuum_threshold',

'autovacuum_vacuum_scale_factor',

'maintenance_work_mem',

'autovacuum_naptime',

'vacuum_cost_limit',

'autovacuum_freeze_max_age',

'vacuum_freeze_min_age');

2. Check If Autovacuum Is Running (Performance or I/O Issues)

SELECT datname,

       usename,

       pid,

       state,

       current_timestamp - xact_start AS xact_runtime,

       query

FROM pg_stat_activity

WHERE upper(query) LIKE '%VACUUM%'

ORDER BY xact_start;

Terminate Long Running Autovacuum (If Required)

SELECT pg_terminate_backend(<PID>);

3. Why Autovacuum Runs for a Long Time

One common reason is insufficient memory allocation, particularly a very small maintenance_work_mem setting.

4. What Is maintenance_work_mem

Memory allocated for maintenance operations such as VACUUM and CREATE INDEX. It stores row identifiers that are candidates for cleanup during vacuum.

5. maintenance_work_mem Details

• Default value: 64MB

• Higher values improve vacuum and index build performance

• Usually safe to set higher than work_mem

6. autovacuum_work_mem

When autovacuum runs, memory may be allocated up to autovacuum_max_workers times this value. VACUUM can use up to 1GB memory for dead tuple identifiers.

7. Memory Allocation Recommendations

• Large servers: maintenance_work_mem up to 2GB

• Very large workloads: 2GB–4GB

• Tune based on workload

8. Verify Databases Requiring Vacuum (Wraparound Risk)

SELECT datname,

       age(datfrozenxid)

FROM pg_database

ORDER BY age(datfrozenxid) DESC;

Check Current Setting

SHOW autovacuum_freeze_max_age;

9. Performing Manual VACUUM FREEZE

In addition to autovacuum, manual VACUUM FREEZE should be performed periodically on high-churn tables during maintenance windows.

Identify Running Autovacuum

SELECT datname,

       usename,

       pid,

       current_timestamp - xact_start AS xact_runtime,

       query

FROM pg_stat_activity

WHERE upper(query) LIKE '%VACUUM%'

ORDER BY xact_start;

10. Increase Memory for Large Tables (Session Level)

SET maintenance_work_mem = '2GB';

SET maintenance_work_mem = '128MB';

Run Manual Vacuum

\timing on

VACUUM FREEZE VERBOSE schema_name.table_name;

Terminate Autovacuum If Needed

SELECT pg_terminate_backend(<PID>);

Conclusion

Proper tuning of autovacuum memory and monitoring processes is essential for PostgreSQL performance and stability.