PostgreSQL : autovacuum

Tables en auto-vacuum

SELECT pid, query FROM pg_stat_activity WHERE query LIKE 'autovacuum: %';

Calcul du seuil de déclenchement du vacuum :

vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples

postgres 9.6 :

La vue pg_stat_progress_vacuum peut aider :

                       View "pg_catalog.pg_stat_progress_vacuum"
       Column       |  Type   | Collation | Nullable | Default | Storage  | Description 
--------------------+---------+-----------+----------+---------+----------+-------------
 pid                | integer |           |          |         | plain    | 
 datid              | oid     |           |          |         | plain    | 
 datname            | name    |           |          |         | plain    | 
 relid              | oid     |           |          |         | plain    | 
 phase              | text    |           |          |         | extended | 
 heap_blks_total    | bigint  |           |          |         | plain    | 
 heap_blks_scanned  | bigint  |           |          |         | plain    | 
 heap_blks_vacuumed | bigint  |           |          |         | plain    | 
 index_vacuum_count | bigint  |           |          |         | plain    | 
 max_dead_tuples    | bigint  |           |          |         | plain    | 
 num_dead_tuples    | bigint  |           |          |         | plain    | 
View definition:
 SELECT s.pid,
    s.datid,
    d.datname,
    s.relid,
        CASE s.param1
            WHEN 0 THEN 'initializing'::text
            WHEN 1 THEN 'scanning heap'::text
            WHEN 2 THEN 'vacuuming indexes'::text
            WHEN 3 THEN 'vacuuming heap'::text
            WHEN 4 THEN 'cleaning up indexes'::text
            WHEN 5 THEN 'truncating heap'::text
            WHEN 6 THEN 'performing final cleanup'::text
            ELSE NULL::text
        END 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)
     JOIN pg_database d ON s.datid = d.oid;

Date du dernier vacuum, auto-vacuum, analyse, auto-analyse :

SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze  FROM pg_stat_all_tables; 
or
SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables;

Durée de l'autovacuum

SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, state, query
FROM pg_stat_activity 
WHERE query LIKE '%autovacuum%' AND query NOT LIKE '%pg_stat_activity%'
ORDER BY xact_start;