VACUUM takes very long and can not be cancelled

Question

I just issue this command to a table and its been an 2 hours but not finished: vacuum verbose people_data

I can not even cancel the process because I can not see the PID using this query:

SELECT pid,
       now() - pg_stat_activity.query_start AS duration,
       query,
       state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes' 
  and state = 'idle'
order by duration desc;

The size of this table is only 16 GB. The dead tuple counts only max 130 using (only 10 tables has dead tuple above 100) :

select n_live_tup, n_dead_tup, relname 
from pg_stat_all_tables 
where n_dead_tup > 100 
limit 20;

Now some people report me that they can not access the DB? Luckily this is development database, so it just halts the development process.

How can I cancel the vacuum without seeing the PID?

Answer 1

VACUUM (VERBOSE) will not block concurrent table access. You didn't use VACUUM (FULL), did you?

The reason why you cannot see the autovacuum process is that it has state = 'active'. You can look into the view pg_stat_progress_vacuum to see if and how VACUUM is proceeding.

With a small table like that, the most likely explanation is that there is a concurrent open database transaction that is holding a high lock on the table. That could also explain why people report that they cannot access the database.