Re: Understanding Postgres Memory Usage

Okay, I got a semi-reproducible test case:
https://gist.github.com/luhn/2b35a9b31255e3a6a2e6a06d1213dfc9

The one caveat is that the memory rise only happens when using a
HashAggregate query plan (included in the gist), which I can't find a way
to get Postgres to reliably use.

If you need it, I could probably find another test case.

— Theron

On Thu, Aug 25, 2016 at 5:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Theron Luhn <theron(at)luhn(dot)com> writes:
> > Okay, here's the output:
> > https://gist.github.com/luhn/a39db625ba5eed90946dd4a196d12220
>
> Hm, well the only thing there that looks even slightly out of the
> ordinary is the amount of free space in TopMemoryContext itself:
>
> TopMemoryContext: 3525712 total in 432 blocks; 3444272 free (12654
> chunks); 81440 used
>
> Normally, TopMemoryContext doesn't get to more than a few hundred K,
> and in the cases I've seen where it does, it's usually been because of
> leaky coding that was allocating stuff there and never cleaning it up.
> But you've got no more than the typical amount of space still allocated
> there, which seems to kill the "leak in TopMemoryContext" theory.
> And in any case there is nowhere near 100MB accounted for by the whole
> dump.
>
> Are you using any other PLs besides plpgsql? We've seen cases where
> bloat occurred within plpython or plperl, and wasn't visible in this
> dump because those languages don't use PG's memory management code.
> Or maybe some nonstandard extension?
>
> If not that, then I'd have to speculate that the query you're running is
> triggering some bug or otherwise pathological behavior. Can you put
> together a self-contained test case?
>
> regards, tom lane
>