From: Frits Hoogland <frits.hoogland@gmail.com>
Message-Id: <5520EFB7-EDD1-42C1-8141-63EED777E026@gmail.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_A6A8FF39-3F27-42A6-B08A-D8D10F983AB7"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\))
Subject: Re: Safe vm.overcommit_ratio for Large Multi-Instance PostgreSQL
 Fleet
Date: Wed, 6 Aug 2025 10:47:29 +0200
In-Reply-To: <3633df47-5693-4871-9e12-bfbb68dd3313@joeconway.com>
Cc: Priya V <mailme0216@gmail.com>,
 pgsql-performance@lists.postgresql.org
To: Joe Conway <mail@joeconway.com>
References: 
 <CAFsZ43xFxjSiONwRccXBQXZrPRd+Lh7XAkSVEG1ai165xPcoDA@mail.gmail.com>
 <3633df47-5693-4871-9e12-bfbb68dd3313@joeconway.com>
Archived-At: 
 <https://www.postgresql.org/message-id/5520EFB7-EDD1-42C1-8141-63EED777E026%40gmail.com>
Precedence: bulk


--Apple-Mail=_A6A8FF39-3F27-42A6-B08A-D8D10F983AB7
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

Joe,=20

Can you name any technical reason why not having swap for a database is =
an actual bad idea?

Memory always is limited. Swap was invented to overcome a situation =
where the (incidental) memory usage of paged in memory was could =
(regularly) get higher than physical memory would allow, and thus have =
the (clear) workaround of having swap to 'cushion' the memory shortage =
issue by allowing a "second level" memory storage on disk.
Still, this does not making memory unlimited. Swap extends the physical =
memory available with the amount of swap. There still is a situation =
where you can run out of memory when swap is added, simply by paging in =
more memory than physical memory and swap.

Today, most systems are not memory constrained anymore, or: it is =
possible to get a server with enough physical memory to hold your common =
needed total memory need.=20
And given the latency sensitive nature of databases in general, which =
includes postgres, for any serious deployment you should get a server =
with enough memory to host your workload, and configure postgres not to =
overload the memory.

If you do oversubscribe on (physical) memory, you will get pain =
somewhere, there is no way around that.
The article in defense of swap in essence is saying that if you happen =
to oversubscribe on memory, sharing the pain between anonymous and file =
is better.
I would say you are already in a bad place if that happens, which is =
especially bad for databases, and databases should allow you to make =
memory usage predictable.

However, what I found is that with 4+ kernels (4.18 to be precise; rhel =
8), the kernel can try to favour file pages in certain situations making =
anonymous memory getting paged out even if swappiness is set to 1 or 0, =
and if there is a wealth of inactive file memory. It seems to have to do =
with workingset protection(?) mechanisms, but given the lack of clear =
statistics I can't be sure about that. What it does lead to in my =
situations is a constant rate of swapping in and out in certain =
situations, whilst there is no technical reason for linux to do so =
because there is enough available memory.

My point of view has been that vm.overcommit_memory set to 2 was the way =
to go, because that allows linux to limit based on a set limit on =
allocation time, which guarantees way to make the database never run out =
of memory.
it does guarantees linux to never run out of memory, absolutely.
However, this limit is hard, and is applied for the process at both =
usermode and system mode (kernel level), and thus can enforce not =
providing memory at times where it's not safe to do so, and thus corrupt =
execution. I have to be honest, I have not seen this myself, but =
trustworthy sources have reported this repeatedly, which I am inclined =
to believe. This means postgres execution can corrupt/terminate in =
unlucky situations, which is impacts availability.

=20
Frits Hoogland


> On 5 Aug 2025, at 20:52, Joe Conway <mail@joeconway.com> wrote:
>=20
> On 8/5/25 13:01, Priya V wrote:
>> *Environment:*
>>    *PostgreSQL Versions:* Mix of 13.13 and 15.12 (upgrades in =
progress
>>    to be at 15.12 currently both are actively in use)
>=20
> PostgreSQL 13 end of life after November 13, 2025
>=20
>>    *OS / Kernel:* RHEL 7 & RHEL 8 variants, kernels in the =
4.14=E2=80=934.18 range
>=20
> RHEL 7 has been EOL for quite a while now. Note that you have to watch =
out for collation issues/corrupted indexes after OS upgrades due to =
collations changing with newer glibc versions.
>=20
>>    *Swap:* Currently none
>=20
> bad idea
>=20
>>    *Workload:* Highly mixed =E2=80=94 OLTP-style internal apps with
>>    unpredictable query patterns and connection counts
>>    *Goal:* Uniform, safe memory settings across the fleet to avoid
>>    kernel or database instability
>=20
>> We=E2=80=99re considering:
>>    *|vm.overcommit_memory =3D 2|* for strict accounting
>=20
> yes
>=20
>>    Increasing |vm.overcommit_ratio| from 50 =E2=86=92 80 or 90 to =
better
>>    reflect actual PostgreSQL usage (e.g., |work_mem| reservations =
that
>>    aren=E2=80=99t fully used)
>=20
> work_mem does not reserve memory -- it is a maximum that might be used =
in memory for a particular operation
>=20
>> *Our questions for those running large PostgreSQL fleets:*
>> 1.
>>    What |overcommit_ratio| do you find safe for PostgreSQL without
>>    causing kernel memory crunches?
>=20
> Read this:
> =
https://www.cybertec-postgresql.com/en/what-you-should-know-about-linux-me=
mory-overcommit-in-postgresql/
>=20
>> 2.
>>    Do you prefer |overcommit_memory =3D 1| or |=3D 2| for production =
stability?
>=20
> Use overcommit_memory =3D 2 for production stability
>=20
>> 3.
>>    How much swap (if any) do you keep in large-memory servers where
>>    PostgreSQL is the primary workload? Is having swap configured a =
good
>>    idea or not ?
>=20
> You don't necessary need a large amount of swap, but you definitely =
should not disable it.
>=20
> Some background on that:
> https://chrisdown.name/2018/01/02/in-defence-of-swap.html
>=20
>> 4.
>>    Any real-world cases where kernel accounting was too strict or too
>>    loose for PostgreSQL?
>=20
> In my experience the biggest issues are when postgres is running in a =
memory constrained cgroup. If you want to constrain memory with cgroups, =
use cgroup v2 (not 1) and use memory.high to constrain it, not =
memory.max.
>=20
>> 5. What settings to go with if we are not planning on using swap ?
>=20
> IMHO do not disable swap on Linux, at least not on production, ever.
>=20
>> We=E2=80=99d like to avoid both extremes:
>>    Too low a ratio =E2=86=92 PostgreSQL backends failing allocations =
even with
>>    free RAM
>=20
> Have you actually seen this or are you theorizing?
>=20
>>    Too high a ratio =E2=86=92 OOM killer terminating PostgreSQL under =
load spikes
>=20
> If overcommit_memory =3D 2, overcommit_ratio is reasonable (less than =
100, maybe 80 or so as you suggested), and swap is not disabled, and you =
are not running in a memory constrained cgroup, I would be very =
surprised if you will ever get hit by the OOM killer. And if you do, =
things are so bad the database was probably dying anyway.
>=20
> HTH,
>=20
> --=20
> Joe Conway
> PostgreSQL Contributors Team
> Amazon Web Services: https://aws.amazon.com
>=20
>=20


--Apple-Mail=_A6A8FF39-3F27-42A6-B08A-D8D10F983AB7
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"overflow-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: =
after-white-space;">Joe,&nbsp;<div><br></div><div>Can you name any =
technical reason why not having swap for a database is an actual bad =
idea?</div><div><br></div><div>Memory always is limited. Swap was =
invented to overcome a situation where the (incidental) memory usage of =
paged in memory was could (regularly) get higher than physical memory =
would allow, and thus have the (clear) workaround of having swap to =
'cushion' the memory shortage issue by allowing a "second level" memory =
storage on disk.</div><div>Still, this does not making memory unlimited. =
Swap extends the physical memory available with the amount of swap. =
There still is a situation where you can run out of memory when swap is =
added, simply by paging in more memory than physical memory and =
swap.</div><div><br></div><div>Today, most systems are not memory =
constrained anymore, or: it is possible to get a server with enough =
physical memory to hold your common needed total memory =
need.&nbsp;</div><div>And given the latency sensitive nature of =
databases in general, which includes postgres, for any serious =
deployment you should get a server with enough memory to host your =
workload, and configure postgres not to overload the =
memory.</div><div><br></div><div>If you do oversubscribe on (physical) =
memory, you will get pain somewhere, there is no way around =
that.</div><div>The article in defense of swap in essence is saying that =
if you happen to oversubscribe on memory, sharing the pain between =
anonymous and file is better.</div><div>I would say you are already in a =
bad place if that happens, which is especially bad for databases, and =
databases should allow you to make memory usage =
predictable.</div><div><br></div><div>However, what I found is that with =
4+ kernels (4.18 to be precise; rhel 8), the kernel can try to favour =
file pages in certain situations making anonymous memory getting paged =
out even if swappiness is set to 1 or 0, and if there is a wealth of =
inactive file memory. It seems to have to do with workingset =
protection(?) mechanisms, but given the lack of clear statistics I can't =
be sure about that. What it does lead to in my situations is a constant =
rate of swapping in and out in certain situations, whilst there is no =
technical reason for linux to do so because there is enough available =
memory.</div><div><br></div><div>My point of view has been that =
vm.overcommit_memory set to 2 was the way to go, because that allows =
linux to limit based on a set limit on allocation time, which guarantees =
way to make the database never run out of memory.</div><div>it does =
guarantees linux to never run out of memory, =
absolutely.</div><div>However, this limit is hard, and is applied for =
the process at both usermode and system mode (kernel level), and thus =
can enforce not providing memory at times where it's not safe to do so, =
and thus corrupt execution. I have to be honest, I have not seen this =
myself, but trustworthy sources have reported this repeatedly, which I =
am inclined to believe. This means postgres execution can =
corrupt/terminate in unlucky situations, which is impacts =
availability.</div><div><br></div><div>&nbsp;</div><div><div>
<div dir=3D"auto" style=3D"caret-color: rgb(0, 0, 0); color: rgb(0, 0, =
0); letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
-webkit-text-stroke-width: 0px; text-decoration: none; word-wrap: =
break-word; -webkit-nbsp-mode: space; line-break: =
after-white-space;"><div><b>Frits =
Hoogland</b></div><div><br></div></div><br =
class=3D"Apple-interchange-newline"><br =
class=3D"Apple-interchange-newline">

</div>
<div><br><blockquote type=3D"cite"><div>On 5 Aug 2025, at 20:52, Joe =
Conway &lt;mail@joeconway.com&gt; wrote:</div><br =
class=3D"Apple-interchange-newline"><div><div>On 8/5/25 13:01, Priya V =
wrote:<br><blockquote type=3D"cite">*Environment:*<br> =
&nbsp;&nbsp;&nbsp;*PostgreSQL Versions:* Mix of 13.13 and 15.12 =
(upgrades in progress<br> &nbsp;&nbsp;&nbsp;to be at 15.12 currently =
both are actively in use)<br></blockquote><br>PostgreSQL 13 end of life =
after November 13, 2025<br><br><blockquote type=3D"cite"> =
&nbsp;&nbsp;&nbsp;*OS / Kernel:* RHEL 7 &amp; RHEL 8 variants, kernels =
in the 4.14=E2=80=934.18 range<br></blockquote><br>RHEL 7 has been EOL =
for quite a while now. Note that you have to watch out for collation =
issues/corrupted indexes after OS upgrades due to collations changing =
with newer glibc versions.<br><br><blockquote type=3D"cite"> =
&nbsp;&nbsp;&nbsp;*Swap:* Currently none<br></blockquote><br>bad =
idea<br><br><blockquote type=3D"cite"> &nbsp;&nbsp;&nbsp;*Workload:* =
Highly mixed =E2=80=94 OLTP-style internal apps with<br> =
&nbsp;&nbsp;&nbsp;unpredictable query patterns and connection counts<br> =
&nbsp;&nbsp;&nbsp;*Goal:* Uniform, safe memory settings across the fleet =
to avoid<br> &nbsp;&nbsp;&nbsp;kernel or database =
instability<br></blockquote><br><blockquote type=3D"cite">We=E2=80=99re =
considering:<br> &nbsp;&nbsp;&nbsp;*|vm.overcommit_memory =3D 2|* for =
strict accounting<br></blockquote><br>yes<br><br><blockquote =
type=3D"cite"> &nbsp;&nbsp;&nbsp;Increasing |vm.overcommit_ratio| from =
50 =E2=86=92 80 or 90 to better<br> &nbsp;&nbsp;&nbsp;reflect actual =
PostgreSQL usage (e.g., |work_mem| reservations that<br> =
&nbsp;&nbsp;&nbsp;aren=E2=80=99t fully =
used)<br></blockquote><br>work_mem does not reserve memory -- it is a =
maximum that might be used in memory for a particular =
operation<br><br><blockquote type=3D"cite">*Our questions for those =
running large PostgreSQL fleets:*<br> 1.<br> &nbsp;&nbsp;&nbsp;What =
|overcommit_ratio| do you find safe for PostgreSQL without<br> =
&nbsp;&nbsp;&nbsp;causing kernel memory =
crunches?<br></blockquote><br>Read =
this:<br>https://www.cybertec-postgresql.com/en/what-you-should-know-about=
-linux-memory-overcommit-in-postgresql/<br><br><blockquote type=3D"cite"> =
2.<br> &nbsp;&nbsp;&nbsp;Do you prefer |overcommit_memory =3D 1| or |=3D =
2| for production stability?<br></blockquote><br>Use overcommit_memory =3D=
 2 for production stability<br><br><blockquote type=3D"cite"> 3.<br> =
&nbsp;&nbsp;&nbsp;How much swap (if any) do you keep in large-memory =
servers where<br> &nbsp;&nbsp;&nbsp;PostgreSQL is the primary workload? =
Is having swap configured a good<br> &nbsp;&nbsp;&nbsp;idea or not =
?<br></blockquote><br>You don't necessary need a large amount of swap, =
but you definitely should not disable it.<br><br>Some background on =
that:<br>https://chrisdown.name/2018/01/02/in-defence-of-swap.html<br><br>=
<blockquote type=3D"cite"> 4.<br> &nbsp;&nbsp;&nbsp;Any real-world cases =
where kernel accounting was too strict or too<br> =
&nbsp;&nbsp;&nbsp;loose for PostgreSQL?<br></blockquote><br>In my =
experience the biggest issues are when postgres is running in a memory =
constrained cgroup. If you want to constrain memory with cgroups, use =
cgroup v2 (not 1) and use memory.high to constrain it, not =
memory.max.<br><br><blockquote type=3D"cite"> 5. What settings to go =
with if we are not planning on using swap ?<br></blockquote><br>IMHO do =
not disable swap on Linux, at least not on production, =
ever.<br><br><blockquote type=3D"cite">We=E2=80=99d like to avoid both =
extremes:<br> &nbsp;&nbsp;&nbsp;Too low a ratio =E2=86=92 PostgreSQL =
backends failing allocations even with<br> &nbsp;&nbsp;&nbsp;free =
RAM<br></blockquote><br>Have you actually seen this or are you =
theorizing?<br><br><blockquote type=3D"cite"> &nbsp;&nbsp;&nbsp;Too high =
a ratio =E2=86=92 OOM killer terminating PostgreSQL under load =
spikes<br></blockquote><br>If overcommit_memory =3D 2, overcommit_ratio =
is reasonable (less than 100, maybe 80 or so as you suggested), and swap =
is not disabled, and you are not running in a memory constrained cgroup, =
I would be very surprised if you will ever get hit by the OOM killer. =
And if you do, things are so bad the database was probably dying =
anyway.<br><br>HTH,<br><br>-- <br>Joe Conway<br>PostgreSQL Contributors =
Team<br>Amazon Web Services: =
https://aws.amazon.com<br><br><br></div></div></blockquote></div><br></div=
></body></html>=

--Apple-Mail=_A6A8FF39-3F27-42A6-B08A-D8D10F983AB7--