Recent work on parallel query has opened my eyes to exactly how
frequently we request locks on various catalog tables. (Attached file
is a lock analysis on a representative Pg server).
Given these are catalog tables, we aren't doing much to them that
requires a strong lock. Specifically, only CLUSTER and VACUUM FULL
touch those tables like that. When we do that, pretty much everything
else hangs, cos you can't get much done while fundamental tables are
locked.
So my proposal is that we invent a "big catalog lock". The benefit of
this is that we greatly reduce lock request traffic, as well as being
able to control exactly when such requests occur. (Fine grained
locking isn't always helpful).
Currently, SearchCatCache() requests locks on individual catalog
tables. Alternatively, we could request an AccessShareLock on a "big
catalog lock" that must be accessed first before a strong
relation-specific lock is requested. We just need to change the lockid
used for each cache.
We can still CREATE, ALTER, DROP and VACUUM all catalog tables - but
this idea would block VACUUM FULL, but that would have been blocked
anyway by general activity.
We reduce lock traffic by having SearchCatCache() use a new call
heap_catalog_open() which calls a new LockCatalogLock() which
specifically caches whether we have already locked the BigCatalogLock
or not. That cache can be cleared easily and cheaply at EOX. And it
can be set quickly and easily in parallel worker tasks.
We then add a special locking clause for VAC FULL on catalog tables.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services