Feedback-ID: idd01497b:Fastmail
Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=UTF-8
Date: Thu, 04 Jun 2026 21:32:39 +0000
Message-Id: <DJ0L4JF9W2J7.2B0DAKKQYA4DF@partin.io>
Cc: "pgsql-hackers" <pgsql-hackers@lists.postgresql.org>
Subject: Re: Add per-backend lock statistics
To: "Bertrand Drouvot" <bertranddrouvot.pg@gmail.com>
From: "Tristan Partin" <tristan@partin.io>
References: <aiAzEY+cMQb/W8yu@bdtpg>
In-Reply-To: <aiAzEY+cMQb/W8yu@bdtpg>
Archived-At: 
 <https://www.postgresql.org/message-id/DJ0L4JF9W2J7.2B0DAKKQYA4DF%40partin.io>
Precedence: bulk

On Wed Jun 3, 2026 at 1:59 PM UTC, Bertrand Drouvot wrote:
> Hi hackers,
>
> Now that we have global lock statistics since 4019f725f5d, it could be us=
eful
> to have the same kind of information on a per-backend basis.
>
> Indeed, pg_stat_lock gives us cluster-wide aggregates: total waits, total=
 wait
> time, total fast-path exceeded across all backends since last reset.
>
> When we see high numbers, we can't answer:
>
> - Which backend is affected the most?
> - Is it one backend affected or many?
> - Is a specific application or connection pool suffering?
> - After a specific workload/application is improved, did its lock behavio=
r
> improve?
>
> With per-backend lock stats, we could:
>
> 1/ Isolate problematic sessions. We can correlate locks behavior with spe=
cific
> PIDs visible in pg_stat_activity: identify the exact application_name or =
user
> experiencing lock waits.
>
> 2/ Debug live contention. During an incident, we could pinpoint which bac=
kends
> are experiencing fast-path exhaustion or lock waits without having to res=
et
> global stats and lose history.
>
> 3/ Define workload characterization. Different backend types may have ver=
y
> different lock profiles. Per-backend stats would let us see this directly=
.
>
> 4/ Compare before/after per session. We could measure a single backend's =
lock
> behavior across a specific operation, which is impossible with global cou=
nters
> that include metrics from all other backends.
>
> IO and WAL stats already have per-backend counterparts (pg_stat_get_backe=
nd_io(),
> pg_stat_get_backend_wal()). Lock stats are the same class of operational =
data:
> having them only at the global level is an inconsistency that limits obse=
rvability.

The motivation makes sense to me.

> As far the technical implementation:
>
> This data can be retrieved with a new system function called
> pg_stat_get_backend_lock(), that returns one tuple per lock type based on=
 the PID
> provided in input.
>
> pgstat_flush_backend() gains a new flag value, able to control the flush =
of the
> lock stats.
>
> This patch relies mostly on the infrastructure provided by 9aea73fc61d4, =
that
> has introduced backend statistics.
>
> The overhead (2 functions calls and counters increments) on the hot path =
(normal
> lock acquisition) is zero: counters are only incremented on paths that ar=
e already
> "slow" (post deadlock timeout waits, fast-path slot exhaustion) and does =
not add
> that much memory per-backend: PgStat_PendingLock is 288 bytes.
>
> The patch is made of 2 sub-patches:
>
> 0001: Refactor pg_stat_get_lock() to use a helper function

> +static void
> +pg_stat_lock_build_tuples(ReturnSetInfo *rsinfo,
> +                         PgStat_LockEntry *lock_stats,
> +                         TimestampTz stat_reset_timestamp)

I think that the alignment of the second and third arguments could be=20
off by one. They should line up with the capital R in ReturnSetInfo.

> -       values[i] =3D TimestampTzGetDatum(lock_stats->stat_reset_timestam=
p);
> +       if (stat_reset_timestamp !=3D 0)
> +           values[i] =3D TimestampTzGetDatum(stat_reset_timestamp);
> +       else
> +           nulls[i] =3D true;

It's not super clear to me why this changed in the first patch. Perhaps=20
it is meant to be in the second patch? I see in the second patch that we=20
use the stat_reset_timestamp from the backend stats instead of the lock=20
stats in pg_stat_get_backend_lock(). The motivation makes sense. It=20
might be cleaner to move the change into patch 2.

> 0002: Add per-backend lock statistics

> +        Returns lock statistics about the backend with the specified
> +        process ID. The output fields are exactly the same as the ones i=
n the
> +        <structname>pg_stat_lock</structname> view.

It probably makes sense to link to pg_stat_lock here.

Other than the few comments I had, this patchset looks good. It follows=20
patterns that were already established with the per-backend IO and WAL=20
stats.

--=20
Tristan Partin
PostgreSQL Contributors Team
AWS (https://aws.amazon.com)