PostgreSQL Locks and Blocked Queries

Monitoring and Operations / Guide 39 of 47 / Intermediate

• OSUbuntu 26.04 LTS
• PostgreSQL18.6-0ubuntu0.26.04.1
• Host2 core / 4 GB, 400,000-row test table
• TimeAbout 18 min

pg_blocking_pids() has been available since PostgreSQL 9.6. Before that, finding a blocker meant self-joining pg_locks by hand.

This guide includes

Use this when a query is blocked and you need the culprit rather than a theory. This matters because pg_blocking_pids() names it in one column - and a read can be blocked by a lock it does not even conflict with.

• creating a blocked query across two sessions
• reading the locks underneath, where only one of six rows matters
• naming the blocker in one column with pg_blocking_pids()
• releasing it, then setting lock_timeout instead of waiting forever
• watching a read blocked by a lock it does not conflict with - the one that causes outages

Before you start

• The events table from the performance track.
• Two shells, or the background jobs used here.

1. 1

   Create a blocked query

2. 2

   Read the locks underneath

3. 3

   Name the blocker in one column

4. 4

   Release it

5. 5

   Set a limit instead of waiting forever

6. 6

   Watch a read get blocked by a lock it does not conflict with

Official sources

• PostgreSQL 18 Documentation - The Statistics Collector
• PostgreSQL 18 Documentation - Viewing Locks
• PostgreSQL 18 Documentation - Explicit Locking
• PostgreSQL 18 Documentation - Error Reporting and Logging
• PostgreSQL 18 Documentation - Connections and Authentication
• PgBouncer Documentation