MIME-Version: 1.0
References: <01ab1d41-3eda-4705-8bbd-af898f5007f1@iki.fi>
 <2981bb36-6bbe-4bdc-9a94-29b1114c79bd@vondra.me>
 <3026ec05-f664-4ebe-8bf6-0a1218b234ec@iki.fi>
 <19945803-6bcc-40fe-a14a-7dc5c462ed80@iki.fi>
 <e07be2ba-856b-4ff5-8313-8b58b6b4e4d0@iki.fi>
 <CAExHW5uWdU1iEM_eVFVVmaHqfjLpq0QrdFUeZjtBDYpNwfuRBg@mail.gmail.com>
 <83e37829-0d94-49b2-ad48-5feb7b5d5e44@iki.fi>
 <CAExHW5uWxs10DBu3f2ZzYm4LfXXSQEYjhqDewDBJqLmeFSkvXA@mail.gmail.com>
 <fe414de5-c62e-4f2e-b547-7e3f595961a3@iki.fi>
In-Reply-To: <fe414de5-c62e-4f2e-b547-7e3f595961a3@iki.fi>
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Date: Fri, 3 Apr 2026 18:33:46 +0530
Message-ID: 
 <CAExHW5uPd=3+CR+GQNUBizBzWBctF_nHzN28zhMZqZmAjGPF8g@mail.gmail.com>
Subject: Re: Shared hash table allocations
To: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: Tomas Vondra <tomas@vondra.me>,
	"pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>,
 Robert Haas <robertmhaas@gmail.com>,
	Rahila Syed <rahilasyed90@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAExHW5uPd%3D3%2BCR%2BGQNUBizBzWBctF_nHzN28zhMZqZmAjGPF8g%40mail.gmail.com>
Precedence: bulk

On Thu, Apr 2, 2026 at 10:15=E2=80=AFPM Heikki Linnakangas <hlinnaka@iki.fi=
> wrote:
>
> On 02/04/2026 19:13, Ashutosh Bapat wrote:
> > On Thu, Apr 2, 2026 at 7:44=E2=80=AFPM Heikki Linnakangas <hlinnaka@iki=
.fi> wrote:
> >>
> >>> I think we should highlight the change in default in the release note=
s
> >>> though. The users which use default configuration will notice an
> >>> increase in the memory. If they are using a custom value, they will
> >>> think of bumping it up. Can we give them some ballpark % by which the=
y
> >>> should increase their max_locks_per_transaction? E.g. double the
> >>> number or something?
> >>
> >> I don't think people who are using the defaults will notice. I'm worri=
ed
> >> about the people who have set max_locks_per_transactions manually, and
> >> now effectively get less lock space for the same setting. Yeah, doubli=
ng
> >> the previous value is a good rule of thumb.
> >
> > Users who have set max_locks_per_transaction to a non-default value
> > but have tuned their application to respect those limits are safe even
> > after this change, since their lock hash table never used wiggle room.
> > Those users who weren't careful to respect those limits will need to
> > bump their setting.
>
> That's technically true, but in practice it's very hard for someone to
> carefully tune the setting. It's difficult to know how many locks a
> particular set of queries take. In practice what people do is they bump
> up the setting if the get the "out of shared memory" error, until the
> error goes away. If you do the tuning that way, it's quite possible that
> you are relying the "wiggle room" without realizing it.
>

That's true.

> > I think the release notes should "nudge" all the
> > users who use non-default max_locks_per_transaction to increase it if
> > they see "out of memory" errors. I don't think it should provide a
> > blanket advise to double their locks
>
> How about:
>
> "If you had previously set max_locks_per_transaction, you might need to
> set it to a higher value in v19 to avoid "out of shared memory" errors.
> If you are unsure what to set it to and don't mind the increased memory
> usage, you can double the value to ensure that you can acquire at least
> as many locks as before"

The wiggle room is 100KB fixed + 10% of other two structures, so value
by which it should be increased is partly fixed and partly a multiple
of current value. "double the value" is simplest advice we can give.
+1.

--=20
Best Wishes,
Ashutosh Bapat