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:

       now() - pg_stat_activity.query_start AS duration,
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?

  • 3
    Well the vacuum process won't be idle if it's currently working, so your WHERE clause will never show it. Maybe it's not doing something and waiting for a lock. Change it to WHERE state <> 'idle'
    – user1822
    Nov 23, 2022 at 8:56
  • 2
    Does this answer your question? How much time will a vacuum/autovacuum operation take?
    – John K. N.
    Nov 24, 2022 at 8:00
  • Hi, nice insight. It does not answer my problem though. thanks @JohnK.N.
    – padjee
    Nov 24, 2022 at 15:53

1 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.

  • I didn't use VACUUM FULL, It's too dangerous because the HDD is less than 20% free. I just could not run any queries. Luckily after a few hours of fiddling around, I cancel it accidentally by pressing the status text (somehow the text can be clicked.. how do i suppose to know that !) on my DBeaver GUI. It takes a while to cancel the ongoing VACUUM. thanks for the information. @LaurenzAlbe
    – padjee
    Nov 24, 2022 at 15:58
  • I repeat: VACUUM does not block concurrent SELECT, INSERT, UPDATE or DELETE statements. Nov 24, 2022 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.