MIME-Version: 1.0
References: 
 <CAK+uD7g=EE4ypAsuF8L9ZQ3FSuRD-YJ8nOkkyDnSooA_jidycQ@mail.gmail.com>
In-Reply-To: 
 <CAK+uD7g=EE4ypAsuF8L9ZQ3FSuRD-YJ8nOkkyDnSooA_jidycQ@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 30 Jun 2026 12:47:11 -0400
Message-ID: 
 <CANzqJaAO05XN2PLrf3t1TzvR9Gp4f4nDUCjdOxEWkSkJLy9jaw@mail.gmail.com>
Subject: Re: Guidance on Calculating max_locks_per_transaction for a Highly
 Partitioned Environment
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000004c8be106557b54d8"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaAO05XN2PLrf3t1TzvR9Gp4f4nDUCjdOxEWkSkJLy9jaw%40mail.gmail.com>
Precedence: bulk

--0000000000004c8be106557b54d8
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Jun 30, 2026 at 9:26=E2=80=AFAM Vivek Gadge <vvkgadge56@gmail.com> =
wrote:

> Hello Team,
>
> I am looking for guidance on determining an appropriate value for the
> max_locks_per_transaction parameter in our PostgreSQL 17.6 production
> environment.
>
> Environment has the following characteristics:
>
>     PostgreSQL Version: 17.6
>     max_connections =3D 1500
>

That's HIGH.  If you have that many concurrent connections, then you really
need a connection pooler.


>     Is there a recommended methodology or formula for estimating
> max_locks_per_transaction.
>
> Our objective is to determine an appropriate value based on workload
> characteristics rather than increasing the parameter through trial and
> error.
>

The memory structures handling locks per transaction are really light.  I
set the value to 15000 (where average connection count is around 30) and
then worry about something else.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr"><span style=3D"background-color:transpare=
nt">On Tue, Jun 30, 2026 at 9:26=E2=80=AFAM Vivek Gadge &lt;<a href=3D"mail=
to:vvkgadge56@gmail.com">vvkgadge56@gmail.com</a>&gt; wrote:</span></div><d=
iv class=3D"gmail_quote gmail_quote_container"><blockquote class=3D"gmail_q=
uote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,2=
04);padding-left:1ex"><div dir=3D"auto">Hello Team,<div dir=3D"auto"><br></=
div><div dir=3D"auto">I am looking for guidance on determining an appropria=
te value for the max_locks_per_transaction parameter in our PostgreSQL 17.6=
 production environment.</div><div dir=3D"auto"><br></div><div dir=3D"auto"=
>Environment has the following characteristics:</div><div dir=3D"auto"><br>=
</div><div dir=3D"auto">=C2=A0 =C2=A0 PostgreSQL Version: 17.6</div><div di=
r=3D"auto">=C2=A0 =C2=A0 max_connections =3D 1500</div></div></blockquote><=
div><br></div><div>That&#39;s HIGH.=C2=A0 If you have that many concurrent =
connections, then you really need a connection pooler.</div><div><span styl=
e=3D"background-color:transparent">=C2=A0</span></div><blockquote class=3D"=
gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(20=
4,204,204);padding-left:1ex"><div dir=3D"auto"><div dir=3D"auto">=C2=A0 =C2=
=A0 Is there a recommended methodology or formula for estimating max_locks_=
per_transaction.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Our obj=
ective is to determine an appropriate value based on workload characteristi=
cs rather than increasing the parameter through trial and error.</div></div=
></blockquote><div>=C2=A0</div></div><div>The memory structures handling lo=
cks per transaction are really light.=C2=A0 I set the value to 15000 (where=
 average connection count is around 30) and then worry about something else=
.</div><div><br></div><span class=3D"gmail_signature_prefix">-- </span><br>=
<div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Re=
dacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<b=
r><div><div>&lt;Redacted&gt; lobster!</div></div></div></div></div></div>

--0000000000004c8be106557b54d8--