Re: Relation locking and relcache load (was Re: Going for "all green" buildfarm results)

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> I think the best solution for this might be to put the responsibility
>> for creating system catalogs' toast tables into the bootstrap phase
>> instead of making initdb do it afterwards.

> Would this make it much more difficult to support user-defined indexes
> on system catalogs?

AFAICS the problems with that are orthogonal to this. You'll never have
user-defined (as in "added after initdb") indexes on shared catalogs,
because there is no way to update their pg_class descriptions in all
databases at once. For non-shared catalogs there's nothing except
access permissions stopping you from adding ordinary indexes now.
We don't support partial or functional indexes on system catalogs,
but the implementation reasons for that are unrelated to what I'm doing.

> It looks like we don't support that at the moment
> but as we see larger Postgres installations it seems likely we'll need
> to. I don't really consider myself a very heavy Postgres user but I've
> got databases w/ > 30k entries in pg_class and near 300k in
> pg_attribute...

And are you seeing any performance issues related to lack of indexes?
For the system catalogs we understand the access patterns pretty well
(I think), and I thought we pretty much had the right indexes on them
already.

regards, tom lane