Skip to main content

// advent-of-postgres/day-18

Day 18 of 24AdvancedRecursive CTEsArrays

The Lock Pile-Up

Thirteen sessions are stuck waiting on locks. Follow the blocking chains back to the session that is really holding everyone up.

Opens 18 December 2026, 00:00 UTC

At 03:12 the gift database froze. Pepper caught a snapshot of who was waiting on whom before restarting anything, and wants to know which session to cancel first.

The snapshot is in lock_snapshot, built from pg_blocking_pids():

Column Type Notes
pid int Primary key: the session
query text What it was running
blocked_by int[] The sessions it was waiting for (can be empty)

If session A waits for B, and B waits for C, then C is holding up both B and A. A session can wait for more than one other session.

Your task

Which session holds up the most other sessions, directly or through a chain?

Submit its pid.

Stuck? Show a hint

unnest(blocked_by) turns the array into one row per blocker. Then walk the edges with a recursive CTE and count everyone each root is holding up.

No database yet. You get a real Postgres database on Neon, free, with no account.
One statement at a time. Ctrl+Enter or Cmd+Enter runs it.

Download today's data as SQL to solve it on your own Postgres (14 or later).

Solved this puzzle? Share it.

Tag @thedevopsdaily and share it with the community.