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.

PostgreSQL - Aggressive Vacuuming

 

Aggressive Vacuuming

Overview

This guide explains how to monitor dead tuples in PostgreSQL tables and tune autovacuum settings to improve performance. All schema names, table names, and database references are kept generic for reuse across environments.

1. Identify Tables with High Dead Tuples

SELECT schemaname,

       relname,

       last_vacuum,

       last_analyze,

       vacuum_count,

       analyze_count,

       last_autoanalyze,

       last_autovacuum,

       autovacuum_count,

       autoanalyze_count,

       n_dead_tup

FROM pg_stat_all_tables

WHERE n_dead_tup > 10000

ORDER BY n_dead_tup DESC

LIMIT 50;

Explanation

This query identifies the top tables with significant dead tuples, which may require manual vacuuming or autovacuum tuning.

2. Tune Autovacuum Settings for a Specific Table

ALTER TABLE schema_name.table_name

SET (autovacuum_vacuum_scale_factor = 0.0);

 

ALTER TABLE schema_name.table_name

SET (autovacuum_vacuum_threshold = 10000);

 

ALTER TABLE schema_name.table_name

SET (autovacuum_analyze_scale_factor = 0.0);

 

ALTER TABLE schema_name.table_name

SET (autovacuum_analyze_threshold = 10000);

Explanation

These settings ensure autovacuum triggers based on a fixed threshold instead of percentage growth, which is useful for large or frequently updated tables.

3. Generate Vacuum Commands Dynamically

SELECT 'VACUUM VERBOSE ANALYZE ' || schemaname || '.' || relname || ';'

FROM pg_stat_all_tables

WHERE n_dead_tup > 0

ORDER BY n_dead_tup DESC

LIMIT 50;

Explanation

This query generates VACUUM commands for tables with dead tuples, allowing administrators to execute maintenance efficiently.

4. Example: Applying Autovacuum Settings to Another Table

ALTER TABLE schema_name.another_table

SET (autovacuum_vacuum_scale_factor = 0.0);

 

ALTER TABLE schema_name.another_table

SET (autovacuum_vacuum_threshold = 10000);

 

ALTER TABLE schema_name.another_table

SET (autovacuum_analyze_scale_factor = 0.0);

 

ALTER TABLE schema_name.another_table

SET (autovacuum_analyze_threshold = 10000);

Best Practices

• Monitor pg_stat_all_tables regularly

• Tune autovacuum per table for high-write workloads

• Use VACUUM VERBOSE to observe maintenance behavior

• Avoid aggressive settings on small tables

• Ensure autovacuum workers and memory settings are adequate

Conclusion

Proper monitoring and tuning of autovacuum significantly improves PostgreSQL performance and prevents table bloat. This guide provides a reusable approach for maintaining healthy database tables.

PostgreSQL - Maintenance Plan: Autovacuum Tuning and Configuration Updates

Overview

This document outlines the planned maintenance activities for OLTP and OLAP PostgreSQL environments. The tasks include dropping obsolete tables, updating autovacuum settings at table and database levels, and applying memory and configuration changes where required.

 All database, schema, and table names have been kept generic for reuse across environments.

Maintenance Scope

1. Dropping old or unused tables in both OLTP and OLAP environments.

2. Updating autovacuum settings in OLTP and OLAP databases.

3. Updating memory and other configuration parameters in OLAP environment.

4. Reloading configuration after applying changes.

Autovacuum Parameters Explained

autovacuum_vacuum_scale_factor:

This parameter defines the fraction of table size (in terms of number of tuples) that must be updated or deleted before an autovacuum is triggered. Setting it to 0.0 disables percentage-based triggering, meaning only the threshold value will control when vacuum runs.

 autovacuum_vacuum_threshold:

This specifies the minimum number of dead tuples that must accumulate before autovacuum starts. Lower values cause vacuum to run more frequently, which is useful for high-write tables to prevent bloat.

 autovacuum_analyze_scale_factor:

Similar to vacuum scale factor, but used for ANALYZE operations. It determines how much data change must occur before PostgreSQL updates table statistics used by the query planner.

autovacuum_analyze_threshold:

Minimum number of inserted, updated, or deleted rows required before auto-analyze runs. Lower values ensure statistics stay fresh, improving query plans.

Why Set Scale Factor to 0.0?

For very large tables, percentage-based triggering can delay vacuum significantly. Setting the scale factor to 0.0 ensures autovacuum runs based only on a fixed threshold, providing predictable maintenance behavior.

Autovacuum Configuration — OLTP Environment (Table Level)

ALTER TABLE schema_name.table_name

SET (

    autovacuum_vacuum_scale_factor = 0.0,

    autovacuum_vacuum_threshold = 500000,

    autovacuum_analyze_scale_factor = 0.0,

    autovacuum_analyze_threshold = 250000

);

Example for High-Volume Table

ALTER TABLE schema_name.large_transaction_table

SET (

    autovacuum_vacuum_scale_factor = 0.0,

    autovacuum_vacuum_threshold = 2000000,

    autovacuum_analyze_scale_factor = 0.0,

    autovacuum_analyze_threshold = 1000000

);

Autovacuum Configuration — OLTP Environment (Database Level)

ALTER DATABASE database_name

SET (autovacuum_vacuum_scale_factor = 0.0);

 

ALTER DATABASE database_name

SET (autovacuum_vacuum_threshold = 25000);

 

ALTER DATABASE database_name

SET (autovacuum_analyze_scale_factor = 0.0);

 

ALTER DATABASE database_name

SET (autovacuum_analyze_threshold = 1000);

Autovacuum Configuration — OLAP Environment (Table Level)

ALTER TABLE schema_name.reporting_table

SET (

    autovacuum_vacuum_scale_factor = 0.0,

    autovacuum_vacuum_threshold = 2500000,

    autovacuum_analyze_scale_factor = 0.0,

    autovacuum_analyze_threshold = 1000000

);

Additional OLAP Tables Example

ALTER TABLE schema_name.analytics_table

SET (

    autovacuum_vacuum_scale_factor = 0.0,

    autovacuum_vacuum_threshold = 500000,

    autovacuum_analyze_scale_factor = 0.0,

    autovacuum_analyze_threshold = 750000

);

Autovacuum Configuration — OLAP Environment (Database Level)

ALTER DATABASE database_name

SET (autovacuum_vacuum_scale_factor = 0.0);

 

ALTER DATABASE database_name

SET (autovacuum_vacuum_threshold = 500);

 

ALTER DATABASE database_name

SET (autovacuum_analyze_scale_factor = 0.0);

 

ALTER DATABASE database_name

SET (autovacuum_analyze_threshold = 250);

Reload PostgreSQL Configuration

SELECT pg_reload_conf();

Best Practices

• Perform changes during a maintenance window.

• Monitor autovacuum activity after applying new settings.

• Validate table bloat and dead tuples before and after changes.

• Adjust thresholds based on workload characteristics.

• Ensure sufficient I/O capacity before aggressive vacuum tuning.

Conclusion

Proper autovacuum tuning is essential for maintaining database performance, especially in high-write OLTP systems and large OLAP workloads. Applying table-specific thresholds combined with database-level defaults helps prevent table bloat, improves query performance, and ensures consistent maintenance behavior.