MIME-Version: 1.0
From: Vivek Gadge <vvkgadge56@gmail.com>
Date: Mon, 29 Jun 2026 23:25:59 +0530
Message-ID: 
 <CAK+uD7g=EE4ypAsuF8L9ZQ3FSuRD-YJ8nOkkyDnSooA_jidycQ@mail.gmail.com>
Subject: Guidance on Calculating max_locks_per_transaction for a Highly
 Partitioned Environment
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000096bf540655682cf9"
Archived-At: 
 <https://www.postgresql.org/message-id/CAK%2BuD7g%3DEE4ypAsuF8L9ZQ3FSuRD-YJ8nOkkyDnSooA_jidycQ%40mail.gmail.com>
Precedence: bulk

--00000000000096bf540655682cf9
Content-Type: text/plain; charset="UTF-8"

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 = 1500
    Daily partitioning strategy
    Large OLTP workload with transactions that may access multiple
partitions

Occasionally, we encounter the following error:

ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.

We understand that the shared lock table is sized based on
max_locks_per_transaction, max_connections, and max_prepared_transactions.
However, we have not found any guidance on how to estimate an appropriate
value for environments with a large number of partitions and indexes.

We would appreciate guidance on the following:

    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.

Thanks

Vivek Gadge.

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

<div dir=3D"auto">Hello Team,<div dir=3D"auto"><br></div><div dir=3D"auto">=
I am looking for guidance on determining an appropriate value for the max_l=
ocks_per_transaction parameter in our PostgreSQL 17.6 production environmen=
t.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Environment has the f=
ollowing characteristics:</div><div dir=3D"auto"><br></div><div dir=3D"auto=
">=C2=A0 =C2=A0 PostgreSQL Version: 17.6</div><div dir=3D"auto">=C2=A0 =C2=
=A0 max_connections =3D 1500</div><div dir=3D"auto">=C2=A0 =C2=A0 Daily par=
titioning strategy</div><div dir=3D"auto">=C2=A0 =C2=A0 Large OLTP workload=
 with transactions that may access multiple partitions=C2=A0</div><div dir=
=3D"auto"><br></div><div dir=3D"auto">Occasionally, we encounter the follow=
ing error:</div><div dir=3D"auto"><br></div><div dir=3D"auto">ERROR: out of=
 shared memory</div><div dir=3D"auto">HINT: You might need to increase max_=
locks_per_transaction.</div><div dir=3D"auto"><br></div><div dir=3D"auto">W=
e understand that the shared lock table is sized based on max_locks_per_tra=
nsaction, max_connections, and max_prepared_transactions. However, we have =
not found any guidance on how to estimate an appropriate value for environm=
ents with a large number of partitions and indexes.</div><div dir=3D"auto">=
<br></div><div dir=3D"auto">We would appreciate guidance on the following:<=
/div><div dir=3D"auto"><br></div><div dir=3D"auto">=C2=A0 =C2=A0 Is there a=
 recommended methodology or formula for estimating max_locks_per_transactio=
n.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Our objective is to d=
etermine an appropriate value based on workload characteristics rather than=
 increasing the parameter through trial and error.</div><div dir=3D"auto"><=
br></div><div dir=3D"auto">Thanks=C2=A0</div><div dir=3D"auto"><br></div><d=
iv dir=3D"auto">Vivek Gadge.</div><div dir=3D"auto"><br></div></div>

--00000000000096bf540655682cf9--