Add Per-Backend Lock Statistics: Technical Analysis

Core Problem

PostgreSQL 17 introduced global lock statistics via commit 4019f725f5d, exposed through the pg_stat_lock view. This provides cluster-wide aggregates for lock waits, wait times, and fast-path exhaustion events. However, these global counters suffer from a fundamental observability limitation: they cannot attribute lock contention to specific backends, applications, or workload patterns.

This is analogous to having only system-wide CPU usage without per-process breakdown — you know there's a problem but can't identify the culprit.

Architectural Context

The Statistics Infrastructure

PostgreSQL's statistics subsystem has evolved toward a consistent pattern:

• Global stats → pg_stat_io, pg_stat_wal, pg_stat_lock
• Per-backend stats → pg_stat_get_backend_io(), pg_stat_get_backend_wal()

The backend statistics infrastructure was formalized in commit 9aea73fc61d4, which introduced the PgStat_Backend statistics kind. This provides a framework for associating per-backend pending statistics that get flushed through pgstat_flush_backend(). Lock statistics at the per-backend level are a natural extension of this pattern.

Why This Matters Architecturally

Lock contention in PostgreSQL manifests in several distinct ways:

1. Heavyweight lock waits — backends blocked waiting for conflicting locks (post-deadlock-timeout path)
2. Fast-path exhaustion — when a backend exceeds its 16 fast-path lock slots and must promote to the shared lock table, causing contention on shared memory lock partitions

Both of these are "slow paths" — they only fire when something has already gone wrong. The key architectural insight is that instrumenting slow paths has zero overhead on the fast path. Normal lock acquisition (the hot path via FastPathGrantRelationLock) remains untouched.

Memory Overhead

The PgStat_PendingLock structure is 288 bytes per backend. Given that PostgreSQL already allocates substantially more per-backend for other statistics (IO stats alone are several KB per backend due to the multi-dimensional breakdown by backend type, IO object, IO context, and IO operation), this is negligible.

Proposed Solution

Patch Structure

0001: Refactor pg_stat_get_lock() to use a helper function

Extracts tuple-building logic into pg_stat_lock_build_tuples(), following the established pattern:

• pg_stat_io_build_tuples() — shared between pg_stat_io view and pg_stat_get_backend_io()
• pg_stat_wal_build_tuple() — shared between pg_stat_wal view and pg_stat_get_backend_wal()

This is a clean refactoring prerequisite that ensures the per-backend function returns identical tuple structure to the global view, maintaining API consistency.

0002: Add per-backend lock statistics

Key components:

• New SQL-callable function pg_stat_get_backend_lock(pid) — returns one tuple per lock type for the specified backend
• Extension of pgstat_flush_backend() with a new flag value to control lock stats flushing
• Per-backend pending counters (PgStat_PendingLock) that accumulate in backend-local memory and flush through the existing statistics flush infrastructure

Design Decisions and Tradeoffs

1. PID-based lookup interface: Rather than exposing all backends' lock stats in a single view (which would require joining), the function takes a PID input. This matches pg_stat_get_backend_io() and pg_stat_get_backend_wal() conventions, enabling direct correlation with pg_stat_activity.

2. Flush-controlled architecture: Statistics are accumulated in pending counters local to each backend and flushed periodically through pgstat_flush_backend(). This avoids contention on shared memory during counter increments — critical since lock operations are already contention-sensitive.

3. Zero hot-path overhead: Counters are only incremented on paths that are already "slow" — post-deadlock-timeout waits and fast-path slot exhaustion. The normal LockAcquire → FastPathGrantRelationLock path is unmodified.

4. Per-lock-type granularity: Returns one row per lock type (AccessShareLock, RowShareLock, etc.), matching the global pg_stat_lock view structure. This enables identifying whether contention is on heavy exclusive locks vs. lighter share locks.

Use Cases Enabled

Consistency with Existing Infrastructure

The proposal follows a well-established pattern in PostgreSQL's statistics framework:

Global View          Per-Backend Function           Shared Helper
─────────────────────────────────────────────────────────────────
pg_stat_io       →  pg_stat_get_backend_io()    →  pg_stat_io_build_tuples()
pg_stat_wal      →  pg_stat_get_backend_wal()   →  pg_stat_wal_build_tuple()
pg_stat_lock     →  pg_stat_get_backend_lock()  →  pg_stat_lock_build_tuples()  [proposed]

This consistency is important for both code maintainability and user mental models. The absence of per-backend lock stats is genuinely an inconsistency in the current observability surface.

Potential Review Concerns

1. Catalog additions: New built-in function requires a new pg_proc entry and OID allocation — routine but needs CF attention.
2. Interaction with connection poolers: Per-backend stats are tied to backend lifetime; with connection pooling, a single backend serves multiple logical sessions, potentially complicating attribution. However, this is the same limitation as pg_stat_get_backend_io().
3. Reset semantics: How are per-backend lock stats reset? Presumably on backend exit and via explicit reset functions. The interaction with pg_stat_reset_backend_stats() would need to be clear.
4. Backward compatibility: Since this is purely additive (new function, no changes to existing interfaces), there are no compatibility concerns.

Current Status

This is an initial RFC posting with no review responses yet. The patch builds on mature infrastructure (the backend statistics framework from 9aea73fc61d4 and global lock stats from 4019f725f5d), which should simplify review. The implementation approach is conservative — extending existing patterns rather than introducing new mechanisms.