PostgreSQL - Query to determine which tables are currently being vacuumed?

Question

I've found a query to view when vacuums have run, but not which are currently running. (http://heatware.net/databases/postgres-tables-auto-vacuum-analyze/)

Is there a query to accomplish this? I know I can hit pg_stat_activity, but some vacuums don't have the table name, but rather have pg_toast.pg_toast_3621837, so this wouldn't work 100% of the time.

Answer 1

This problem can easily be solved with system catalogs. I suggest to join on pg_locks since autovacuum acquires a ShareUpdateExclusiveLock lock on the table it is working on, to avoid some manual parsing of the query from pg_stat_activity.

The following query lists the tables being auto-vacuumed, solving the pg_toast reference if a toast table is being vacuumed, as explained in Postgres pg_toast in autovacuum - which table? question linked to by @Zeki.

SELECT n.nspname || '.' || c.relname
    FROM pg_namespace n, pg_stat_activity a, pg_locks l, pg_class c
    WHERE
        a.query LIKE 'autovacuum: %'
        AND l.pid = a.pid
        AND l.mode = 'ShareUpdateExclusiveLock'
        AND (c.oid = l.relation OR c.reltoastrelid = l.relation)
        AND n.oid = c.relnamespace
        AND n.nspname <> 'pg_toast';

Please note that while pg_stat_activity and pg_locks catalogs are shared across databases, this query will only list the tables being auto-vacuumed in the current database as pg_relation is not a shared catalog.

Answer 2

In instead of finding if the tables are being vacuumed turn auto vacuum off for the involved tables:

alter table table_name_pattern 
set (
    autovacuum_enabled = false,
    toast.autovacuum_enabled = false
);

table pattern is a glob pattern like tbl*. At the end of the query turn auto vacuum back on

alter table table_name_pattern 
set (
    autovacuum_enabled = true,
    toast.autovacuum_enabled = true
);

---

Edit in response to the commentaries:

The query to find if the involved tables are being vacuumed is unnecessary and useless. If it is known that one or more of the involved tables are being vacuumed what is it supposed to be done? Wait and keep repeating the inquiring query until none is being vacuumed? And when none is then start the long query just to discover after a while that auto vacuum was just kicked off again? There is no point. Why not just turn auto vacuum off and avoid all the hassle?

There is no ethical superiority in doing it the hard way, especially if the hard way is going to give worse results than the simple one. Simpler code is simpler to use and understand but it is not necessarily easier to build. Many times it is the opposite, requiring more intellectual effort or preparedness then the complex one.

---

If the autovacuum setting is altered inside a transaction and that transaction is rolled back the setting will be back to whatever it was before the transaction start

drop table if exists t;
create table t (id int);

begin;

alter table t
set (
    autovacuum_enabled = false,
    toast.autovacuum_enabled = false
);

\d+ t
                          Table "public.t"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id     | integer |           | plain   |              | 
Has OIDs: no
Options: autovacuum_enabled=false

rollback;

\d+ t
                          Table "public.t"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id     | integer |           | plain   |              | 
Has OIDs: no

But that setting inside the transaction will not be seen outside of the transaction so I guess autovacuum will still run. If this is true than the setting must be done outside of the transaction and controlled by a job that will turn it back regardless of what happens with the long running query.