Benedict Holland <benedict.m.holland@xxxxxxxxx> wrote: > 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. I was trying to suggest techniques which would prevent that bloat and make the VACUUM FULL unnecessary. But, now that I've had a chance to format the attachment into a readable format, I agree that it isn't part of the problem. The iceberg in this case is the ALTER TABLE, which is competing with two other queries. > The blocked statements are the select count(*) and the alter > table. OK. > Both are blocked on the update table command. Not directly. The lock held by the UPDATE would *not* block the SELECT; but it *does* block the ALTER TABLE command, which can't share the table while it changes the structure of the table. The SELECT is blocked behind the ALTER TABLE. > The alter table command SHOULD be blocked and that is fine. I'm glad we're on the same page there. > 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. In PostgreSQL SELECT count(*) must scan the table to see which rows are visible to the executing database transaction. Without that, it can't give a completely accurate count from a transactional perspective. If you can settle for a non-transactional approximation, select the reltuples value from the pg_class row for the table. > I also don't understand why a select count(*) requires an > AccessShareLock. I don't understand why a select should lock > anything at all. So that the table isn't dropped or truncated while the count is scanning the table. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
