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.
