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.