From: Andreas Seltenreich <andreas.seltenreich@credativ.de>
To: pgsql-hackers@lists.postgresql.org
Subject: Excessive memory usage in multi-statement queries w/ partitioning
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/26.0.50 (gnu/linux)
Date: Wed, 22 May 2019 21:15:40 +0200
Message-ID: <87ftp6l2qr.fsf@credativ.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

Hi,

a customer reported excessive memory usage and out-of-memory ERRORs
after introducing native partitioning in one of their databases.  We
could narrow it down to the overhead introduced by the partitioning when
issuing multiple statements in a single query.  I could reduce the
problem to the following recipe:

--8<---------------cut here---------------start------------->8---
#!/bin/bash

# create 100 partitions
psql -c 'create table t(c int primary key) partition by range(c)'
for i in {1..100}; do
    psql -e -c "create table t$i partition of t for values
         from ($(((i-1)*100))) to ($((i*100-1))) "
done

# artificially limit per-process memory by setting a resource limit for
# the postmaster to 256MB

prlimit -d$((256*1024*1024)) -p $POSTMASTER_PID
--8<---------------cut here---------------end--------------->8---

Now, updates to a partition are fine with 4000 update statements:

,----
| $ psql -c "$(yes update t2 set c=3Dc where c=3D6 \; | head -n 4000)"
| UPDATE 0
`----

=E2=80=A6but when doing it on the parent relation, even 100 statements are
enough to exceed the limit:

,----
| $ psql -c "$(yes update t set c=3Dc where c=3D6 \; | head -n 100)"
| FEHLER:  Speicher aufgebraucht
| DETAIL:  Failed on request of size 200 in memory context "MessageContext".
`----

The memory context dump shows plausible values except for the MessageContex=
t:

TopMemoryContext: 124336 total in 8 blocks; 18456 free (11 chunks); 105880 =
used
  [...]
  MessageContext: 264241152 total in 42 blocks; 264 free (0 chunks); 264240=
888 used
  [...]

Maybe some tactically placed pfrees or avoiding putting redundant stuff
into MessageContext can relax the situation?

regards,
Andreas