Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3818.100.11.1.3\))
Subject: Re: Suggestion for memory parameters
From: Philip Semanchuk <philip@americanefficient.com>
In-Reply-To: <CAEzWdqfUQuKtpqGAwf86dwkjPq2Kkeyj6Pw31GXr92YC8M2Y5g@mail.gmail.com>
Date: Mon, 30 Sep 2024 11:16:20 -0400
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <A57336D3-37A0-413F-8D74-B41607359DC6@americanefficient.com>
References: <CAEzWdqfUQuKtpqGAwf86dwkjPq2Kkeyj6Pw31GXr92YC8M2Y5g@mail.gmail.com>
To: yudhi s <learnerdatabase99@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/A57336D3-37A0-413F-8D74-B41607359DC6%40americanefficient.com>
Precedence: bulk


> On Sep 26, 2024, at 7:03=E2=80=AFAM, yudhi s =
<learnerdatabase99@gmail.com> wrote:
>=20
> In a RDS postgres ...

>  Is it fine to let it use "FreeLocalStorage" unless it goes till zero?

Hi Yudhi,
FreeLocalStorage and some of the other things you ask about are specific =
to AWS RDS, so you might have better luck getting answers on an =
RDS-specific mailing list. We also use RDS-hosted Postgres and so I =
completely understand how Postgres and RDS are intertwined.

We have had runaway queries exhaust FreeLocalStorage. It has been quite =
a while since that happened, so my memories are hazy, but I=E2=80=99m =
pretty sure that when we used all of FreeLocalStorage, the result was =
that Postgres restarted. It might be equivalent to using all memory and =
disk space on a standalone system. Once there=E2=80=99s no storage left, =
behavior is unpredictable but we can=E2=80=99t be surprised if things =
crash. Usually our runaway queries got killed before FreeLocalStorage =
filled up, but not always.

I second Veem=E2=80=99s suggestion to set work_mem on a per-session =
basis. Also note that the doc for work_mem says, =E2=80=9Cthe total =
memory used could be many times the value of work_mem; it is necessary =
to keep this fact in mind when choosing the value."

=
https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-W=
ORK-MEM

Cheers
Philip=