Part 140: Large-Scale Index Maintenance & Unused Indexes

Every index accelerates reads but slows down every INSERT, UPDATE, and DELETE.

1. Detecting Unused Indexes in PostgreSQL

SELECT 
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan AS number_of_scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Indexes with 0 scans waste disk space and slow down writes—drop them!

Key Takeaways

• Audit and drop unused indexes to restore database write throughput.

In Part 141, we enter Module 15 to master Database Normalization: 1NF, 2NF, and 3NF!