Debugging PostgreSQL Throughput Drops | Dataford Interview Questions

Your question is Debugging PostgreSQL Throughput Drops. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

How would you debug a sudden, unexplained throughput drop in a high-performance PostgreSQL database under concurrent write-heavy workloads?

Write a PostgreSQL query over the diagnostic snapshots to identify current lock contention and compare each affected query's current five-minute call rate with its preceding five-minute rate.

Output

One row per currently blocked session and matching blocker.
Include blocked_pid, blocker_pid, lock_key, blocked_seconds, blocked_query, blocker_query, query_id, current_calls, previous_calls, and throughput_ratio.
Include only current blocked sessions with a known blocker. Sort by blocked_seconds descending, then both process IDs ascending.

Schema

activity_snapshots

Column	Type	Description
captured_at	TIMESTAMP	Diagnostic capture timestamp
pidPK	INT	Backend process identifier
state	VARCHAR(32)	Backend state
query_id	INT	Normalized query identifier
query	TEXT	Current SQL text
query_start	TIMESTAMP	Time the current query began

lock_snapshots

Column	Type	Description
captured_at	TIMESTAMP	Diagnostic capture timestamp
pidPK	INT	Backend process identifier
lock_keyPK	VARCHAR(64)	Normalized lock resource identifier
mode	VARCHAR(32)	Requested lock mode
granted	BOOLEAN	Whether the lock is granted

query_metrics

Column	Type	Description
captured_atPK	TIMESTAMP	Metric capture timestamp
query_idPK	INT	Normalized query identifier
calls	BIGINT	Cumulative query call count