On Thu, Sep 17, 2020 at 10:06:07AM -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> =?UTF-8?Q?=C3=98ystein_Kolsrud?= <kolsrud@gmail.com> writes:
> > So my question is: When does a postgres process forked for a connection use
> > private memory instead of shared, and what can I do to avoid this?
>
> The only significant long-term consumption of private memory is for
> caches. There are catalog caches, which can get large if the session
> accesses a whole lot of database objects (e.g., thousands of different
> tables). Some of the PLs maintain caches with parsed versions of any
> function that's been executed. (An ex-employer of mine had a lot of
> trouble in that regard, because they had hundreds of thousands of lines
> worth of plpgsql functions.) There isn't any user-accessible knob for
> limiting the size of those caches. If you have a problem of that sort,
> about the only way to mitigate it is to use fewer backends so that the
> total memory consumption stays in bounds, or redesign your application.
> In some cases it might help to restart your sessions when they get too
> big, but that seems like at best a band-aid.
>
> regards, tom lane
For what it's worth, I have 171305 lines of plpgsql/sql
functions and it hasn't caused any problem on a server
with 4GB RAM. With a small number of long-lived
connections.
cheers,
raf