-- Basic VACUUM (reclaims dead tuple space)
VACUUM users;
-- VACUUM all tables in database
VACUUM;
-- VACUUM VERBOSE (shows progress)
VACUUM VERBOSE users;
-- VACUUM ANALYZE (vacuum + update statistics)
VACUUM ANALYZE users;
-- VACUUM FULL (rewrites table, requires exclusive lock)
VACUUM FULL users;
-- WARNING: Locks table, can take hours on large tables
-- VACUUM specific columns
VACUUM ANALYZE users (email, username);
-- ANALYZE only (update statistics without vacuum)
ANALYZE users;
-- ANALYZE specific columns
ANALYZE users (email);
-- Check dead tuples
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,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
-- Estimate table bloat
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
ROUND(100 * pg_relation_size(schemaname||'.'||tablename) /
NULLIF(pg_total_relation_size(schemaname||'.'||tablename), 0), 2) AS table_pct
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
-- Autovacuum configuration (postgresql.conf)
-- autovacuum = on
-- autovacuum_max_workers = 3
-- autovacuum_naptime = 1min
-- autovacuum_vacuum_threshold = 50
-- autovacuum_vacuum_scale_factor = 0.2
-- autovacuum_analyze_threshold = 50
-- autovacuum_analyze_scale_factor = 0.1
-- Per-table autovacuum settings
ALTER TABLE large_table SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_analyze_scale_factor = 0.02,
autovacuum_vacuum_threshold = 1000
);
-- Disable autovacuum for specific table (not recommended)
ALTER TABLE staging_table SET (
autovacuum_enabled = false
);
-- Transaction ID wraparound prevention
SELECT
datname,
age(datfrozenxid) AS xid_age,
2147483647 - age(datfrozenxid) AS xids_until_wraparound,
ROUND(100.0 * age(datfrozenxid) / 2147483647, 2) AS pct_toward_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC;
-- VACUUM FREEZE (freeze old tuples)
VACUUM FREEZE users;
-- Check when table needs freezing
SELECT
schemaname,
tablename,
age(relfrozenxid) AS xid_age,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
JOIN pg_class ON pg_tables.tablename = pg_class.relname
WHERE schemaname = 'public'
ORDER BY age(relfrozenxid) DESC;
-- Monitor autovacuum activity
SELECT
pid,
now() - query_start AS duration,
query
FROM pg_stat_activity
WHERE query LIKE '%autovacuum%'
AND query NOT LIKE '%pg_stat_activity%';
-- REINDEX (rebuild indexes)
REINDEX TABLE users;
REINDEX INDEX idx_users_email;
REINDEX DATABASE mydb; -- Requires exclusive lock
-- REINDEX CONCURRENTLY (PostgreSQL 12+, doesn't lock)
REINDEX INDEX CONCURRENTLY idx_users_email;
-- Check index bloat
SELECT
schemaname,
tablename,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;
-- 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';
