PostgresSQL - a query to determine which tables are currently vacuuming?

Question

I found a request to view when starting a vacuum, but not at present. ( http://heatware.net/databases/postgres-tables-auto-vacuum-analyze/ )

Is there a request for this? I know that I can hit pg_stat_activity, but some vacuum cleaners do not have a table name, but have pg_toast.pg_toast_3621837, so this will not work for 100% of the time.

Answer 1

Instead of finding if vacuuming the tables, turn off the automatic vacuum for the tables involved:

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

a table template is a glob template, for example tbl* . At the end of the request, turn on the automatic vacuum again

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

---

Edit in response to comments:

A query to find out if the involved tables are vacuumed is unnecessary and unhelpful. If it is known that one or more of the tables involved is vacuumed, what should be done? Wait and keep repeating the query until no one has vacuumed? And when none of them starts a long request, in order to find out after a while that the automatic vacuum has just been released? Pointlessly. Why not just turn off the automatic vacuum and avoid all the hassle?

There is no ethical superiority in doing it hard, especially if the hard way gives worse results than the simple one. Simplified code is easier to use and understand, but it is not always easy to build. Many times the opposite, requiring more intellectual effort or willingness, and then more complex.

---

If the autovacuum parameter is changed inside the transaction and that the transaction is canceled, the setting will revert to what it was before the transaction began.

 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 this setting inside the transaction will not be visible outside the transaction, so I think autovacuum will still work. If this is true, then the parameter must be executed outside the transaction and controlled by the task, which will return it regardless of what happens with a long request.

Answer 2

This problem can be easily solved using system directories . I suggest joining pg_locks , since autovacuum gets a ShareUpdateExclusiveLock lock on the table it is working on to avoid manually parsing the request from pg_stat_activity .

The following query lists the tables that automatically vacuum, resolving the pg_toast link, if you vacuum the toast table, as explained in Postgres pg_toast in autovacuum - which table? question related to @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 although the pg_stat_activity and pg_locks are shared between the databases, this query will only list tables that are automatically vacuumed in the current database, since pg_relation not a shared directory.