Re: huge memory of Postgresql backend process

On Fri, Sep 9, 2022 at 6:42 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> On Fri, 9 Sept 2022 at 01:48, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Moreover, HASH partitioning is effectively incapable of being
> > pruned, so that every query is going to touch every partition.
> > (IMO, hash partitioning is basically never a good idea.)
>
> I think that might have only briefly been true during the v11 cycle,
> but by the time the release came we had hash partition and partition
> pruning.

That is correct.

create table hp (a int, b text) partition by hash (a, b);
create table hp0 partition of hp for values with (modulus 2, remainder 0);
create table hp1 partition of hp for values with (modulus 2, remainder 1);
insert into hp values (1, 'abc');
select tableoid::regclass, * from hp;
tableoid | a | b
----------+---+-----
hp0 | 1 | abc
(1 row)
explain select * from hp where a = 1 and b = 'abc';
QUERY PLAN
--------------------------------------------------------
Seq Scan on hp0 hp (cost=0.00..29.05 rows=1 width=36)
Filter: ((a = 1) AND (b = 'abc'::text))
(2 rows)

Maybe, James is thinking that the reason for high memory usage is the
same when using PG v13 as it is when using v14. v13 can't handle
UPDATE/DELETEs of partitioned tables as well as v14, though only for
queries where partition pruning isn't being used. It's true though
that the backend-lifetime caching of partition metadata, especially
when there are too many backends doing it, can add up over many
backends and many partitions accessed in each. So your advice of
lowering the number of backends or the number of partitions will help.

--
Thanks, Amit Langote
EDB: http://www.enterprisedb.com