Re: [BUGS] PostgreSQL backend process high memory usage issue

Tom Lane-2 wrote:
>
>
> I don't think it's a leak, exactly: it's just that the "relcache" entry
> for each one of these views occupies about 100K. A backend that touches
> N of the views is going to need about N*100K in relcache space. I can't
> get terribly excited about that. Trying to reduce the size of the
> relcache would be a net loss for most usage patterns (ie, we'd end up
> increasing the amount of re-fetching from the system catalogs that
> backends would have to do). And I don't think that this test case has
> much of anything to do with sane application design, anyway. Do you
> really need that many complex views? Do you really need to have most
> sessions touching all of them?
>
>

Thanks for the clarification, that answers our question and gives us a good
direction where to look for further information. We have gained more
confidence on moving toward using PostgreSQL as our multitenant database
backend.

The tests were designed to show the high memory usage findings while we are
evaluating PostgreSQL, and yes, it's far from real world scenario. However,
the concern is not come from nothing - current our system is running on
Microsoft SQL Server with one db per tenant multitenancy model. We have one
db server that has 5000 tenant databases, each with 200 tables and 500
views. There are quite a few views that are much more complex than the one
shown in the test. When a request comes in, the application servers will
randomly pick a connection from the pool to query the db, so theoretically
every connection could eventually hit all views, in real wold it may take
quite a while to fill-up the memory until it reaches an unacceptable size.
However, it just feel a little weird that there isn't a convenient way for
PostgreSQL to control the cache memory usage of backend process. :)

We are still at early staging of moving to a different multitenant db model
and there are plenty of options that we can go or get around issues like
this. Thanks again.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-backend-process-high-memory-usage-issue-tp4289407p4301552.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.