stat-user-tables

SELECT
  current_database() datname,
  schemaname,
  relname,
  seq_scan,
  seq_tup_read,
  idx_scan,
  idx_tup_fetch,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_tup_hot_upd,
  n_live_tup,
  n_dead_tup,
  n_mod_since_analyze,
  COALESCE(last_vacuum, '1970-01-01Z') as last_vacuum,
  COALESCE(last_autovacuum, '1970-01-01Z') as last_autovacuum,
  COALESCE(last_analyze, '1970-01-01Z') as last_analyze,
  COALESCE(last_autoanalyze, '1970-01-01Z') as last_autoanalyze,
  vacuum_count,
  autovacuum_count,
  analyze_count,
  autoanalyze_count,
  pg_indexes_size(relid) as indexes_size,
  pg_table_size(relid) as table_size
FROM pg_stat_user_tables

pg_stat_user_tables_seq_scan{datname="myapp",schemaname="public",relname="users"} 1250
pg_stat_user_tables_idx_scan{datname="myapp",schemaname="public",relname="users"} 125000
pg_stat_user_tables_n_tup_ins{datname="myapp",schemaname="public",relname="users"} 50000
pg_stat_user_tables_n_live_tup{datname="myapp",schemaname="public",relname="users"} 45000
pg_stat_user_tables_n_dead_tup{datname="myapp",schemaname="public",relname="users"} 1200
pg_stat_user_tables_table_size_bytes{datname="myapp",schemaname="public",relname="users"} 8388608

# Tables with high sequential scan rate
rate(pg_stat_user_tables_seq_scan[5m]) > 1

# Ratio of sequential to index scans
rate(pg_stat_user_tables_seq_scan[5m]) /
(rate(pg_stat_user_tables_seq_scan[5m]) + rate(pg_stat_user_tables_idx_scan[5m]))

# Dead tuple ratio
pg_stat_user_tables_n_dead_tup / 
(pg_stat_user_tables_n_live_tup + pg_stat_user_tables_n_dead_tup)

# Tables with many dead tuples
pg_stat_user_tables_n_dead_tup > 10000

# Time since last autovacuum
time() - pg_stat_user_tables_last_autovacuum

# Tables never vacuumed
pg_stat_user_tables_last_autovacuum == 0

# Estimated bloat percentage (rough)
100 * pg_stat_user_tables_n_dead_tup / 
(pg_stat_user_tables_n_live_tup + pg_stat_user_tables_n_dead_tup)

# Percentage of updates that are HOT
rate(pg_stat_user_tables_n_tup_hot_upd[5m]) /
rate(pg_stat_user_tables_n_tup_upd[5m])

# Insert rate per table
rate(pg_stat_user_tables_n_tup_ins[5m])

# Total modification rate
rate(pg_stat_user_tables_n_tup_ins[5m]) +
rate(pg_stat_user_tables_n_tup_upd[5m]) +
rate(pg_stat_user_tables_n_tup_del[5m])

- alert: HighDeadTuples
  expr: |
    pg_stat_user_tables_n_dead_tup /
    (pg_stat_user_tables_n_live_tup + 1) > 0.1
    and pg_stat_user_tables_n_dead_tup > 1000
  annotations:
    summary: "Table has high dead tuple ratio"
    description: "{{ $labels.relname }} has {{ $value }}% dead tuples"

- alert: VacuumNotRunning
  expr: time() - pg_stat_user_tables_last_autovacuum > 86400
  annotations:
    summary: "Table not vacuumed in 24 hours"
    description: "{{ $labels.relname }} last vacuumed {{ $value }}s ago"

- alert: FrequentSequentialScans
  expr: |
    rate(pg_stat_user_tables_seq_scan[5m]) > 10
    and pg_stat_user_tables_n_live_tup > 10000
  annotations:
    summary: "Large table being sequentially scanned"
    description: "{{ $labels.relname }} ({{ $labels.n_live_tup }} rows) scanned {{ $value }}/sec"

-- Find tables with many sequential scans
SELECT schemaname, relname, seq_scan, idx_scan, n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 100
  AND n_live_tup > 10000
ORDER BY seq_scan DESC;