Re: BUG #16363: Memory increases for each table accessed until connection is closed

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> I have a specific use case when I need a lot of similar tables in my
> database, in one case, for example, I have more than 700 tables.
> Whenever I insert data in one of these tables within the same connection,
> the memory used by that connection process will increase and never be freed
> (unless I close the connection), because of that I'm getting Out Of Memory
> errors in my backend since the connection eventually consumes the entire
> memory available in my system.

Yes, Postgres caches some information about every table your session
has accessed, and no you don't have a lot of control over that, and
no it isn't a bug.

Having said that, the amount of memory consumed this way is in the
vicinity of a few tens of KB per table, according to some simple
experiments I just did. I couldn't get a process touching 700 simple
tables to eat more than ~70MB of space. So either you are talking about
tables with enormously complex schemas, or there's something else going
on. (Please note that the SO post you are referencing describes an
attempt to use circa 100000 tables/views in one session, not 700.)

The memory consumption curve you showed looks suspiciously like you have
circa 4GB shared buffers allocated and the reported number is mostly about
how many of those buffers the process has touched so far. This is a
common misunderstanding; "top" and similar tools tend to be really bad
about accounting for shared memory usage in any useful way. In any case,
the process you've shown us stopped growing its space consumption
some time ago, so I wonder where the OOM complaint actually came from.

The short answer is that you're probably barking up the wrong tree,
but there's not enough information here to provide any useful guesses
about which is the right tree. Please see

https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

for some hints about submitting a trouble report that's complete
enough to garner useful responses.

regards, tom lane