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.