Auto‑Generated PostgreSQL Index Reports (Full Implementation)

These reports answer the questions:

• Which indexes are largest?
• Which indexes are growing fast?
• Which indexes are unused?
• Which tables are over‑indexed?
• Which indexes need REINDEX or DROP?

---

Report Types We Will Generate

✅ Daily Index Health Report
✅ Top Largest Indexes Report
✅ Fast‑Growing Indexes Report
✅ Unused Indexes Report
✅ GIN Index Risk Report

All generated automatically via cron.

---

Directory Structure

Shell

/opt/pg_monitoring/

├── reports/

│ ├── index_health_report.sql

│ ├── largest_indexes.sql

│ ├── growing_indexes.sql

│ ├── unused_indexes.sql

│ └── gin_indexes.sql

├── output/

│ └── index_report_$(date).txt

└── scripts/

└── generate_index_report.sh

Show more lines

---

1️⃣ Index Health Summary Report

📄 reports/index_health_report.sql

SQL

SELECT

now() AS report_time,

COUNT(*) AS total_indexes,

SUM(CASE WHEN idx_scan = 0 THEN 1 ELSE 0 END) AS unused_indexes,

pg_size_pretty(SUM(pg_relation_size(indexrelid))) AS total_index_size

FROM pg_stat_user_indexes;

Show more lines

✅ High‑level overview
✅ First thing DBAs should read

---

2️⃣ Top 10 Largest Indexes Report

📄 reports/largest_indexes.sql

SQL

SELECT

schemaname,

indexrelname AS index_name,

relname AS table_name,

pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,

idx_scan

FROM pg_stat_user_indexes

ORDER BY pg_relation_size(indexrelid) DESC

LIMIT 10;

Show more lines

🚨 Large + low idx_scan = bloat risk

---

3️⃣ Fast‑Growing Indexes Report

📄 reports/growing_indexes.sql

SQL

SELECT

index_name,

pg_size_pretty(MAX(index_size_bytes) - MIN(index_size_bytes)) AS growth_7_days

FROM monitoring.index_size_history

WHERE captured_at >= now() - INTERVAL '7 days'

GROUP BY index_name

HAVING MAX(index_size_bytes) - MIN(index_size_bytes) > 100000000

ORDER BY growth_7_days DESC;

Show more lines

🚨 Flags indexes growing >100 MB/week

---

4️⃣ Unused Indexes Report

📄 reports/unused_indexes.sql

SQL

SELECT

schemaname,

indexrelname AS index_name,

relname AS table_name,

idx_scan,

pg_size_pretty(pg_relation_size(indexrelid)) AS index_size

FROM pg_stat_user_indexes

WHERE idx_scan = 0

ORDER BY pg_relation_size(indexrelid) DESC;

Show more lines

✅ Drop candidates
✅ High write overhead
✅ Common cause of hidden bloat

---

5️⃣ GIN Index Risk Report

📄 reports/gin_indexes.sql

SQL

SELECT

indexrelname AS gin_index,

relname AS table_name,

pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,

idx_scan

FROM pg_stat_user_indexes

WHERE indexrelname ILIKE '%gin%'

ORDER BY pg_relation_size(indexrelid) DESC;

Show more lines

⚠ GIN indexes:

• Grow fast
• Reindex often
• Need special tuning

---

6️⃣ Report Generator Shell Script

📄 scripts/generate_index_report.sh

Shell

#!/bin/bash

 

DB="mydb"

BASE="/opt/pg_monitoring"

OUT="$BASE/output/index_report_$(date +%F).txt"

PSQL="/usr/bin/psql"

 

echo "POSTGRESQL INDEX REPORT - $(date)" > $OUT

echo "=================================" >> $OUT

echo "" >> $OUT

 

for report in index_health_report largest_indexes growing_indexes unused_indexes gin_indexes

do

echo "---- $report ----" >> $OUT

$PSQL -d $DB -f $BASE/reports/$report.sql >> $OUT 2>&1

echo "" >> $OUT

done

Show more lines

Make it executable:

Shell

chmod +x /opt/pg_monitoring/scripts/generate_index_report.sh

Show more lines

---

7️⃣ Cron Job (Automated Report Generation)

Shell

crontab -e

Show more lines

Run Every Day at 3:00 AM

Shell

0 3 * * * /opt/pg_monitoring/scripts/generate_index_report.sh

Show more lines

✅ Zero downtime
✅ Safe
✅ Fully automated

---

8️⃣ Sample Generated Report Output

📄 index_report_2026‑04‑18.txt

Plain Text

POSTGRESQL INDEX REPORT - Sat Apr 18 03:00:01 IST 2026

=================================

 

---- index_health_report ----

total_indexes | unused_indexes | total_index_size

---------------+----------------+------------------

128 | 19 | 14 GB

 

---- largest_indexes ----

index_name | table_name | index_size | idx_scan

-----------------------+------------+------------+---------

idx_users_profile | users | 3.2 GB | 412

idx_orders_created_at | orders | 2.1 GB | 128297

idx_logs_payload_gin | logs | 1.8 GB | 12

 

---- growing_indexes ----

index_name | growth_7_days

-----------------------+---------------

idx_logs_payload_gin | 420 MB

 

---- unused_indexes ----

index_name | table_name | index_size

-----------------------+------------+-----------

idx_old_feature_flag | users | 640 MB

 

---- gin_indexes ----

gin_index | table_name | index_size | idx_scan

-----------------------+------------+------------+---------

idx_logs_payload_gin | logs | 1.8 GB | 12

Show more lines

✅ This is what DBAs love
✅ Clear actions: REINDEX or DROP

---

9️⃣ How to Use These Reports

Action Guide

Finding	Action
Large + unused index	DROP
Fast‑growing index	REINDEX
Large GIN index	Tune + REINDEX
Index > table size	Investigate
Repeated growth	Adjust fillfactor

---

10️⃣ Best Practices for Index Reports

✅ Generate daily
✅ Review weekly
✅ Store 30–90 days
✅ Never auto‑drop indexes
✅ Use reports for decision support

---

Summary

• Reports are auto‑generated
• No manual queries
• Clear actionable metrics
• Cron‑based
• Production‑safe
• Scales with database size

You now have a full auto‑generated PostgreSQL index reporting system ✅