Re: PostgreSQL (9.3 and 9.6) eats all memory when using many tables

hubert depesz lubaczewski <depesz@depesz.com> writes:
> While I do appreciate caching of metadata, it is causing serious
> problems, which we will alleviate with server_lifetime, but I would much
> prefer a setting like:
> internal_cache_limit = 256MB

Be careful what you ask for, you might get it.

There used to be exactly such a limit in the catcache logic, which we
ripped out because it caused far more performance problems than it fixed.
See
https://www.postgresql.org/message-id/flat/5141.1150327541%40sss.pgh.pa.us

While we have no direct experience with limiting the plancache size,
I'd expect a pretty similar issue there: a limit will either do nothing
except impose substantial bookkeeping overhead (if it's more than the
number of plans in your working set) or it will result in a performance
disaster from cache thrashing (if it's less).  You can only avoid falling
off the performance cliff if your workload has *very* strong locality of
reference, and that tends not to be the case.

Another problem, if the bloat is being driven by explicit PREPARE commands
as you illustrate here, is that the backend doesn't get to silently
discard prepared statements.  We could reduce the amount of memory per
prepared statement by dropping the plan tree, but we'd still have to keep
the parse tree, so there's still bloat.

            regards, tom lane