Yes. I needed to do a full vacuum. Again, the database is very large. I batch inserted quite a lot of data and then modified that data. The vacuum isn't blocking anything. It was blocking other tables (as expected) but continues to run and clean. My tables in general are around 10GB, each update seems to nearly double the size of it so I required a full vacuum. The blocked statements are the select count(*) and the alter table. Both are blocked on the update table command. The alter table command SHOULD be blocked and that is fine. The select count(*) should never be blocked as that is the whole point of running an MVCC operation at least to my understanding. I can even accept the use case that the select should block with an Alter Table operation if data is retrieved from the table, but a select count(*) only returns the number of rows and should be table space independent. I also don't understand why a select count(*) requires an AccessShareLock. I don't understand why a select should lock anything at all.
You have three VACUUM FULL commands running? VACUUM FULL is very aggressive maintenance, which is only needed for cases of extreme bloat. It does lock the table against any concurrent access, since it is completely rewriting it.
Now, if you are running UPDATE statements which affect all rows in a table, you will *get* extreme bloat. You either need to do such updates as a series of smaller updates with VACUUM commands in between, or schedule your aggressive maintenance for a time when it can have exclusive access to the tables with minimal impact.
Reporting the other issues without mentioning the VACUUM FULL processes is a little bit like calling from the Titanic to mention that the ship isn't going as fast as it should and neglecting to mention the iceberg. :-) -Kevin
