MIME-Version: 1.0
From: =?UTF-8?B?Q8Opc2FyIE11w7Fveg==?= <xcumail@gmail.com>
Date: Sun, 22 Jun 2025 20:07:06 +0200
Message-ID: <CAMXZnGjLO2V+4nht7xvv04K4hE6w3DKbRZZKky1t6eYSEa9Sug@mail.gmail.com>
Subject: Memory overhead of a large number of partitions in the same table
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000003774b206382cf78a"
Archived-At: <https://www.postgresql.org/message-id/CAMXZnGjLO2V%2B4nht7xvv04K4hE6w3DKbRZZKky1t6eYSEa9Sug%40mail.gmail.com>
Precedence: bulk

--0000000000003774b206382cf78a
Content-Type: text/plain; charset="UTF-8"

I would like to understand how much can partitioning can contribute to
memory usage in Postgres backend processes.

My application mainly runs SELECT statements in a partitioned table. The
table is partitioned by size (100K rows per partition, 730 partitions in
total). However, each Postgres backend process to run these SELECTs takes
~300 MB in memory, which seems a lot compared to what I've seen around.

work_mem is set to 16MB, so nothing too crazy. Also, I am using Pss to
measure the memory used by each backend process so that shared_buffers
don't mess the whole thing. Here's the output for a couple of them

Pss:             1260383 kB
Pss_Anon:         305501 kB
Pss_File:            199 kB
Pss_Shmem:        954682 kB

Pss:             1247796 kB
Pss_Anon:         293041 kB
Pss_File:            200 kB
Pss_Shmem:        954554 kB

Is this expected? Or the overhead of partitions should be smaller in terms
of memory consumption? I'm using Partman to partition the table.

Also, shared_buffers for this instance is 25GB with a total of 60GB memory.
I can provide output for explain (analyze, buffers) if needed, but the main
index used for queries is fully in memory (it takes around 18GB of space).

--0000000000003774b206382cf78a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">I would like to understand how much can partitioning can c=
ontribute to memory usage in Postgres backend processes.<br><br>My applicat=
ion mainly runs SELECT statements in a partitioned table. The table is part=
itioned by size (100K rows per partition, 730 partitions in total). However=
, each Postgres backend process to run these SELECTs takes ~300 MB in memor=
y, which seems a lot compared to what I&#39;ve seen around.<br><br>work_mem=
 is set to 16MB, so nothing too crazy. Also, I am using Pss to measure the =
memory used by each backend process so that shared_buffers don&#39;t mess t=
he whole thing. Here&#39;s the output for a couple of them<br><br>Pss: =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 1260383 kB<br>Pss_Anon: =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 305501 kB<br>Pss_File: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0199 kB<br>Pss_Shmem: =C2=A0 =C2=A0 =C2=A0 =C2=A0954682 kB<br><br>Pss=
: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 1247796 kB<br>Pss_Anon: =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 293041 kB<br>Pss_File: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0200 kB<br>Pss_Shmem: =C2=A0 =C2=A0 =C2=A0 =C2=A0954554 kB<br><br>=
Is this expected? Or the overhead of partitions should be smaller in terms =
of memory consumption? I&#39;m using Partman to partition the table.<div><b=
r></div><div>Also, shared_buffers for this instance is 25GB with a total of=
 60GB memory. I can provide output for explain (analyze, buffers) if needed=
, but the main index used for queries is fully in memory (it takes around 1=
8GB of space).</div></div>

--0000000000003774b206382cf78a--