Message-ID: <cec733623111083dbd4d35ddc3950be9c02968cf.camel@cybertec.at>
Subject: Re: work_mem RAM Accounting in PostgreSQL
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Alexandru Lazarev <alexandru.lazarev@gmail.com>, 
	pgsql-general@lists.postgresql.org
Date: Thu, 14 Nov 2024 15:40:00 +0100
In-Reply-To: <CAL93h0GtZ5H+X+8CRDOkcN1w0Bha2w3-57CqKSNFC2xrTNhEhA@mail.gmail.com>
References: 
	<CAL93h0GtZ5H+X+8CRDOkcN1w0Bha2w3-57CqKSNFC2xrTNhEhA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-2.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/cec733623111083dbd4d35ddc3950be9c02968cf.camel%40cybertec.at>
Precedence: bulk

On Wed, 2024-11-13 at 21:09 +0200, Alexandru Lazarev wrote:
> I have some questions regarding how the "work_mem" parameter affects the =
overall RAM
> usage of PostgreSQL processes within a physical host or container.
>=20
> Each backend process during SQL execution may allocate N * "work_mem" sim=
ultaneously.
> For example, if "work_mem" 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 process to use 160MB (32MB * =
5) + 10MB,
> resulting in a total RAM usage of 170MB.

The limit for a hash is hash_mem_multiplier * work_mem.

> My questions are as follows:
>=20
> 1. What happens to the allocated "work_mem" after the execution of query =
nodes? Are
>    these memory allocations freed?

Yes.

> 2. If they are freed, do they remain in the RSS of the PostgreSQL backend=
?

They may, because the C library can choose not to actually free all the mem=
ory,
but retain some to serve future malloc() requests more efficiently.

> 3. From various sources, I understand that these allocations are freed af=
ter
>    each node execution due to memory contexts, but they might remain in s=
ome sort
>    of backend memory pool for future reuse. Is this correct?

I am not sure what you mean, but perhaps what I wrote above.

> 4. If so, will this memory be accounted for as used RAM on my Linux/Conta=
iner
>    system after the backend returns to an idle state (e.g., connection po=
oling)?

Certainly.

> Additionally: If the above is true, and my PostgreSQL host or container i=
s 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 s=
ome point
> (because it is real allocated memory)?

The backends won't keep that much memory allocated, so you need not worry.

100 connections are a lot.  With efficient pooling, you could have fewer co=
nnections
and use your resources more efficiently.

Yours,
Laurenz Albe