MIME-Version: 1.0
References: 
 <hS-XH0n2SemZiCabUk02t2mKBBLxiFFF-iA7u_RIp8HuTPrFt1T3J_ljcMcYV7syotOSkKM7zV9jwTVcnkz356oltoxkLJjXvMIObTMP254=@protonmail.ch>
In-Reply-To: 
 <hS-XH0n2SemZiCabUk02t2mKBBLxiFFF-iA7u_RIp8HuTPrFt1T3J_ljcMcYV7syotOSkKM7zV9jwTVcnkz356oltoxkLJjXvMIObTMP254=@protonmail.ch>
From: Beniamin Hendre <benysofy7@gmail.com>
Date: Wed, 28 Jan 2026 22:21:00 +0200
Message-ID: 
 <CAOST94jEPPCk4BhrDYSrnB80e-uq2EdY5HJ7oxypk31VYAUXRA@mail.gmail.com>
Subject: Re: Maximum amount of pg_locks
To: Tim Herren <tim.herren@protonmail.ch>
Cc: pgsql-admin@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000038a0410649787c67"
Archived-At: 
 <https://www.postgresql.org/message-id/CAOST94jEPPCk4BhrDYSrnB80e-uq2EdY5HJ7oxypk31VYAUXRA%40mail.gmail.com>
Precedence: bulk

--00000000000038a0410649787c67
Content-Type: text/plain; charset="UTF-8"

Actualisation script maximo plus??

mie., 28 ian. 2026, 22:12 Tim Herren <tim.herren@protonmail.ch> a scris:

> Hi
>
> 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 are
> 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 respectively gives
> 92160.
>
> Yet I regularly observe around 119k locks during my backup using "pg_dump
> -Fc".
>
> postgres=# SELECT
>     locktype,
>     mode,
>     count(*)
> FROM pg_locks
> GROUP BY 1, 2
> ORDER BY 3 DESC;
>   locktype  |      mode       | count
> ------------+-----------------+--------
>  relation   | AccessShareLock | 119948
>  virtualxid | ExclusiveLock   |      2
> (2 rows)
>
> The database I'm backing up at that time contains 242 schemas and each has
> around 500 relations (a mix of tables and sequences).
> So that matches pretty well.
>
> I've verified that my configured setting is enough in the sense that I get
> a usable backup and my database remains operational during the backup
> period. Non the less I would like to understand where that difference comes
> from and how close to the "actual limit" I am.
> Please let me know if this needs more information.
>
> Thanks
>

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

<div dir=3D"auto">Actualisation script maximo plus??</div><br><div class=3D=
"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">m=
ie., 28 ian. 2026, 22:12 Tim Herren &lt;<a href=3D"mailto:tim.herren@proton=
mail.ch">tim.herren@protonmail.ch</a>&gt; a scris:<br></div><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;pa=
dding-left:1ex"><div style=3D"font-family:Arial,sans-serif;font-size:14px">=
<span>Hi</span><div><br></div><div><span>I&#39;m trying to wrap my head aro=
und the way the calculation for the maximum amount of locks works in postgr=
es 16.11</span></div><div><span>I already came to the understanding that th=
e maximum amount of locks are not on a transaction basis, but rather influe=
nced by the setting &quot;max_locks_per_transaction&quot; and the &quot;max=
_connections&quot;.</span></div><div><span>I&#39;m saying influenced rather=
 than calculated because on my server a simple multiplication of those two =
values, set at 512 and 180 respectively gives 92160.</span></div><div><br><=
/div><div><span>Yet I regularly observe around 119k locks during my backup =
using &quot;pg_dump -Fc&quot;.</span></div><div><br></div><div><span>postgr=
es=3D# SELECT </span></div><div><span>=C2=A0 =C2=A0 locktype, </span></div>=
<div><span>=C2=A0 =C2=A0 mode, </span></div><div><span>=C2=A0 =C2=A0 count(=
*) </span></div><div><span>FROM pg_locks </span></div><div><span>GROUP BY 1=
, 2 </span></div><div><span>ORDER BY 3 DESC;</span></div><div><span>=C2=A0 =
locktype =C2=A0| =C2=A0 =C2=A0 =C2=A0mode =C2=A0 =C2=A0 =C2=A0 | count =C2=
=A0</span></div><div><span>------------+-----------------+--------</span></=
div><div><span>=C2=A0relation =C2=A0 | AccessShareLock | 119948</span></div=
><div><span>=C2=A0virtualxid | ExclusiveLock =C2=A0 | =C2=A0 =C2=A0 =C2=A02=
</span></div><div><span>(2 rows)</span></div><div><br></div><div><span>The =
database I&#39;m backing up at that time contains 242 schemas and each has =
around 500 relations (a mix of tables and sequences).</span></div><div><spa=
n>So that matches pretty well.</span></div><div><br></div><div><span>I&#39;=
ve verified that my configured setting is enough in the sense that I get a =
usable backup and my database remains operational during the backup period.=
 Non the less I would like to understand where that difference comes from a=
nd how close to the &quot;actual limit&quot; I am.</span></div><div><span>P=
lease let me know if this needs more information.</span></div><div><br></di=
v><span>Thanks</span><br></div>
<div style=3D"font-family:Arial,sans-serif;font-size:14px">
    <div>
       =20
            </div>
   =20
            <div>
       =20
            </div>
</div>
</blockquote></div>

--00000000000038a0410649787c67--