Hi hackers, Now that we have global lock statistics since 4019f725f5d, it could be useful 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 behavior improve? With per-backend lock stats, we could: 1/ Isolate problematic sessions. We can correlate locks behavior with specific 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 backends are experiencing fast-path exhaustion or lock waits without having to reset global stats and lose history. 3/ Define workload characterization. Different backend types may have very 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 counters that include metrics from all other backends. IO and WAL stats already have per-backend counterparts (pg_stat_get_backend_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 observability. 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 are 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 Extract the tuple-building logic from pg_stat_get_lock() into a new static helper pg_stat_lock_build_tuples(). This is in preparation for pg_stat_get_backend_lock() which will reuse the same helper, following the pattern established by pg_stat_io_build_tuples() for IO stats and pg_stat_wal_build_tuple() for WAL stats. 0002: Add per-backend lock statistics As discussed above. Looking forward to your feedback, Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
>From c00ae2fb9022b80bf2262afe4bc23e3255d02809 Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Wed, 3 Jun 2026 13:04:26 +0000 Subject: [PATCH v1 1/2] Refactor pg_stat_get_lock() to use a helper function Extract the tuple-building logic from pg_stat_get_lock() into a new static helper pg_stat_lock_build_tuples(). This is in preparation for pg_stat_get_backend_lock() which will reuse the same helper, following the pattern established by pg_stat_io_build_tuples() for IO stats and pg_stat_wal_build_tuple() for WAL stats. Author: Bertrand Drouvot <[email protected]> Reviewed-by: Discussion: --- src/backend/utils/adt/pgstatfuncs.c | 47 +++++++++++++++++++---------- 1 file changed, 31 insertions(+), 16 deletions(-) 100.0% src/backend/utils/adt/ diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 6f9c9c72de5..353607954ad 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1737,38 +1737,53 @@ pg_stat_get_wal(PG_FUNCTION_ARGS) wal_stats->stat_reset_timestamp)); } -Datum -pg_stat_get_lock(PG_FUNCTION_ARGS) +/* + * pg_stat_lock_build_tuples + * + * Helper routine for pg_stat_get_lock(), filling a result tuplestore with one + * tuple for each lock type. + */ +static void +pg_stat_lock_build_tuples(ReturnSetInfo *rsinfo, + PgStat_LockEntry *lock_stats, + TimestampTz stat_reset_timestamp) { #define PG_STAT_LOCK_COLS 5 - ReturnSetInfo *rsinfo; - PgStat_Lock *lock_stats; - - InitMaterializedSRF(fcinfo, 0); - rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; - - lock_stats = pgstat_fetch_stat_lock(); - for (int lcktype = 0; lcktype <= LOCKTAG_LAST_TYPE; lcktype++) { - const char *locktypename; Datum values[PG_STAT_LOCK_COLS] = {0}; bool nulls[PG_STAT_LOCK_COLS] = {0}; - PgStat_LockEntry *lck_stats = &lock_stats->stats[lcktype]; + PgStat_LockEntry *lck_stats = &lock_stats[lcktype]; int i = 0; - locktypename = LockTagTypeNames[lcktype]; - - values[i++] = CStringGetTextDatum(locktypename); + values[i++] = CStringGetTextDatum(LockTagTypeNames[lcktype]); values[i++] = Int64GetDatum(lck_stats->waits); values[i++] = Int64GetDatum(lck_stats->wait_time); values[i++] = Int64GetDatum(lck_stats->fastpath_exceeded); - values[i] = TimestampTzGetDatum(lock_stats->stat_reset_timestamp); + if (stat_reset_timestamp != 0) + values[i] = TimestampTzGetDatum(stat_reset_timestamp); + else + nulls[i] = true; Assert(i + 1 == PG_STAT_LOCK_COLS); tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls); } +} + +Datum +pg_stat_get_lock(PG_FUNCTION_ARGS) +{ + ReturnSetInfo *rsinfo; + PgStat_Lock *lock_stats; + + InitMaterializedSRF(fcinfo, 0); + rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + + lock_stats = pgstat_fetch_stat_lock(); + + pg_stat_lock_build_tuples(rsinfo, lock_stats->stats, + lock_stats->stat_reset_timestamp); return (Datum) 0; } -- 2.34.1
>From 51eb375f5cc254ada379c0722db1c4e07a91910d Mon Sep 17 00:00:00 2001 From: Bertrand Drouvot <[email protected]> Date: Wed, 3 Jun 2026 13:05:03 +0000 Subject: [PATCH v1 2/2] Add per-backend lock statistics This commit adds per-backend lock statistics, providing the same information as pg_stat_lock, except that it is now possible to retrieve those stats (lock wait counts, wait times, and fast-path exceeded count) on a per-backend basis. 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. Like pg_stat_get_backend_io(), this is useful when joined with pg_stat_activity to get a live picture of the locks behavior for each running backend. pgstat_flush_backend() gains a new flag value, able to control the flush of the lock stats. This commit relies mostly on the infrastructure provided by 9aea73fc61d4, that has introduced backend statistics. XXX: Bump catalog version. A bump of PGSTAT_FILE_FORMAT_ID is not required, as backend stats do not persist on disk. Author: Bertrand Drouvot <[email protected]> Reviewed-by: Discussion: --- doc/src/sgml/monitoring.sgml | 19 ++++++ src/backend/utils/activity/pgstat_backend.c | 70 +++++++++++++++++++++ src/backend/utils/activity/pgstat_lock.c | 4 ++ src/backend/utils/adt/pgstatfuncs.c | 29 ++++++++- src/include/catalog/pg_proc.dat | 8 +++ src/include/pgstat.h | 11 ++++ src/include/utils/pgstat_internal.h | 3 +- src/test/regress/expected/stats.out | 12 ++++ src/test/regress/sql/stats.sql | 9 +++ 9 files changed, 162 insertions(+), 3 deletions(-) 14.7% doc/src/sgml/ 38.6% src/backend/utils/activity/ 14.8% src/backend/utils/adt/ 8.7% src/include/catalog/ 9.9% src/include/ 6.8% src/test/regress/expected/ 6.1% src/test/regress/sql/ diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 08d5b824552..3936fb62a5d 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -5545,6 +5545,25 @@ description | Waiting for a newly initialized WAL file to reach durable storage </para></entry> </row> + <row> + <entry id="pg-stat-get-backend-lock" role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>pg_stat_get_backend_lock</primary> + </indexterm> + <function>pg_stat_get_backend_lock</function> ( <type>integer</type> ) + <returnvalue>setof record</returnvalue> + </para> + <para> + Returns lock statistics about the backend with the specified + process ID. The output fields are exactly the same as the ones in the + <structname>pg_stat_lock</structname> view. + </para> + <para> + The function does not return lock statistics for the checkpointer, + the background writer, the startup process and the autovacuum launcher. + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c index 73461c9bca5..297eda0a489 100644 --- a/src/backend/utils/activity/pgstat_backend.c +++ b/src/backend/utils/activity/pgstat_backend.c @@ -39,6 +39,7 @@ */ static PgStat_BackendPending PendingBackendStats; static bool backend_has_iostats = false; +static bool backend_has_lockstats = false; /* * WAL usage counters saved from pgWalUsage at the previous call to @@ -86,6 +87,37 @@ pgstat_count_backend_io_op(IOObject io_object, IOContext io_context, pgstat_report_fixed = true; } +/* + * Utility routines to report lock stats for backends, kept here to avoid + * exposing PendingBackendStats to the outside world. + */ +void +pgstat_count_backend_lock_waits(uint8 locktag_type, long msecs) +{ + if (!pgstat_tracks_backend_bktype(MyBackendType)) + return; + + Assert(locktag_type <= LOCKTAG_LAST_TYPE); + PendingBackendStats.pending_lock.stats[locktag_type].waits++; + PendingBackendStats.pending_lock.stats[locktag_type].wait_time += (PgStat_Counter) msecs; + + backend_has_lockstats = true; + pgstat_report_fixed = true; +} + +void +pgstat_count_backend_lock_fastpath_exceeded(uint8 locktag_type) +{ + if (!pgstat_tracks_backend_bktype(MyBackendType)) + return; + + Assert(locktag_type <= LOCKTAG_LAST_TYPE); + PendingBackendStats.pending_lock.stats[locktag_type].fastpath_exceeded++; + + backend_has_lockstats = true; + pgstat_report_fixed = true; +} + /* * Returns statistics of a backend by proc number. */ @@ -262,6 +294,36 @@ pgstat_flush_backend_entry_wal(PgStat_EntryRef *entry_ref) prevBackendWalUsage = pgWalUsage; } +/* + * Flush out locally pending backend lock statistics. Locking is managed + * by the caller. + */ +static void +pgstat_flush_backend_entry_lock(PgStat_EntryRef *entry_ref) +{ + PgStatShared_Backend *shbackendent; + PgStat_PendingLock *bktype_shstats; + + if (!backend_has_lockstats) + return; + + shbackendent = (PgStatShared_Backend *) entry_ref->shared_stats; + bktype_shstats = &shbackendent->stats.lock_stats; + + for (int i = 0; i <= LOCKTAG_LAST_TYPE; i++) + { +#define LOCKSTAT_ACC(fld) \ + (bktype_shstats->stats[i].fld += PendingBackendStats.pending_lock.stats[i].fld) + LOCKSTAT_ACC(waits); + LOCKSTAT_ACC(wait_time); + LOCKSTAT_ACC(fastpath_exceeded); +#undef LOCKSTAT_ACC + } + + MemSet(&PendingBackendStats.pending_lock, 0, sizeof(PgStat_PendingLock)); + backend_has_lockstats = false; +} + /* * Flush out locally pending backend statistics * @@ -286,6 +348,10 @@ pgstat_flush_backend(bool nowait, uint32 flags) pgstat_backend_wal_have_pending()) has_pending_data = true; + /* Some lock data pending? */ + if ((flags & PGSTAT_BACKEND_FLUSH_LOCK) && backend_has_lockstats) + has_pending_data = true; + if (!has_pending_data) return false; @@ -301,6 +367,9 @@ pgstat_flush_backend(bool nowait, uint32 flags) if (flags & PGSTAT_BACKEND_FLUSH_WAL) pgstat_flush_backend_entry_wal(entry_ref); + if (flags & PGSTAT_BACKEND_FLUSH_LOCK) + pgstat_flush_backend_entry_lock(entry_ref); + pgstat_unlock_entry(entry_ref); return false; @@ -339,6 +408,7 @@ pgstat_create_backend(ProcNumber procnum) MemSet(&PendingBackendStats, 0, sizeof(PgStat_BackendPending)); backend_has_iostats = false; + backend_has_lockstats = false; /* * Initialize prevBackendWalUsage with pgWalUsage so that diff --git a/src/backend/utils/activity/pgstat_lock.c b/src/backend/utils/activity/pgstat_lock.c index aec64f8fb4b..76116db3593 100644 --- a/src/backend/utils/activity/pgstat_lock.c +++ b/src/backend/utils/activity/pgstat_lock.c @@ -131,6 +131,8 @@ pgstat_count_lock_fastpath_exceeded(uint8 locktag_type) PendingLockStats.stats[locktag_type].fastpath_exceeded++; have_lockstats = true; pgstat_report_fixed = true; + + pgstat_count_backend_lock_fastpath_exceeded(locktag_type); } /* @@ -147,4 +149,6 @@ pgstat_count_lock_waits(uint8 locktag_type, long msecs) PendingLockStats.stats[locktag_type].wait_time += (PgStat_Counter) msecs; have_lockstats = true; pgstat_report_fixed = true; + + pgstat_count_backend_lock_waits(locktag_type, msecs); } diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 353607954ad..3f7c238e557 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1740,8 +1740,8 @@ pg_stat_get_wal(PG_FUNCTION_ARGS) /* * pg_stat_lock_build_tuples * - * Helper routine for pg_stat_get_lock(), filling a result tuplestore with one - * tuple for each lock type. + * Helper routine for pg_stat_get_lock() and pg_stat_get_backend_lock(), + * filling a result tuplestore with one tuple for each lock type. */ static void pg_stat_lock_build_tuples(ReturnSetInfo *rsinfo, @@ -1788,6 +1788,31 @@ pg_stat_get_lock(PG_FUNCTION_ARGS) return (Datum) 0; } +/* + * Returns lock statistics for a backend with given PID. + */ +Datum +pg_stat_get_backend_lock(PG_FUNCTION_ARGS) +{ + int pid; + ReturnSetInfo *rsinfo; + PgStat_Backend *backend_stats; + + InitMaterializedSRF(fcinfo, 0); + rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + + pid = PG_GETARG_INT32(0); + backend_stats = pgstat_fetch_stat_backend_by_pid(pid, NULL); + + if (!backend_stats) + return (Datum) 0; + + pg_stat_lock_build_tuples(rsinfo, backend_stats->lock_stats.stats, + backend_stats->stat_reset_timestamp); + + return (Datum) 0; +} + /* * Returns statistics of SLRU caches. */ diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index be157a5fbe9..87f7161527e 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -6092,6 +6092,14 @@ proargmodes => '{i,o,o,o,o,o,o}', proargnames => '{backend_pid,wal_records,wal_fpi,wal_bytes,wal_fpi_bytes,wal_buffers_full,stats_reset}', prosrc => 'pg_stat_get_backend_wal' }, +{ oid => '9682', descr => 'statistics: backend lock statistics', + proname => 'pg_stat_get_backend_lock', prorows => '10', proretset => 't', + provolatile => 'v', proparallel => 'r', prorettype => 'record', + proargtypes => 'int4', + proallargtypes => '{int4,text,int8,int8,int8,timestamptz}', + proargmodes => '{i,o,o,o,o,o}', + proargnames => '{backend_pid,locktype,waits,wait_time,fastpath_exceeded,stats_reset}', + prosrc => 'pg_stat_get_backend_lock' }, { oid => '6248', descr => 'statistics: information about WAL prefetching', proname => 'pg_stat_get_recovery_prefetch', prorows => '1', proretset => 't', provolatile => 'v', prorettype => 'record', proargtypes => '', diff --git a/src/include/pgstat.h b/src/include/pgstat.h index dfa2e837638..b5e69e6250e 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -523,6 +523,7 @@ typedef struct PgStat_Backend TimestampTz stat_reset_timestamp; PgStat_BktypeIO io_stats; PgStat_WalCounters wal_counters; + PgStat_PendingLock lock_stats; } PgStat_Backend; /* --------- @@ -535,6 +536,12 @@ typedef struct PgStat_BackendPending * Backend statistics store the same amount of IO data as PGSTAT_KIND_IO. */ PgStat_PendingIO pending_io; + + /* + * Backend statistics store the same amount of lock data as + * PGSTAT_KIND_LOCK. + */ + PgStat_PendingLock pending_lock; } PgStat_BackendPending; /* @@ -586,6 +593,10 @@ extern void pgstat_count_backend_io_op(IOObject io_object, IOContext io_context, IOOp io_op, uint32 cnt, uint64 bytes); + +/* used by pgstat_lock.c for lock stats tracked in backends */ +extern void pgstat_count_backend_lock_waits(uint8 locktag_type, long msecs); +extern void pgstat_count_backend_lock_fastpath_exceeded(uint8 locktag_type); extern PgStat_Backend *pgstat_fetch_stat_backend(ProcNumber procNumber); extern PgStat_Backend *pgstat_fetch_stat_backend_by_pid(int pid, BackendType *bktype); diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h index fe463faaf63..b0788336ae3 100644 --- a/src/include/utils/pgstat_internal.h +++ b/src/include/utils/pgstat_internal.h @@ -705,7 +705,8 @@ extern void pgstat_archiver_snapshot_cb(void); /* flags for pgstat_flush_backend() */ #define PGSTAT_BACKEND_FLUSH_IO (1 << 0) /* Flush I/O statistics */ #define PGSTAT_BACKEND_FLUSH_WAL (1 << 1) /* Flush WAL statistics */ -#define PGSTAT_BACKEND_FLUSH_ALL (PGSTAT_BACKEND_FLUSH_IO | PGSTAT_BACKEND_FLUSH_WAL) +#define PGSTAT_BACKEND_FLUSH_LOCK (1 << 2) /* Flush lock statistics */ +#define PGSTAT_BACKEND_FLUSH_ALL (PGSTAT_BACKEND_FLUSH_IO | PGSTAT_BACKEND_FLUSH_WAL | PGSTAT_BACKEND_FLUSH_LOCK) extern bool pgstat_flush_backend(bool nowait, uint32 flags); extern bool pgstat_backend_flush_cb(bool nowait); diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index bbb1db3c433..fa550676f83 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -2019,6 +2019,10 @@ BEGIN END; $$; SELECT fastpath_exceeded AS fastpath_exceeded_before FROM pg_stat_lock WHERE locktype = 'relation' \gset +-- Test pg_stat_get_backend_lock() +SELECT fastpath_exceeded AS backend_fastpath_exceeded_before + FROM pg_stat_get_backend_lock(pg_backend_pid()) + WHERE locktype = 'relation' \gset -- Needs a lock on each partition SELECT count(*) FROM part_test; count @@ -2039,5 +2043,13 @@ SELECT fastpath_exceeded > :fastpath_exceeded_before FROM pg_stat_lock WHERE loc t (1 row) +SELECT fastpath_exceeded > :backend_fastpath_exceeded_before + FROM pg_stat_get_backend_lock(pg_backend_pid()) + WHERE locktype = 'relation'; + ?column? +---------- + t +(1 row) + DROP TABLE part_test; -- End of Stats Test diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index 610fd21fae4..f5683302a75 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -998,6 +998,11 @@ $$; SELECT fastpath_exceeded AS fastpath_exceeded_before FROM pg_stat_lock WHERE locktype = 'relation' \gset +-- Test pg_stat_get_backend_lock() +SELECT fastpath_exceeded AS backend_fastpath_exceeded_before + FROM pg_stat_get_backend_lock(pg_backend_pid()) + WHERE locktype = 'relation' \gset + -- Needs a lock on each partition SELECT count(*) FROM part_test; @@ -1006,6 +1011,10 @@ SELECT pg_stat_force_next_flush(); SELECT fastpath_exceeded > :fastpath_exceeded_before FROM pg_stat_lock WHERE locktype = 'relation'; +SELECT fastpath_exceeded > :backend_fastpath_exceeded_before + FROM pg_stat_get_backend_lock(pg_backend_pid()) + WHERE locktype = 'relation'; + DROP TABLE part_test; -- End of Stats Test -- 2.34.1
