Re: Does RelCache/SysCache shrink except when relations are deleted?

On Thu, Sep 29, 2011 at 10:45 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> ... It seems that we used to have
>> some kind of LRU algorithm to prevent excessive memory usage, but we
>> rippped it out because it was too expensive (see commit
>> 8b9bc234ad43dfa788bde40ebf12e94f16556b7f).
>
> Not only was it too expensive, but performance fell off a cliff as soon
> as you had a catalog working set large enough to cause the code to
> actually do something,  ...

Sure, a big working set is going to cause a performance problem if you
start flushing cache entries that are being regularly used. But the
point is just because you have, at some time, accessed 100,000 tables
during a session does not mean that your working set is that large.
The working set is the set of things that you are actually using
regularly, not the things you've *ever* accessed.

In addition to the problem of blowing out memory, there are a number
of other things about the current code that don't seem well-suited to
dealing with large numbers of tables. For example, catcache hash
tables can't be resized, so for very large numbers of entries you can
potentially have to walk a very long chain. And, you can exhaust the
shared memory space for the primary lock table, leading to, for
example, inability to back up the database using pg_dump (ouch!).

I can't really explain why people seem to keep wanting to create
hundreds of thousands or even millions of tables, but it's not like
MauMau's customer is the first one to try to do this, and I'm sure
they won't be the last. I don't want to de-optimize the more common
(and sensible) cases too much, but "slow" still trumps "fails
outright".

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company