Database maintenance with VACUUM and ANALYZE

-- Scheduled maintenance script
DO $$
DECLARE
  table_record RECORD;
BEGIN
  -- Vacuum tables with high dead tuple ratio
  FOR table_record IN
    SELECT schemaname, tablename
    FROM pg_stat_user_tables
    WHERE n_dead_tup > 10000
      AND ROUND(100 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) > 10
  LOOP
    RAISE NOTICE 'Vacuuming %.%', table_record.schemaname, table_record.tablename;
    EXECUTE format('VACUUM ANALYZE %I.%I',
                  table_record.schemaname,
                  table_record.tablename);
  END LOOP;
END $$;

-- Maintenance window for VACUUM FULL
BEGIN;

-- Lock table
LOCK TABLE large_table IN ACCESS EXCLUSIVE MODE;

-- Vacuum full
VACUUM FULL large_table;

-- Reindex
REINDEX TABLE large_table;

-- Analyze
ANALYZE large_table;

COMMIT;

-- Alternative: pg_repack (online table rewrite)
-- pg_repack -t large_table -k
-- Doesn't require exclusive lock

-- Cluster table by index (physically reorders rows)
CLUSTER users USING idx_users_created_at;
-- Future inserts won't maintain order

-- Check if table needs clustering
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Update statistics sampling
ALTER TABLE large_table
  ALTER COLUMN search_vector
  SET STATISTICS 1000;  -- Default is 100

-- Increase statistics for better query plans
ANALYZE large_table (search_vector);

-- Check statistics quality
SELECT
  schemaname,
  tablename,
  attname,
  n_distinct,
  most_common_vals,
  most_common_freqs
FROM pg_stats
WHERE tablename = 'users'
  AND schemaname = 'public';

-- Maintenance monitoring query
WITH maintenance_status AS (
  SELECT
    schemaname,
    tablename,
    n_live_tup,
    n_dead_tup,
    ROUND(100 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_vacuum,
    last_autovacuum,
    NOW() - last_autovacuum AS time_since_autovacuum,
    last_analyze,
    last_autoanalyze,
    NOW() - last_autoanalyze AS time_since_autoanalyze
  FROM pg_stat_user_tables
)
SELECT *
FROM maintenance_status
WHERE dead_pct > 10
   OR time_since_autovacuum > INTERVAL '1 day'
   OR time_since_autoanalyze > INTERVAL '1 day'
ORDER BY dead_pct DESC;

-- Automated maintenance with pg_cron
CREATE EXTENSION pg_cron;

-- Schedule daily VACUUM ANALYZE at 2 AM
SELECT cron.schedule(
  'nightly-vacuum',
  '0 2 * * *',
  $$VACUUM ANALYZE$$
);

-- Schedule weekly REINDEX at 3 AM Sunday
SELECT cron.schedule(
  'weekly-reindex',
  '0 3 * * 0',
  $$REINDEX DATABASE mydb$$
);

-- MySQL maintenance equivalent
/*
-- Optimize table (defragment)
OPTIMIZE TABLE users;

-- Analyze table (update statistics)
ANALYZE TABLE users;

-- Check table integrity
CHECK TABLE users;

-- Repair table
REPAIR TABLE users;
*/

-- Space reclamation summary
SELECT
  pg_size_pretty(SUM(pg_total_relation_size(schemaname||'.'||tablename))) AS total_size,
  pg_size_pretty(SUM(pg_relation_size(schemaname||'.'||tablename))) AS table_size,
  pg_size_pretty(SUM(pg_total_relation_size(schemaname||'.'||tablename) -
                pg_relation_size(schemaname||'.'||tablename))) AS index_size,
  COUNT(*) AS table_count
FROM pg_tables
WHERE schemaname = 'public';