stat-progress-vacuum

SELECT
  d.datname,
  s.relid::regclass::text AS relname,
  s.param1 AS phase,
  s.param2 AS heap_blks_total,
  s.param3 AS heap_blks_scanned,
  s.param4 AS heap_blks_vacuumed,
  s.param5 AS index_vacuum_count,
  s.param6 AS max_dead_tuples,
  s.param7 AS num_dead_tuples
FROM
  pg_stat_get_progress_info('VACUUM'::text)
  s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
LEFT JOIN
  pg_database d ON s.datid = d.oid

pg_stat_progress_vacuum_phase{datname="myapp",relname="users",phase="scanning heap"} 1
pg_stat_progress_vacuum_phase{datname="myapp",relname="users",phase="initializing"} 0
pg_stat_progress_vacuum_heap_blks{datname="myapp",relname="users"} 100000
pg_stat_progress_vacuum_heap_blks_scanned{datname="myapp",relname="users"} 45000
pg_stat_progress_vacuum_heap_blks_vacuumed{datname="myapp",relname="users"} 42000
pg_stat_progress_vacuum_index_vacuums{datname="myapp",relname="users"} 0
pg_stat_progress_vacuum_max_dead_tuples{datname="myapp",relname="users"} 11184810
pg_stat_progress_vacuum_num_dead_tuples{datname="myapp",relname="users"} 8500000

# Percentage of heap scanned
100 * pg_stat_progress_vacuum_heap_blks_scanned / 
pg_stat_progress_vacuum_heap_blks

# Percentage of dead tuples collected
100 * pg_stat_progress_vacuum_num_dead_tuples / 
pg_stat_progress_vacuum_max_dead_tuples

# Find tables in specific phases
pg_stat_progress_vacuum_phase{phase="vacuuming indexes"} == 1

# Blocks remaining
pg_stat_progress_vacuum_heap_blks - pg_stat_progress_vacuum_heap_blks_scanned

max_dead_tuples = maintenance_work_mem / 6 bytes per tuple

- alert: VacuumStuck
  expr: |
    (pg_stat_progress_vacuum_heap_blks_scanned / 
     pg_stat_progress_vacuum_heap_blks) < 0.1
    and time() - pg_stat_activity_autovacuum_timestamp_seconds > 3600
  annotations:
    summary: "Vacuum appears stuck"
    description: "Vacuum on {{ $labels.relname }} has made < 10% progress in 1 hour"

- alert: ManyIndexVacuumCycles
  expr: pg_stat_progress_vacuum_index_vacuums > 5
  annotations:
    summary: "Many index vacuum cycles"
    description: "Consider increasing maintenance_work_mem for {{ $labels.relname }}"

SELECT 
  datname,
  relid::regclass as table_name,
  phase,
  heap_blks_total,
  heap_blks_scanned,
  heap_blks_vacuumed,
  index_vacuum_count,
  max_dead_tuples,
  num_dead_tuples,
  round(100.0 * heap_blks_scanned / NULLIF(heap_blks_total, 0), 2) as pct_complete
FROM pg_stat_progress_vacuum
JOIN pg_database ON pg_stat_progress_vacuum.datid = pg_database.oid;