PostgreSQL vacuuming monitoring queries

pg_vacuuming_queries.md

Postgresql Vacuuming

To monitor progress of autovacuum

SELECT
    heap_blks_scanned/cast(heap_blks_total as numeric) * 100 as heap_blks_percent,
    progress.*,
    activity.query
FROM
    pg_stat_progress_vacuum AS progress
INNER JOIN
    pg_stat_activity AS activity
ON
    activity.pid = progress.pid;

DOES NOT SEEM TO WORK OFTEN!

To list active transactions

SELECT
    pid,
    left(query, 90),
    (now() - xact_start) as t
FROM
    pg_stat_activity
WHERE
    state IN ('idle in transaction', 'active')
ORDER BY t DESC;

To list only active/queued autiovacuum transactions

SELECT
    pid,
    left(query, 90),
    (now() - xact_start) as t
FROM
    pg_stat_activity
WHERE
    state IN ('idle in transaction', 'active')
AND
    query like 'autovacuum:%'
ORDER BY t DESC;

To kill active/queued autovacuum transactions

SELECT
     pg_terminate_backend(pid)
 FROM
     pg_stat_activity
 WHERE
     state IN ('idle in transaction', 'active')
 AND
     query like 'autovacuum:%';

To change autovacuum related config per-table

alter table keywords_report SET (autovacuum_analyze_threshold = 10000, autovacuum_vacuum_threshold = 10000);
-- reset to default
alter table keywords_report reset (autovacuum_analyze_threshold, autovacuum_vacuum_threshold);