Run against any database from version 12 onwards. Read-only — it creates nothing and locks nothing.

Bash
psql -d yourdb -f pg_health_check.sql > report.txt

Reports on:

  • Indexes with fewer than 50 scans, ordered by wasted space
  • Duplicate and redundant index definitions
  • Estimated table and index bloat
  • Buffer cache hit ratio per table
  • Queries running longer than five minutes
  • Tables overdue for autovacuum, with dead tuple counts
  • Replication lag per standby

idx_scan counts are per-node. Check every replica before dropping an index — the one that looks unused on the primary may be serving all your reporting traffic.