-- ============================================================================
-- pg_health_check.sql — read-only PostgreSQL health report
-- Version 3.0 · MIT licence · https://everettkildare.com/downloads
--
-- Usage:  psql -d yourdb -f pg_health_check.sql > report.txt
--
-- Requires PostgreSQL 12 or later. Creates nothing, alters nothing, and takes
-- no locks beyond the catalogue snapshots the statistics views already hold.
-- Safe to run against a primary during business hours.
--
-- Every section is scoped to the CURRENT DATABASE. Statistics counters are
-- per-node and reset by pg_stat_reset() and by a crash — check
-- "Statistics age" at the bottom before drawing conclusions from low counts.
-- ============================================================================

\pset pager off
\timing off
\set ON_ERROR_STOP off

\echo ''
\echo '==========================================================='
\echo ' PostgreSQL health check'
\echo '==========================================================='

SELECT current_database()                       AS database,
       version()                                AS version,
       pg_size_pretty(pg_database_size(current_database())) AS size,
       (SELECT count(*) FROM pg_stat_activity)  AS connections,
       current_setting('max_connections')       AS max_connections;

-- ---------------------------------------------------------------------------
-- 1. Unused and barely-used indexes
--
-- idx_scan is per-node. An index that looks unused on the primary may be
-- serving every reporting query on a replica — check each standby before you
-- drop anything. Primary key and unique-constraint indexes are excluded
-- because dropping them is a schema change, not a tuning decision.
-- ---------------------------------------------------------------------------
\echo ''
\echo '--- 1. Unused indexes (fewer than 50 scans, largest first) ---'

SELECT s.schemaname                                  AS schema,
       s.relname                                     AS table,
       s.indexrelname                                AS index,
       s.idx_scan                                    AS scans,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
  FROM pg_stat_user_indexes s
  JOIN pg_index i ON i.indexrelid = s.indexrelid
 WHERE s.idx_scan < 50
   AND NOT i.indisunique
   AND NOT i.indisprimary
   AND pg_relation_size(s.indexrelid) > 1024 * 1024
 ORDER BY pg_relation_size(s.indexrelid) DESC
 LIMIT 40;

-- ---------------------------------------------------------------------------
-- 2. Duplicate and redundant indexes
--
-- Exact duplicates first (identical column list and opclass), then indexes
-- whose leading columns are a prefix of another index on the same table —
-- the prefix index is usually redundant, but confirm no query depends on its
-- smaller size before dropping.
-- ---------------------------------------------------------------------------
\echo ''
\echo '--- 2a. Exact duplicate indexes ---'

SELECT indrelid::regclass          AS table,
       array_agg(indexrelid::regclass ORDER BY indexrelid) AS duplicates,
       pg_size_pretty(sum(pg_relation_size(indexrelid))) AS total_size
  FROM pg_index
 WHERE indrelid IN (SELECT relid FROM pg_stat_user_tables)
 GROUP BY indrelid, indkey, indclass, indpred, indexprs
HAVING count(*) > 1
 ORDER BY sum(pg_relation_size(indexrelid)) DESC;

\echo ''
\echo '--- 2b. Redundant prefix indexes (leading columns covered by another) ---'

WITH idx AS (
    SELECT i.indexrelid,
           i.indrelid,
           i.indexrelid::regclass AS index_name,
           i.indrelid::regclass   AS table_name,
           -- indkey is an int2vector, whose array subscripts start at 0.
           -- Casting it straight to int2[] keeps that 0 base and silently
           -- shifts every slice below by one, so round-trip it through text
           -- to get an ordinary 1-based array.
           string_to_array(i.indkey::text, ' ')::int2[] AS cols,
           i.indpred IS NOT NULL  AS partial
      FROM pg_index i
     WHERE i.indrelid IN (SELECT relid FROM pg_stat_user_tables)
       AND i.indexprs IS NULL
)
SELECT a.table_name              AS table,
       a.index_name              AS redundant_index,
       b.index_name              AS covered_by,
       pg_size_pretty(pg_relation_size(a.indexrelid)) AS wasted
  FROM idx a
  JOIN idx b
    ON a.indrelid = b.indrelid
   AND a.indexrelid <> b.indexrelid
   AND NOT a.partial
   AND NOT b.partial
   AND array_length(a.cols, 1) < array_length(b.cols, 1)
   AND a.cols = b.cols[1:array_length(a.cols, 1)]
 ORDER BY pg_relation_size(a.indexrelid) DESC
 LIMIT 40;

-- ---------------------------------------------------------------------------
-- 3. Estimated table bloat
--
-- This is an ESTIMATE derived from column statistics, not a measurement.
-- It assumes ANALYZE is reasonably current. Treat anything under 20% as
-- noise, and confirm with pgstattuple before acting on a large table.
-- ---------------------------------------------------------------------------
\echo ''
\echo '--- 3. Estimated table bloat (>20%, largest first) ---'

WITH constants AS (
    SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 8 AS ma
), no_stats AS (
    SELECT table_schema, table_name
      FROM information_schema.columns
     WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
     GROUP BY table_schema, table_name
), null_headers AS (
    SELECT hdr + 1 + (sum(CASE WHEN null_frac <> 0 THEN 1 ELSE 0 END) / 8) AS nullhdr,
           sum((1 - null_frac) * avg_width)                                AS datawidth,
           max(null_frac)                                                  AS maxfracsum,
           schemaname,
           tablename,
           hdr, ma, bs
      FROM pg_stats
     CROSS JOIN constants
     WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
     GROUP BY schemaname, tablename, hdr, ma, bs
), data AS (
    SELECT ma, bs, schemaname, tablename,
           (datawidth + (hdr + ma - CASE WHEN hdr % ma = 0 THEN ma ELSE hdr % ma END))::numeric AS datahdr,
           (maxfracsum * (nullhdr + ma - CASE WHEN nullhdr % ma = 0 THEN ma ELSE nullhdr % ma END)) AS nullhdr2
      FROM null_headers
), estimates AS (
    SELECT schemaname, tablename, bs,
           -- ::numeric matters: ceil() over a float8 division yields double
           -- precision, and there is no round(double precision, integer).
           ceil((cc.reltuples * ((datahdr + ma
                - (CASE WHEN datahdr % ma = 0 THEN ma ELSE datahdr % ma END))
                + nullhdr2 + 4)) / (bs - 20::float))::numeric AS expected_pages,
           cc.relpages::numeric AS actual_pages
      FROM data
      JOIN pg_class cc
        ON cc.relname = data.tablename
      JOIN pg_namespace nn
        ON cc.relnamespace = nn.oid
       AND nn.nspname = data.schemaname
     WHERE cc.relkind = 'r'
)
SELECT schemaname                                     AS schema,
       tablename                                      AS table,
       pg_size_pretty((bs * actual_pages)::bigint)    AS size,
       round(100 * (actual_pages - expected_pages) / nullif(actual_pages, 0)::numeric, 1) AS bloat_pct,
       pg_size_pretty((bs * (actual_pages - expected_pages))::bigint) AS wasted
  FROM estimates
 WHERE actual_pages > 128
   AND expected_pages > 0
   AND actual_pages > expected_pages
   AND round(100 * (actual_pages - expected_pages) / nullif(actual_pages, 0)::numeric, 1) > 20
 ORDER BY (actual_pages - expected_pages) DESC
 LIMIT 30;

-- ---------------------------------------------------------------------------
-- 4. Buffer cache hit ratio per table
--
-- A low ratio on a small table is harmless; it is the large, frequently read
-- tables at the top of this list that justify a shared_buffers conversation.
-- ---------------------------------------------------------------------------
\echo ''
\echo '--- 4. Cache hit ratio (tables read more than 1000 times) ---'

SELECT schemaname                                AS schema,
       relname                                   AS table,
       heap_blks_read                            AS disk_reads,
       heap_blks_hit                             AS cache_hits,
       round(100.0 * heap_blks_hit
             / nullif(heap_blks_hit + heap_blks_read, 0), 2) AS hit_pct,
       pg_size_pretty(pg_total_relation_size(relid))         AS total_size
  FROM pg_statio_user_tables
 WHERE heap_blks_hit + heap_blks_read > 1000
 ORDER BY hit_pct ASC NULLS LAST, heap_blks_read DESC
 LIMIT 30;

\echo ''
\echo '--- 4b. Database-wide cache hit ratio ---'

SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2) AS hit_pct,
       sum(xact_commit)                            AS commits,
       sum(xact_rollback)                          AS rollbacks,
       sum(deadlocks)                              AS deadlocks,
       sum(temp_files)                             AS temp_files,
       pg_size_pretty(sum(temp_bytes))             AS temp_bytes
  FROM pg_stat_database
 WHERE datname = current_database();

-- ---------------------------------------------------------------------------
-- 5. Long-running queries and blocking
-- ---------------------------------------------------------------------------
\echo ''
\echo '--- 5a. Queries running longer than 5 minutes ---'

SELECT pid,
       usename                             AS user,
       application_name                    AS application,
       state,
       now() - query_start                 AS running_for,
       wait_event_type,
       wait_event,
       left(regexp_replace(query, '\s+', ' ', 'g'), 120) AS query
  FROM pg_stat_activity
 WHERE state <> 'idle'
   AND pid <> pg_backend_pid()
   AND query_start < now() - interval '5 minutes'
 ORDER BY query_start;

\echo ''
\echo '--- 5b. Idle in transaction for more than 5 minutes ---'
\echo '     (these hold back vacuum and the transaction ID horizon)'

SELECT pid,
       usename                        AS user,
       application_name               AS application,
       now() - state_change           AS idle_for,
       left(regexp_replace(query, '\s+', ' ', 'g'), 120) AS last_query
  FROM pg_stat_activity
 WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
   AND state_change < now() - interval '5 minutes'
 ORDER BY state_change;

\echo ''
\echo '--- 5c. Blocked sessions and their blockers ---'

SELECT blocked.pid                    AS blocked_pid,
       blocked.usename                AS blocked_user,
       blocker.pid                    AS blocking_pid,
       blocker.usename                AS blocking_user,
       now() - blocked.query_start    AS blocked_for,
       left(regexp_replace(blocked.query, '\s+', ' ', 'g'), 80) AS blocked_query
  FROM pg_stat_activity blocked
  JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS blocking(pid) ON true
  JOIN pg_stat_activity blocker ON blocker.pid = blocking.pid
 WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

-- ---------------------------------------------------------------------------
-- 6. Autovacuum backlog
--
-- dead_ratio is dead tuples as a share of live tuples. The default autovacuum
-- threshold fires at 20%; anything materially above that on a busy table means
-- autovacuum is not keeping up, is being cancelled, or is being blocked by a
-- long transaction (see 5b).
-- ---------------------------------------------------------------------------
\echo ''
\echo '--- 6a. Tables overdue for autovacuum ---'

SELECT schemaname                     AS schema,
       relname                        AS table,
       n_live_tup                     AS live_tuples,
       n_dead_tup                     AS dead_tuples,
       round(100.0 * n_dead_tup / nullif(n_live_tup, 0), 1) AS dead_pct,
       last_autovacuum,
       last_autoanalyze,
       pg_size_pretty(pg_total_relation_size(relid)) AS size
  FROM pg_stat_user_tables
 WHERE n_dead_tup > 1000
   AND n_dead_tup > n_live_tup * 0.2
 ORDER BY n_dead_tup DESC
 LIMIT 30;

\echo ''
\echo '--- 6b. Transaction ID wraparound headroom ---'
\echo '     (age approaching autovacuum_freeze_max_age needs attention)'

SELECT c.relnamespace::regnamespace   AS schema,
       c.relname                      AS table,
       age(c.relfrozenxid)            AS xid_age,
       round(100.0 * age(c.relfrozenxid)
             / current_setting('autovacuum_freeze_max_age')::numeric, 1) AS pct_of_max,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
  FROM pg_class c
 WHERE c.relkind IN ('r', 'm')
   AND c.relnamespace::regnamespace::text NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
 ORDER BY age(c.relfrozenxid) DESC
 LIMIT 20;

-- ---------------------------------------------------------------------------
-- 7. Replication
--
-- Empty on a standalone server; that is not an error. Lag is reported in
-- bytes because a byte figure is comparable across quiet and busy periods,
-- whereas a time figure collapses to zero on an idle primary.
-- ---------------------------------------------------------------------------
\echo ''
\echo '--- 7a. Replication lag per standby ---'

SELECT application_name               AS standby,
       client_addr                    AS address,
       state,
       sync_state,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn))       AS replay_lag_bytes,
       write_lag,
       flush_lag,
       replay_lag
  FROM pg_stat_replication
 ORDER BY pg_wal_lsn_diff(sent_lsn, replay_lsn) DESC NULLS LAST;

\echo ''
\echo '--- 7b. Replication slots (an inactive slot retains WAL forever) ---'

SELECT slot_name,
       slot_type,
       database,
       active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
  FROM pg_replication_slots
 ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;

-- ---------------------------------------------------------------------------
-- 8. Statistics age
--
-- Read this before trusting anything above it.
-- ---------------------------------------------------------------------------
\echo ''
\echo '--- 8. Statistics collection window ---'

SELECT stats_reset                          AS counters_reset_at,
       now() - stats_reset                  AS counters_cover,
       (SELECT count(*) FROM pg_stat_user_tables WHERE last_analyze IS NULL
                                                   AND last_autoanalyze IS NULL) AS tables_never_analyzed
  FROM pg_stat_database
 WHERE datname = current_database();

\echo ''
\echo '=== End of report ==='
\echo ''
