til icon indicating copy to clipboard operation
til copied to clipboard

How to check Postgres VACUUM process

Open xluffy opened this issue 6 months ago • 0 comments

SELECT
  p.pid,
  now() - a.xact_start AS duration,
  coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting,
  CASE
    WHEN a.query ~*'^autovacuum.*to prevent wraparound' THEN 'wraparound'
    WHEN a.query ~*'^vacuum' THEN 'user'
  ELSE
    'regular'
  END AS mode,
  p.datname AS database,
  p.relid::regclass AS table,
  p.phase,
  pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) AS table_size,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) AS scanned,
  pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) AS vacuumed,
  round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct,
  round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct,
  p.index_vacuum_count,
  round(100.0 * p.num_dead_tuples / p.max_dead_tuples,1) AS dead_pct
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a using (pid)
ORDER BY now() - a.xact_start DESC;

   pid   |    duration     |       waiting       |    mode    |      database      |         table         |     phase     | table_size | total_size | scanned | vacuumed | scanned_pct | vacuumed_pct | index_vacuum_count | dead_pct
---------+-----------------+---------------------+------------+--------------------+-----------------------+---------------+------------+------------+---------+----------+-------------+--------------+--------------------+----------
 1783330 | 00:16:52.247513 | Timeout.VacuumDelay | wraparound |    gh_prod_user    |     xxx_audit_logs    | scanning heap | 395 GB     | 483 GB     | 15 GB   | 0 bytes  |         3.8 |          0.0 |                  0 |      0.0
(1 row)

Time: 2.099 ms

xluffy avatar Aug 21 '24 09:08 xluffy