0

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?

padjee
  • 187
  • 1
  • 7
  • 22

1 Answers1

0

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.

Laurenz Albe
  • 51,298
  • 4
  • 39
  • 69
  • 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 '22 at 15:58
  • I repeat: VACUUM does not block concurrent SELECT, INSERT, UPDATE or DELETE statements. – Laurenz Albe Nov 24 '22 at 16:10