Technologies/PostgreSQL/postgresql.database.locks

PostgreSQLMetric

postgresql.database.locks

Active locks

Dimensions:None

Available on:

Datadog (1)

OpenTelemetry (1)

Prometheus (1)

Interface Metrics (3)

Datadog

postgresql.locks

Enabled with `relations`. The number of locks active for this database. This metric is tagged with db, lock_mode, lock_type, schema, table, granted.

Dimensions:None

OpenTelemetry

postgresql.database.locks

The number of database locks.

Dimensions:None

Prometheus

locks_count

Number of locks

Dimensions:None

Sources

postgresql.locksgithub.com

postgresql.database.locksgithub.com

locks_countgithub.com

Technical Annotations (25)

Configuration Parameters (3)

lock_timeout

prevents queries from waiting indefinitely for locks

log_lock_waitsrecommended: on

enables logging of slow lock acquisitions

deadlock_timeoutrecommended: 1s

time threshold before logging lock waits

CLI Commands (7)

ALTER SYSTEM SET log_lock_waits = on;monitoring

ALTER SYSTEM SET deadlock_timeout = '1s';monitoring

SELECT pg_reload_conf();remediation

SELECT id, payload FROM job_queue WHERE status = 'pending' ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED;remediation

CREATE INDEX CONCURRENTLY idx_orders_customer ON orders(customer_id);remediation

CREATE TABLE events (...) PARTITION BY RANGE (created_at);remediation

SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS blocking_statement FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted;

diagnostic

Technical References (15)

pg_lockscomponentpg_stat_activitycomponentcheck_postgrescomponentungranted locksconceptlock contentionconceptShareLockcomponentpg_catalog.pg_lockscomponentSKIP LOCKEDconceptFOR UPDATEconceptjob queuecomponentACCESS EXCLUSIVEcomponentCREATE INDEX CONCURRENTLYconcepttable partitioningconceptPARTITION BY RANGEconceptwait_event_typecomponent

Related Insights (9)

Lock contention and deadlocks block transaction progresswarning

▸

Ungranted locks on a relation indicate contention among database clientswarning

▸

Long-running transactions create lock contention and query pileupcritical

▸

Lock conflicts and deadlocks degrade query performancewarning

▸

Lock wait times exceed acceptable thresholdswarning

▸

Queue workers block each other waiting for row lockswarning

▸

CREATE INDEX blocks all writes during buildcritical

▸

High contention on single table from concurrent updateswarning

▸

Blocked queries identified in pg_stat_activitywarning

▸