-- Configure aggressive autovacuum for frequently updated tables
ALTER TABLE user_sessions SET (
    autovacuum_vacuum_threshold = 100,
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_analyze_threshold = 50,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_cost_delay = 10
);

3. Schedule Manual VACUUM During Low Activity

-- Create a maintenance script for off-peak hours
DO $$
DECLARE
    table_record RECORD;
BEGIN
    FOR table_record IN 
        SELECT schemaname, tablename 
        FROM pg_stat_user_tables 
        WHERE n_dead_tup > 1000
    LOOP
        EXECUTE format('VACUUM ANALYZE %I.%I', 
                      table_record.schemaname, 
                      table_record.tablename);
        RAISE NOTICE 'Vacuumed table: %.%', 
                     table_record.schemaname, 
                     table_record.tablename;
    END LOOP;
END $$;

4. Handle Large Tables Strategically

For very large tables, consider partitioning or incremental approaches.

-- VACUUM specific partitions
VACUUM ANALYZE sales_data_2024_01;
VACUUM ANALYZE sales_data_2024_02;

-- Use VACUUM with cost-based delay for large tables
VACUUM (VERBOSE, BUFFER_USAGE_LIMIT '256MB') large_transaction_table;

Advanced VACUUM Techniques

Monitoring VACUUM Progress

-- Monitor running VACUUM operations
SELECT 
    pid,
    datname,
    query,
    state,
    query_start,
    state_change
FROM pg_stat_activity 
WHERE query LIKE '%VACUUM%' 
AND state = 'active';

Custom VACUUM Maintenance Function

-- Create a comprehensive maintenance function
CREATE OR REPLACE FUNCTION perform_maintenance_vacuum()
RETURNS void AS $$
DECLARE
    table_rec RECORD;
    bloat_threshold CONSTANT numeric := 20.0;
BEGIN
    -- Log maintenance start
    RAISE NOTICE 'Starting maintenance VACUUM at %', now();

    -- VACUUM tables with high bloat
    FOR table_rec IN
        SELECT 
            schemaname,
            tablename,
            n_dead_tup,
            n_live_tup + n_dead_tup as total_tup
        FROM pg_stat_user_tables
        WHERE n_dead_tup > 0 
        AND (n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100) > bloat_threshold
        ORDER BY n_dead_tup DESC
    LOOP
        EXECUTE format('VACUUM ANALYZE %I.%I', 
                      table_rec.schemaname, 
                      table_rec.tablename);

        RAISE NOTICE 'Processed table %.% with % dead tuples', 
                     table_rec.schemaname, 
                     table_rec.tablename,
                     table_rec.n_dead_tup;
    END LOOP;

    RAISE NOTICE 'Maintenance VACUUM completed at %', now();
END;
$$ LANGUAGE plpgsql;

-- Execute maintenance function
SELECT perform_maintenance_vacuum();

Performance Tuning for VACUUM

Adjust VACUUM Cost Parameters

-- Optimize VACUUM performance settings
SET vacuum_cost_delay = 0;  -- Disable delay for faster VACUUM
SET vacuum_cost_page_hit = 1;
SET vacuum_cost_page_miss = 10;
SET vacuum_cost_page_dirty = 20;
SET vacuum_cost_limit = 2000;  -- Higher limit for faster processing

-- Increase maintenance_work_mem for VACUUM operations
SET maintenance_work_mem = '1GB';

-- For VACUUM FULL operations
SET temp_tablespaces = 'fast_ssd_tablespace';

-- Find tables that haven't been vacuumed recently
SELECT 
    schemaname,
    tablename,
    last_vacuum,
    last_autovacuum,
    n_dead_tup,
    CASE 
        WHEN last_vacuum IS NULL AND last_autovacuum IS NULL THEN 'Never vacuumed'
        WHEN GREATEST(last_vacuum, last_autovacuum) < now() - interval '7 days' THEN 'Overdue'
        ELSE 'Recent'
    END as vacuum_status
FROM pg_stat_user_tables
WHERE n_dead_tup > 100
ORDER BY GREATEST(last_vacuum, last_autovacuum) ASC NULLS FIRST;

-- Check for long-running transactions blocking autovacuum
SELECT 
    pid,
    usename,
    application_name,
    state,
    query_start,
    xact_start,
    now() - xact_start as transaction_age
FROM pg_stat_activity 
WHERE state IN ('idle in transaction', 'active')
AND now() - xact_start > interval '1 hour'
ORDER BY xact_start;

-- Create a view for VACUUM monitoring
CREATE VIEW vacuum_monitoring AS
SELECT 
    schemaname,
    tablename,
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) as bloat_percent,
    last_vacuum,
    last_autovacuum,
    GREATEST(last_vacuum, last_autovacuum) as last_vacuum_any,
    vacuum_count,
    autovacuum_count
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 0;

-- Query the monitoring view
SELECT * FROM vacuum_monitoring 
WHERE bloat_percent > 15 
ORDER BY bloat_percent DESC;