MIME-Version: 1.0
References: 
 <hS-XH0n2SemZiCabUk02t2mKBBLxiFFF-iA7u_RIp8HuTPrFt1T3J_ljcMcYV7syotOSkKM7zV9jwTVcnkz356oltoxkLJjXvMIObTMP254=@protonmail.ch>
 <854468.1769633854@sss.pgh.pa.us>
In-Reply-To: <854468.1769633854@sss.pgh.pa.us>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 28 Jan 2026 16:50:32 -0500
Message-ID: 
 <CANzqJaD2ahSpQK6TGdOKyQmh_zBtw6pp_A5Kvik1805-Q4ECTA@mail.gmail.com>
Subject: Re: Maximum amount of pg_locks
To: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000006b1db1064979bb3c"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaD2ahSpQK6TGdOKyQmh_zBtw6pp_A5Kvik1805-Q4ECTA%40mail.gmail.com>
Precedence: bulk

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

On Wed, Jan 28, 2026 at 3:57=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Tim Herren <tim.herren@protonmail.ch> writes:
> > I'm trying to wrap my head around the way the calculation for the
> maximum amount of locks works in postgres 16.11
> > I already came to the understanding that the maximum amount of locks ar=
e
> not on a transaction basis, but rather influenced by the setting
> "max_locks_per_transaction" and the "max_connections".
> > I'm saying influenced rather than calculated because on my server a
> simple multiplication of those two values, set at 512 and 180 respectivel=
y
> gives 92160.
> > Yet I regularly observe around 119k locks during my backup using
> "pg_dump -Fc".
>
> Well, the hash table size is indeed set by a calculation of that form,
> but it's max_locks_per_transaction times the number of potential
> locker processes --- not only regular sessions (max_connections),
> but also autovacuum processes and other background workers.  And
> prepared transactions, too.  lock.c has
>
> #define NLOCKENTS() \
>         mul_size(max_locks_per_xact, add_size(MaxBackends,
> max_prepared_xacts))
>
> where postinit.c calculates MaxBackends as:
>
>         /* Note that this does not include "auxiliary" processes */
>         MaxBackends =3D MaxConnections + autovacuum_worker_slots +
>                 max_worker_processes + max_wal_senders +
> NUM_SPECIAL_WORKER_PROCS;
>
> Then on top of that, there's a pretty considerable fudge factor:
> lock.c scales up its shared-memory size request by 10%, and there
> is (from memory) about 100K slop space added on top of the total of
> shared-memory size requests from all modules, and all of that space
> is potentially available for the lock table to overflow into.  (Other
> shared data structures could claim it too, but I think the lock table
> is the only one that we don't have a hard upper bound for.)
>
> If you really want to know how many locks can be taken in your
> particular setup, I'd counsel experimenting by deliberately exhausting
> the lock space.  (Maybe not during production hours.)  The precise
> limit will vary across PG versions, and potentially depend on other
> factors like what extensions you have loaded.
>

I asked a similar question a few years ago, and you said that on modern
systems it's no problem to bump max_locks_per_transaction pretty darned
high (like 1 million).   "The default, 64, has historically proven
sufficient" had me slowly incrementing, but after your comment *I added a
zero* to my existing max_locks_per_transaction value (1536, one and a half
KiB) *and haven't thought about it since*.

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

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

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Jan 28, 2026 at 3:57=E2=80=AFPM T=
om Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; =
wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex">Tim Herren &lt;<a href=3D"mailto:tim.he=
rren@protonmail.ch" target=3D"_blank">tim.herren@protonmail.ch</a>&gt; writ=
es:<br>
&gt; I&#39;m trying to wrap my head around the way the calculation for the =
maximum amount of locks works in postgres 16.11<br>
&gt; I already came to the understanding that the maximum amount of locks a=
re not on a transaction basis, but rather influenced by the setting &quot;m=
ax_locks_per_transaction&quot; and the &quot;max_connections&quot;.<br>
&gt; I&#39;m saying influenced rather than calculated because on my server =
a simple multiplication of those two values, set at 512 and 180 respectivel=
y gives 92160.<br>
&gt; Yet I regularly observe around 119k locks during my backup using &quot=
;pg_dump -Fc&quot;.<br>
<br>
Well, the hash table size is indeed set by a calculation of that form,<br>
but it&#39;s max_locks_per_transaction times the number of potential<br>
locker processes --- not only regular sessions (max_connections),<br>
but also autovacuum processes and other background workers.=C2=A0 And<br>
prepared transactions, too.=C2=A0 lock.c has<br>
<br>
#define NLOCKENTS() \<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 mul_size(max_locks_per_xact, add_size(MaxBacken=
ds, max_prepared_xacts))<br>
<br>
where postinit.c calculates MaxBackends as:<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 /* Note that this does not include &quot;auxili=
ary&quot; processes */<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 MaxBackends =3D MaxConnections + autovacuum_wor=
ker_slots +<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 max_worker_processe=
s + max_wal_senders + NUM_SPECIAL_WORKER_PROCS;<br>
<br>
Then on top of that, there&#39;s a pretty considerable fudge factor:<br>
lock.c scales up its shared-memory size request by 10%, and there<br>
is (from memory) about 100K slop space added on top of the total of<br>
shared-memory size requests from all modules, and all of that space<br>
is potentially available for the lock table to overflow into.=C2=A0 (Other<=
br>
shared data structures could claim it too, but I think the lock table<br>
is the only one that we don&#39;t have a hard upper bound for.)<br>
<br>
If you really want to know how many locks can be taken in your<br>
particular setup, I&#39;d counsel experimenting by deliberately exhausting<=
br>
the lock space.=C2=A0 (Maybe not during production hours.)=C2=A0 The precis=
e<br>
limit will vary across PG versions, and potentially depend on other<br>
factors like what extensions you have loaded.<br></blockquote><div><br></di=
v><div>I asked a similar question a few years ago, and you said that on mod=
ern systems it&#39;s no problem to bump max_locks_per_transaction pretty da=
rned high (like 1 million).=C2=A0 =C2=A0&quot;The default, 64, has historic=
ally proven sufficient&quot; had me slowly incrementing, but after your com=
ment <b>I added a zero</b> to my existing max_locks_per_transaction value (=
1536, one and a half KiB) <b>and haven&#39;t thought about it since</b>.</d=
iv></div><div><br></div><span class=3D"gmail_signature_prefix">-- </span><b=
r><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;=
Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.=
<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div></div></div>

--0000000000006b1db1064979bb3c--