MIME-Version: 1.0
From: Alexandru Lazarev <alexandru.lazarev@gmail.com>
Date: Wed, 13 Nov 2024 21:09:35 +0200
Message-ID: <CAL93h0GtZ5H+X+8CRDOkcN1w0Bha2w3-57CqKSNFC2xrTNhEhA@mail.gmail.com>
Subject: work_mem RAM Accounting in PostgreSQL
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000bdf4200626d01301"
Archived-At: <https://www.postgresql.org/message-id/CAL93h0GtZ5H%2BX%2B8CRDOkcN1w0Bha2w3-57CqKSNFC2xrTNhEhA%40mail.gmail.com>
Precedence: bulk

--000000000000bdf4200626d01301
Content-Type: text/plain; charset="UTF-8"

Hello Everyone,

I have some questions regarding how the "work_mem" parameter affects the
overall RAM usage of PostgreSQL processes within a physical host or
container.

Each backend process during SQL execution may allocate N * "work_mem"
simultaneously. For example, if "work_mem" is set to 32MB and N=5 (i.e. 5
simultaneous and/or sequential ORDER and hash operations), and the initial
RAM usage (RSS - Resident Set Size) of the backend is 10MB, I would expect
the backend process to use 160MB (32MB * 5) + 10MB, resulting in a total
RAM usage of 170MB.

My questions are as follows:

1. What happens to the allocated "work_mem" after the execution of query
nodes? Are these memory allocations freed?
2. If they are freed, do they remain in the RSS of the PostgreSQL backend?
3. From various sources, I understand that these allocations are freed
after each node execution due to memory contexts, but they might remain in
some sort of backend memory pool for future reuse. Is this correct?
4. If so, will this memory be accounted for as used RAM on my
Linux/Container system after the backend returns to an idle state (e.g.,
connection pooling)?

Additionally: If the above is true, and my PostgreSQL host or container is
limited to 16GB of RAM, what would happen if I have 100 pooled connections,
each gradually allocating those 160MB? Will this memory be reclaimed (if I
understood it correctly as a kind of inactive anon mem), or will the OOM
Killer be triggered at some point (because it is real allocated memory)?

Thank you for your insights.

Best regards,
AlexL
Java Dev

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

<div dir=3D"ltr"><div dir=3D"ltr"><div>Hello Everyone,</div><div><br></div>=
<div>I have some questions regarding how the &quot;work_mem&quot; parameter=
 affects the overall RAM usage of PostgreSQL processes within a physical ho=
st or container.</div><div><br></div><div>Each backend process during SQL e=
xecution may allocate N * &quot;work_mem&quot; simultaneously. For example,=
 if &quot;work_mem&quot; is set to 32MB and N=3D5 (i.e. 5 simultaneous and/=
or sequential ORDER and hash operations), and the initial RAM usage (RSS - =
Resident Set Size) of the backend is 10MB, I would expect the backend proce=
ss to use 160MB (32MB * 5) + 10MB, resulting in a total RAM usage of 170MB.=
</div><div><br></div><div>My questions are as follows:</div><div><br></div>=
<div>1. What happens to the allocated &quot;work_mem&quot; after the execut=
ion of query nodes? Are these memory allocations freed?</div><div>2. If the=
y are freed, do they remain in the RSS of the PostgreSQL backend?</div><div=
>3. From various sources, I understand that these allocations are freed aft=
er each node execution due to memory contexts, but they might remain in som=
e sort of backend memory pool for future reuse. Is this correct?</div><div>=
4. If so, will this memory be accounted for as used RAM on my Linux/Contain=
er system after the backend returns to an idle state (e.g., connection pool=
ing)?</div><div><br></div><div>Additionally: If the above is true, and my P=
ostgreSQL host or container is limited to 16GB of RAM, what would happen if=
 I have 100 pooled connections, each gradually allocating those 160MB? Will=
 this memory be reclaimed (if I understood it correctly as a kind of inacti=
ve anon mem), or will the OOM Killer be triggered at some point (because it=
 is real allocated memory)?</div><div><br></div><div>Thank you for your ins=
ights.</div><div><br></div><div>Best regards,=C2=A0</div><div>AlexL</div><d=
iv>Java Dev</div></div></div>

--000000000000bdf4200626d01301--