1  #!/bin/sh
 2  # pg-health-check.sh — PostgreSQL performance health check
 3  #
 4  # Run: sh scripts/pg-health-check.sh
 5  # Or add to cron jobs for regular monitoring.
 6  
 7  set -eu
 8  
 9  DB="mmo"
10  
11  echo "=== PostgreSQL Health Check ==="
12  echo "Time: $(date -u '+%Y-%m-%d %H:%M:%S UTC')"
13  echo ""
14  
15  # 1. Connection status
16  echo "--- Connections ---"
17  psql -d "$DB" -c "
18  SELECT state, count(*)
19  FROM pg_stat_activity
20  WHERE datname = '$DB'
21  GROUP BY state
22  ORDER BY count DESC;
23  "
24  
25  # 2. Table sizes
26  echo "--- Top 10 Tables by Size ---"
27  psql -d "$DB" -c "
28  SELECT schemaname || '.' || relname AS table,
29         pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS total_size,
30         n_live_tup AS live_rows
31  FROM pg_stat_user_tables
32  ORDER BY pg_total_relation_size(schemaname || '.' || relname) DESC
33  LIMIT 10;
34  "
35  
36  # 3. Missing indexes (sequential scans on large tables)
37  echo "--- Potential Missing Indexes (seq scans on large tables) ---"
38  psql -d "$DB" -c "
39  SELECT schemaname || '.' || relname AS table,
40         seq_scan, seq_tup_read,
41         idx_scan, n_live_tup,
42         ROUND(100.0 * seq_scan / GREATEST(seq_scan + idx_scan, 1), 1) AS seq_pct
43  FROM pg_stat_user_tables
44  WHERE n_live_tup > 1000
45    AND seq_scan > idx_scan
46  ORDER BY seq_tup_read DESC
47  LIMIT 10;
48  "
49  
50  # 4. Table bloat / autovacuum
51  echo "--- Autovacuum Status (tables with most dead tuples) ---"
52  psql -d "$DB" -c "
53  SELECT schemaname || '.' || relname AS table,
54         n_dead_tup, n_live_tup,
55         ROUND(100.0 * n_dead_tup / GREATEST(n_live_tup, 1), 1) AS dead_pct,
56         last_autovacuum::text,
57         last_autoanalyze::text
58  FROM pg_stat_user_tables
59  WHERE n_dead_tup > 100
60  ORDER BY n_dead_tup DESC
61  LIMIT 10;
62  "
63  
64  # 5. Slowest queries (requires pg_stat_statements)
65  echo "--- Top 5 Slowest Queries (by mean time) ---"
66  psql -d "$DB" -c "
67  SELECT ROUND(mean_exec_time::numeric, 2) AS mean_ms,
68         calls,
69         ROUND(total_exec_time::numeric, 0) AS total_ms,
70         LEFT(query, 80) AS query_preview
71  FROM pg_stat_statements
72  WHERE dbid = (SELECT oid FROM pg_database WHERE datname = '$DB')
73    AND calls > 5
74  ORDER BY mean_exec_time DESC
75  LIMIT 5;
76  " 2>/dev/null || echo "(pg_stat_statements not available)"
77  
78  # 6. Database size
79  echo "--- Database Size ---"
80  psql -d "$DB" -c "SELECT pg_size_pretty(pg_database_size('$DB')) AS database_size;"
81  
82  echo ""
83  echo "=== Health check complete ==="