Index Maintenance and Monitoring

PostgreSQL indexes require ongoing maintenance. VACUUM reclaims storage occupied by dead tuples (rows deleted or updated but not yet physically removed), and ANALYZE updates planner statistics so the query optimizer can choose efficient plans. AUTOVACUUM runs these automatically in the background, but heavily updated tables may need manual tuning of autovacuum parameters. REINDEX rebuilds a corrupt or bloated index from scratch. The pg_stat_user_indexes view shows how often each index is used — indexes with zero or very low scans should be candidates for removal as they consume write overhead for no benefit. The pg_stat_statements extension tracks query execution statistics and is invaluable for finding the slowest queries.

Example

-- Check index usage statistics
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan        AS times_used,
  idx_tup_read    AS tuples_read,
  idx_tup_fetch   AS tuples_fetched,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- Find unused indexes (candidates for removal)
SELECT indexname, tablename
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND tablename NOT IN (SELECT tablename FROM pg_tables WHERE tableowner = 'postgres');

-- Bloat check — indexes much larger than needed
SELECT
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- Rebuild an index (locks writes briefly)
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

-- Manual VACUUM and ANALYZE
VACUUM ANALYZE orders;

-- Enable pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned