Feedback-ID: i0fe9450f:Fastmail
Date: Fri, 13 Feb 2026 16:36:57 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Cc: Jeff Davis <pgsql@j-davis.com>,
	Greg Sabino Mullane <htamfids@gmail.com>,
	pgsql-hackers@lists.postgresql.org
Subject: Re: Adding locks statistics
Message-ID: <aY7UmTIiBScP4rAq@paquier.xyz>
References: <aIyNxBWFCybgBZBS@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAKAnmm+USTr5MeKuyrAvbCjHqbSFbZ+czJsV7fPYt9nECQkf-A@mail.gmail.com>
 <87c3170d0645cec732f0d7b2969c75db1b3c86c6.camel@j-davis.com>
 <aJqAei83uRWV93bM@paquier.xyz>
 <aJsLc+S62xAbjncC@ip-10-97-1-34.eu-west-3.compute.internal>
 <1a236172c7dda72939e4293657a90536cce7dd16.camel@j-davis.com>
 <aJwMi6qWKNc/ygg9@ip-10-97-1-34.eu-west-3.compute.internal>
 <aUA7ymkrh/UpkJ6O@ip-10-97-1-34.eu-west-3.compute.internal>
 <aYreqqn3meYqPm1A@ip-10-97-1-34.eu-west-3.compute.internal>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="Uf9F6Dk18ouFjoBO"
Content-Disposition: inline
In-Reply-To: <aYreqqn3meYqPm1A@ip-10-97-1-34.eu-west-3.compute.internal>
Archived-At: <https://www.postgresql.org/message-id/aY7UmTIiBScP4rAq%40paquier.xyz>
Precedence: bulk


--Uf9F6Dk18ouFjoBO
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Tue, Feb 10, 2026 at 07:30:50AM +0000, Bertrand Drouvot wrote:
> New rebase due to 73d60ac385a.

I have been looking at this patch, and can get behind the data
gathered here in terms of being able to tune things, but not all.  See
below for the details of my reasoning.

max_locks_per_xact is a PGC_POSTMASTER, so backend-level stats with
this data would not be relevant for its tuning.  However, something
else can be said about deadlock_timeout, where one could rely on the
data gathered by this view to set it on a backend basis, particularly
if the load pattern is divided into a subsets of connections (say few
backend see a lot of the deadlock_timeout, for example).  Same
argument for lock_timeout, which is user-settable.

As the set of data gathered, I think that I'm OK with timeouts (for
lock_timeout), deadlock_timeouts (for deadlock_timeout), fastpath (for
max_locks_per_xact), that can all be compared with the number of
requests.

Regarding "deadlocks" and "waits", these two are less useful than the
three others because not really actionable.  They would become much
more relevant if and only if we know the distribution of the deadlocks
not only for the lock types, but for the objects involved, especially
if the activity is diluted across many objects.  So these have less
value IMO because they are not really actionable in the system.  The
other three can be directly tuned based on the GUCs we have.

So my suggestion for the moment would be to be more frugal (yeah I
know, sorry..) and limit ourselves to four fields: deadlock_timeout,
requests, fastpath and timeouts.  Three fields to compare with
requests, one for each GUC.

Regarding the implementation, you are right to use a fixed-sized stats
kind for the job.  I can see a lot of code has been copy-pasted from
pgstat_io.c, then slightly adjusted to fit into the picture.  That's
fine here, it makes the implementation straight-forward to read.

Regarding the documentation, listing all the values for locktype is a
recipe for rot.  I'd suggest to remove the list instead, with only a
link referring to pg_locks to avoid the duplication.
--
Michael

--Uf9F6Dk18ouFjoBO
Content-Type: application/pgp-signature; name=signature.asc

-----BEGIN PGP SIGNATURE-----

iQIzBAEBCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmmO1JkACgkQnvQgOdby
QH2tfQ//b52gcCQLSxDkBTDqVZ0ut29GkvnLEZYbDQyxWhefMClxF0LuCpUnZ39R
w/9TuudY5bew/GXFiND9ijqpcdruXWl2Z3YsJwPLQiIYc7Udfz5UpnLrfAkb5DcL
NixNTfYDRRC3G9r9PvX8PswkID80TVbqgj66gE1cUb/U4tdtGMyYKrXnrd1u8kFQ
QmiBmHdCo1NaZrC8ZAxQVAti6N8KwEXAPbPyY6xUsFJOL0SzdIgf5X5t7lyEw/3j
mp7dd2ib4iSkwcQthZiHQdtU5XSlH9BRo9+ASwKRPJUEw6D3eRzMil2lqAiNs2Xf
i5lv07buzb/twDyQ0V0K3fOyK1mxT4lzsjsbEkqXC3AlzzZ3gEoCKml/LCrZuZSg
qZe4eCiYur3LeJTA4xFKrCu9BflDiHBa7QK1+S2FOTeOtwV1CKD/+R47WIANQEQN
WE/RezrahIarpLu7jDiXfBHLJ251G8IfmrONdTIuNilx69gFoOdBfnYvAchY2vDF
YNpOn0ZkNPFkBvmVuUXepfzfLgZ6RkMlEdX0Skh5U5n3UpCsHGLAG6dK0PwAfLQk
45zG4qhN1b0ETai1Az8GQBw4tibDFal0XBtXg2EkrQKNkYnnEeTjA/BKYkDbsj61
1FWunAUIbbPH9EkP129va1jolkOPIUQLRoUOv1geoHYxGp/EHiI=
=ydx3
-----END PGP SIGNATURE-----

--Uf9F6Dk18ouFjoBO--