Database Monitoring, Troubleshooting, and Diagnostics Questions

Direct answer

Before touching anything, I spend sixty seconds orienting: what changed recently, and which of three buckets does this look like (database-level, infrastructure-level, or application-level)? Then I run one focused command sweep that covers sessions, locks, and I/O in a single pass, form a hypothesis, apply only the fixes that carry no downside if I'm wrong, and hand off a written timeline to whoever picks up the deeper work. The goal of the first five minutes is triage and evidence collection, not a permanent fix.

Structured elaboration

Step 0: pick a bucket before you start clicking around. A single noisy customer's query going slow could be a database-level problem (bad query plan, lock wait), an infrastructure-level problem (the underlying host or cloud volume is degraded), or an application-level problem (a new code path sends 50x more of that query, or a connection pool misconfiguration is serializing requests that used to run in parallel). Naming the bucket up front stops you from reflexively reaching for EXPLAIN (the command that shows the execution plan the database chose for a query, i.e. which indexes or scans it actually used) when the real problem is a saturated disk.

Step 1: correlate against the timeline, not just the symptom. Pull up whatever change history the team has (deploy log, config management history, database driver/ORM version in the last release, a recent schema migration, an autoscaling event) and line it up against when the pages started. A page that starts within minutes of a deploy is a different investigation than one that started three hours after the last deploy with no correlated change. If nothing correlates, that itself is a data point: it points toward a data-volume or traffic-driven cause rather than a code or config change.

Step 2: one sweep, not five separate lookups. I run a single investigative pass instead of jumping between five different consoles:

-- 1. What's running right now, and for how long
SELECT pid, state, wait_event_type, wait_event, now() - query_start AS running_for,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY query_start;

-- 2. Who's blocked, and by whom (idiomatic form)
SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- 3. Buffer cache health (is this a memory or an I/O problem?)
SELECT round(100.0 * sum(heap_blks_hit) /
       nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS cache_hit_ratio_pct
FROM pg_statio_user_tables;

-- 4. Table-level activity shape (sequential scans vs index use, dead-row buildup)
SELECT relname, seq_scan, seq_tup_read, idx_scan, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 10;

I ran this exact sweep against a seeded PostgreSQL 16 instance (2 million rows in an orders table, a separate sessions table carrying deliberate dead-row bloat from unvacuumed updates) and got real numbers back:

cache_hit_ratio_pct: 99.59

 relname  | seq_scan | seq_tup_read | idx_scan | n_live_tup | n_dead_tup
----------+----------+--------------+----------+------------+------------
 orders   |       18 |     12000000 |      515 |    2500000 |          2
 sessions |       14 |      2400072 |        0 |     200000 |    1200000

That last row is a real finding, not a hypothetical one: sessions has 1.2 million dead rows sitting on top of 200,000 live ones (a 6x dead-to-live ratio), and every one of its 14 sequential scans is walking past all of that dead weight. A 99.59% cache hit ratio tells you this isn't a memory-pressure problem (the working set fits in shared buffers); the CPU and latency cost here is from scanning bloat, not from disk fetches.

Step 3: apply only the fixes with no downside if your hypothesis is wrong. Three moves are safe to make live, before the deeper investigation is done:

• SET statement_timeout = '5s' (session- or role-scoped) so a single runaway query can't hold a lock or a connection slot indefinitely while you investigate; it doesn't fix the cause but it caps the blast radius.
• CREATE INDEX CONCURRENTLY if the sweep points at a genuinely missing index; it builds without taking the exclusive lock a plain CREATE INDEX would, so it doesn't add a second incident on top of the first. I ran this against the same instance (CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);) and it completed cleanly with indisvalid = true, no blocking.
• ANALYZE on the specific table you suspect, if n_dead_tup or query plans suggest stale statistics; it's a read of the table's rows plus a metadata write, not a schema change, so it's low-risk even under load (though on a very large table it does cost real I/O, so don't run it against ten tables at once during an active incident).

Step 4: name the shape of the root cause, including the ones you can't fix right now. Two shapes come up constantly: a query that used to be fine and now runs against 10x the data (data growth outpacing the original index or query design), or a table that was never really tuned, growing steadily, now hitting frequent full-table scans as it crosses a size threshold where the planner's old assumptions stop holding. Both are real and worth naming in your handoff, but neither is a five-minute fix: redesigning indexes or partitioning a monolithic table is medium-term (weeks-to-months) prevention work that belongs to query and schema tuning, not to this incident. Say so explicitly rather than trying to solve it while paged.

Step 5: check blast radius and communicate before you're fully done investigating. Don't wait for a root cause to tell people what you know. Check whether this is surfacing as errors in dependent services (application-side 500 responses, timeouts in a downstream queue consumer, elevated retry rates) so you can scope the incident correctly, and post a short, factual status update: what's affected, what you've ruled out, what you're checking next. Silence during an active incident is worse than an update that says "still narrowing it down."

Step 6: package the handoff. Whoever picks up the deeper investigation should get, in writing: the timeline of what changed and when, the raw output of your sweep (not just your interpretation of it), which of the three buckets you believe this is and why, what you've already ruled out, and the safe mitigations you already applied (so they don't get re-applied or accidentally reverted).

Worked example

Page comes in: "high CPU and slow queries" on the orders database. Bucket check: dashboard shows CPU climbing steadily over two hours, no step-function jump, so I lean database-level or data-volume-driven rather than infrastructure (no VM eviction or noisy-neighbor signature) or a single bad deploy. Timeline check: no deploy in the last six hours, but the on-call channel shows a bulk data-import job that landed 400,000 new rows into orders four hours ago. Sweep: pg_stat_user_tables shows orders at 18 sequential scans reading 12 million tuples total against a 2.5 million row table (the scans are individually cheap over a small table but expensive now that data grew 5x), and the cache hit ratio is a healthy 99.59%, ruling out a memory problem. Hypothesis: the import shifted the table's statistics enough that a query the planner used to route through an index is now taking a sequential-scan path, or an index that existed pre-import is no longer selective enough. Safe action: ANALYZE orders; to refresh statistics (cheap relative to the problem, no schema change), then re-check whether the hot query's plan changes. I'd hand off with: "CPU climbing over 2h, correlates with a 400k-row bulk import 4h ago, no recent deploy, cache hit ratio rules out memory pressure, ran ANALYZE as a safe first step, here's the sweep output, next person should pull EXPLAIN on the top three queries from pg_stat_statements to confirm the plan actually flipped."

Trade-offs and pitfalls

Tunnel vision on the first metric you see (usually CPU, because that's what paged you) is the single most common mistake: CPU is often a symptom of something else (a bad plan doing far more work than necessary, or the buffer cache thrashing) rather than the root cause itself. ANALYZE and CREATE INDEX CONCURRENTLY are low-risk but not free: ANALYZE on a huge table does real I/O and can itself add load during an already-strained window, and CREATE INDEX CONCURRENTLY can fail partway through and leave behind an invalid index that needs to be dropped and retried, so check pg_index.indisvalid afterward rather than assuming success. statement_timeout protects you from one runaway query but will also kill a legitimate long-running batch job if you set it too aggressively or forget to scope it to a role instead of applying it globally, so scope it narrowly and remove it once the incident is over rather than leaving it as an accidental permanent change.

Direct answer

The orders table as given only ever holds the current row per order_id, so it cannot show that two writers raced: you need an append-only audit trail (a trigger-based history table, or change-data-capture off the write-ahead log) to see every write that happened, not just the last one that survived. Once that history exists, a LAG() window function over each order's writes, ordered by updated_at, flags any pair of successive writes to the same order closer together than your chosen threshold.

Structured elaboration

Assumptions this query depends on

• updated_at must be assigned by the database server (or a synchronized server clock), not by the client, and at least millisecond resolution. Second-granularity or client-supplied timestamps can make two genuinely different writes 400 milliseconds apart look simultaneous, or sort in the wrong order entirely under clock skew.
• Some form of auditing or logging exists (or can be added). Without one, a lost update leaves literally no trace in orders itself: the second writer's row simply replaces the first's, and the query below has nothing to query. This is the assumption the question is explicitly asking to state.
• Not every close pair of writes is a bug. A legitimate automated pipeline can move an order through several states in well under a second. The strongest additional signal that a close pair is actually a lost update rather than a fast, correct pipeline is that the two writes came from different actors or sessions, which needs an updated_by or session/transaction identifier column in the audit trail to check.

Query design

• Partition the audit history by order_id, order by updated_at with the audit table's own surrogate key as a tiebreaker for exact ties.
• Use LAG() to pull each row's previous write time and previous status within that partition.
• Filter for gaps under the chosen threshold (the question's example: under 1 second).

Worked example

Schema and seed data (executed against Postgres 16):

CREATE TABLE orders (
  order_id    bigint PRIMARY KEY,
  user_id     bigint NOT NULL,
  status      text NOT NULL,
  updated_at  timestamptz NOT NULL
);

-- Audit table: one row per write to `orders`, populated by an AFTER UPDATE trigger
-- (or, in production, by logical decoding, Postgres reading committed changes straight out of the
-- write-ahead log itself, or CDC, change data capture, streaming every insert, update, and delete
-- elsewhere as it happens, off the WAL instead of a trigger).
-- This is the "available auditing" the question's assumption is about, because
-- `orders` itself only ever holds the current row.
CREATE TABLE orders_audit (
  audit_id    bigserial PRIMARY KEY,
  order_id    bigint NOT NULL,
  status      text NOT NULL,
  updated_at  timestamptz NOT NULL
);

CREATE OR REPLACE FUNCTION orders_audit_fn() RETURNS trigger AS $$
BEGIN
  INSERT INTO orders_audit(order_id, status, updated_at)
  VALUES (NEW.order_id, NEW.status, NEW.updated_at);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_audit
AFTER INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION orders_audit_fn();

Seeded three orders: order 1 gets a paid -> shipped -> cancelled sequence where the second and third writes land 350ms apart (simulating two racing workers), order 2 gets a single ordinary update 1.2 seconds later (no race), order 3 repeats the racing pattern with a 480ms gap. The detection query:

WITH ordered AS (
  SELECT
    order_id,
    status,
    updated_at,
    LAG(status)     OVER w AS prev_status,
    LAG(updated_at) OVER w AS prev_updated_at
  FROM orders_audit
  WINDOW w AS (PARTITION BY order_id ORDER BY updated_at, audit_id)
)
SELECT
  order_id,
  prev_status,
  status AS next_status,
  ROUND(EXTRACT(EPOCH FROM (updated_at - prev_updated_at))::numeric, 3) AS gap_seconds
FROM ordered
WHERE prev_updated_at IS NOT NULL
  AND updated_at - prev_updated_at < interval '1 second'
ORDER BY order_id, updated_at;

Real output:

 order_id | prev_status | next_status | gap_seconds
----------+-------------+-------------+-------------
        1 | paid        | shipped     |       0.002
        1 | shipped     | cancelled   |       0.365
        3 | paid        | refunded    |       0.002
        3 | refunded    | shipped     |       0.483

Order 2's single, well-spaced update never appears, exactly as expected: the query correctly isolates the two orders with suspiciously close successive writes and leaves the normal one alone.

Trade-offs and pitfalls

• This query surfaces the symptom (near-simultaneous writes), not proof of an actual lost update. Two legitimate rapid writes from the same idempotent retry look identical here; correlating against an actor or session identifier cuts the false-positive rate substantially.
• An audit table retrofitted today only protects you going forward. Past lost updates that happened before the trigger existed left no trace; if you need historical evidence, you're limited to whatever the write-ahead log's retention or a CDC stream already captured.
• A synchronous trigger adds write overhead to every UPDATE (roughly one extra small insert per write). On very high-throughput tables, a CDC stream off the WAL avoids adding synchronous work inside the OLTP transaction at all.
• Detection is not prevention. The actual fix for lost updates is optimistic concurrency (a version column, or an UPDATE ... WHERE updated_at = :expected check on rows-affected) or pessimistic locking (SELECT ... FOR UPDATE) at the point of the read-modify-write cycle. This query is a forensic and monitoring tool, not a substitute for that guard.

Direct answer

Confirm this is genuine write amplification (more physical bytes written per logical write than before, not just more traffic) rather than assume it from the timing alone, then check the two most common migration-triggered causes: a newly added index on a frequently updated column defeating an existing update-in-place optimization, or a row-size change that broke existing free-space assumptions. Protect the primary immediately by removing or throttling whatever is generating the extra writes, not by tuning storage under fire, and confirm the fix worked by re-measuring the same ratio that flagged the problem, not just watching the IOPS (input/output operations per second) graph look better.

Structured elaboration

Glossary

Write amplification: writing more total bytes to physical storage than the logical write itself requires, for example one small UPDATE triggering writes to the table's heap plus several index structures plus the write-ahead log. IOPS (input/output operations per second): a measure of how many discrete disk read or write operations storage is asked to perform.

Confirming it's actually amplification

Compare the rate of logical writes (transactions per second, or rows changed per second) against the rate of physical bytes written (WAL bytes per second, or OS-level disk write throughput). If the logical write rate is flat but physical bytes written per logical write jumped, that's amplification, not simply more traffic.

Common migration-triggered causes, most likely first

• A newly added index on a column that's updated frequently: every update to an indexed column now has to write both the heap row and maintain every index touching it. In Postgres specifically, updating an indexed column disables the heap-only-tuple (HOT) optimization for that update, which otherwise lets an update skip touching indexes entirely when no indexed column changed and the page has free space. This is directly checkable: compare n_tup_hot_upd against n_tup_upd in pg_stat_user_tables; a sudden drop in that ratio right after the migration is a strong, checkable signal, and it's demonstrated concretely below.
• A column type or width change (widening a column, or adding several nullable columns) that increased average row size enough that rows no longer fit in their page's existing free space, forcing more page splits, and combined with a low fill factor, more full-page writes.
• A backfill migration still running in the background, generating sustained extra write-ahead-log volume on top of normal traffic; check whether the spike tracks the backfill's own progress rather than just the moment the schema change deployed.
• Full-page writes after a checkpoint: many engines write a full copy of a page to the log the first time it's touched after a checkpoint, to protect against a torn page on crash. If the migration spread writes over a wider, more random set of pages (for example, via a new index), more distinct pages hit this full-page-write cost following each checkpoint, amplifying log volume independent of any single row's size.

Immediate steps to protect the primary

If the cause is a specific new index that isn't yet load-bearing for query performance, consider dropping it (or marking it unused) as an emergency measure, an explicit, reversible trade made deliberately, not silently. If it's a backfill job, pause it rather than let it keep competing with foreground traffic for the same I/O budget; a well-built backfill should be resumable, and if this one isn't, that's a finding to fix on its own. If the amplification is coming from application write volume itself, throttle at the statement or connection level as a stopgap. Do not reach for "add more IOPS" as the first move; it treats the symptom and hides whether the underlying amplification is actually fixed.

Confirming the mitigation worked

Re-measure the same ratio used to confirm the problem (physical bytes per logical write, and the HOT-update ratio if that was the cause) back at its pre-migration baseline. A lower IOPS graph can simply mean traffic dropped for an unrelated reason (off-peak hours); the underlying amplification can still be broken and will resurface at the next traffic peak.

Worked example

A real, executed demonstration of the HOT-update mechanism specifically, since it's the most directly checkable and most common migration-triggered cause. Postgres 16, a table with free space reserved per page (fillfactor=70) so HOT updates are actually possible:

CREATE TABLE hot_demo (id bigint PRIMARY KEY, note text, counter int NOT NULL DEFAULT 0)
  WITH (fillfactor=70);

3,000 random updates to counter, with no index on it yet:

 n_tup_upd | n_tup_hot_upd | hot_pct
-----------+---------------+---------
      2922 |          2271 |    77.7

77.7% of updates avoided touching any index, exactly what HOT is for. Then an index is added on counter, the very column being updated, statistics reset, and the same 3,000 random updates repeated:

CREATE INDEX idx_hot_demo_counter ON hot_demo (counter);

 n_tup_upd | n_tup_hot_upd | hot_pct
-----------+---------------+---------
      2969 |             0 |     0.0

The HOT-update rate drops from 77.7% to exactly 0.0%. Every single update now has to touch the new index too, because it's on the column changing, real, measured write amplification caused directly by adding an index, on identical data and an identical update pattern, changed by nothing except the index's existence.

Trade-offs and pitfalls

• Dropping a newly added index to relieve write pressure can silently reintroduce the slow-query problem the index was meant to fix. Only do this as a deliberate, communicated, temporary trade, with a plan to rebuild it (ideally online, off-peak) once the amplification's root cause is actually addressed.
• Lowering fill factor helps HOT-update eligibility going forward but does not retroactively fix already-full existing pages; a table that's already bloated usually needs a rebuild to benefit, which carries its own availability trade-offs.
• A backfill migration that isn't resumable and rate-limited is a recurring failure pattern; treat "can this be paused and resumed without redoing work" as a required property of any migration touching every row of a live table, not an afterthought.

Direct answer

I'd put four categories on the dashboard: request-level health (queries per second, latency at the p50 and p99 percentiles, error rate), resource saturation (connections in use, cache hit ratio, lock wait time), replication and durability (replication lag, write-ahead log queue depth, backup freshness), and the specific failure modes that have actually paged people before. For alerting, I set thresholds using rolling windows and a warn/page split, not single static numbers, and I map every page to a first remediation step so the on-call engineer isn't starting from zero at 3am.

Structured elaboration

What goes on the dashboard, and why each one.

If the cluster runs on a storage engine with background compaction (an LSM-tree, log-structured merge-tree: a storage design that buffers writes in memory and periodically merges, or compacts, them into new sorted files on disk instead of updating in place; or a columnar engine, one that stores each column's values together instead of each row, common for BI, business intelligence, and analytics workloads) sitting next to the OLTP (online transaction processing: the primary's regular small, latency-sensitive reads and writes) primary, such as a Citus columnar table (Citus is primarily PostgreSQL's distributed sharding extension; Citus columnar is one table access method within it, added for compressed, append-mostly storage of historical partitions, not the extension's main purpose) or a ClickHouse/TimescaleDB continuous-aggregate sidecar (a separate analytics-oriented database kept in sync with the primary specifically to serve rollup queries without loading the OLTP primary), add compaction backlog and table bloat (wasted, dead space that accumulates in a table or index and isn't automatically shrunk back down) to the list; a growing compaction backlog degrades read latency the same way index bloat does on a classic B-tree engine, just through a different mechanism.

Setting thresholds without drowning in noise.

Three techniques matter more than picking the "right" number:

1. Use rolling windows, not instantaneous values. A single 5-second latency spike from garbage collection or a checkpoint is normal; the same value sustained for 5 minutes is not. Alert on "p99 latency over 500ms for 5 consecutive minutes," not on any single sample crossing 500ms.
2. Split warn from page. Warn (visible on a dashboard, maybe a low-priority ticket) catches trend problems early; page (wakes a human) is reserved for things that are both urgent and actionable right now. Example: cache hit ratio dropping below 95% is a warn (the working set is starting to outgrow memory, worth planning around); dropping below 85% is a page (you're now taking a meaningfully worse I/O hit on a large fraction of reads).
3. Prefer relative and anomaly-aware thresholds over hardcoded absolutes where the baseline varies by time of day or by deployment size. A fixed "QPS > 10,000" alert is wrong for a service whose normal peak is 50,000; a threshold expressed as a deviation from the same hour last week, or a simple rolling z-score, survives normal traffic-shape changes without needing to be hand-tuned every time the service grows.

Concrete threshold examples, with the reasoning attached (not just the numbers):

• Cache hit ratio: warn under 95%, page under 85%. Reasoning: below 95% you're starting to lose the benefit of memory caching on a meaningful slice of reads; below 85% the marginal cost of each additional point drops is large, because you've moved from "occasional disk read" to "disk read is now common."
• Replication lag: warn over 30 seconds, page over 300 seconds (5 minutes). Reasoning: 30 seconds of staleness is tolerable for most read-replica use cases and often self-heals; 5 minutes means a failover right now risks losing up to 5 minutes of committed writes on an asynchronous replica, and reads from that replica are stale enough to be user-visible.
• Long-running transactions: page anything open longer than 10 minutes holding row or table locks. Reasoning: a transaction open that long is either a bug (forgotten COMMIT), a batch job that should be broken into smaller pieces, or actively blocking other work; none of those are things you want to discover only when something else pages.
• I/O operations per second (IOPS) on the storage volume, if the cluster runs on cloud block storage (a virtual disk volume attached to the database server, such as AWS EBS or GCP Persistent Disk, as opposed to object storage): page at 90% of the provisioned IOPS ceiling, sustained for 5 minutes. Reasoning: cloud volumes throttle hard at the ceiling rather than degrading gracefully, so you want the page before you hit the wall, not after latency has already spiked.

Map every page to a first action, not just a threshold. An alert without a next step trains people to acknowledge and go back to sleep. Two of the most common:

• High I/O wait alert fires: first action is to check whether it's a specific query (via the active-session view) versus a broad pattern across all queries (pointing at the underlying storage), and whether a recent deploy or backfill job correlates.
• Replication lag alert fires: first action is to check whether the primary's write volume actually increased (a load-driven cause you may just need to ride out) versus the replica itself being resource-starved (a replica-side problem you can act on directly, like resizing or restarting a stuck apply process).

Worked example

Say the team's baseline is 2,000 QPS with p99 latency normally around 40ms. I'd set: p99 latency warn at 150ms sustained 5 minutes, page at 400ms sustained 5 minutes (roughly 4x and 10x baseline, chosen because a load test showed the application's own request timeout is 500ms, so 400ms is the last point where paging still gives someone time to act before users see timeouts, not after). Connections in use: warn at 70% of max_connections, page at 90%, because pool exhaustion is a hard cliff, not a gradual degradation, so you want the warning well before the cliff. If, after a week of this configuration, the warn-level connection alert has fired forty times with no corresponding incident, that's a signal the threshold is miscalibrated for this workload's normal variance, not that the metric is useless; the fix is widening the window or raising the threshold, not deleting the alert.

Trade-offs and pitfalls

The most common failure mode isn't missing an important metric, it's alerting on too many of them with static thresholds tuned for a different traffic level than the service has grown into, which trains the on-call rotation to ignore pages. The opposite failure, cutting alerts down to "just the essentials," risks the exact regression this question is trying to avoid: rediscovering a real incident an hour late because nothing caught it. Anomaly-aware and relative thresholds reduce false positives but are harder to reason about at 3am ("why did this page, the number doesn't look that bad") than a simple static threshold, so pair them with a runbook line that explains, in plain language, what the alert is actually detecting. A backup-freshness check that only verifies "the job ran" rather than "the resulting backup restores cleanly" is a common blind spot: it will show green right up until the day you actually need it and discover the backup was silently corrupt.

Direct answer

I find the root blocking session first, because in a chain of blocked transactions everyone downstream is just waiting on the one at the top; fixing anyone else does nothing. In PostgreSQL, pg_blocking_pids(pid) does that lookup directly for you. Once I know which session actually holds the lock, I look at what it's doing and how long it's been doing it before choosing between letting it finish, canceling its current statement, or terminating the connection outright, in that order of preference, because each option gets more disruptive.

Structured elaboration

Finding the blocking chain in PostgreSQL. The built-in function pg_blocking_pids(pid) returns the process IDs currently blocking a given session, which is simpler and more reliable than hand-writing a self-join against pg_locks:

SELECT pid,
       application_name,
       wait_event_type,
       wait_event,
       pg_blocking_pids(pid) AS blocked_by,
       state,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

For a manual look at the underlying locks (useful when you want to see the lock mode, not just who's blocked):

SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE relation = 'orders'::regclass
ORDER BY granted, pid;

Finding the blocking chain in MySQL. InnoDB (MySQL's default storage engine) exposes the same shape of information through information_schema (or performance_schema on modern MySQL): SELECT * FROM sys.innodb_lock_waits; in the sys schema view gives a ready-made blocked-versus-blocking pair, and SHOW ENGINE INNODB STATUS includes a TRANSACTIONS section listing the most recent deadlock (a cycle where two or more sessions are each waiting on a lock the other one already holds, so neither can proceed without help, unlike the one-directional chain this whole example is built around) if InnoDB had to resolve one automatically.

Finding the blocking chain in SQL Server. The dynamic management views (DMVs, system views exposing live server state) sys.dm_exec_requests and sys.dm_tran_locks carry the same information under different names: sys.dm_exec_requests.blocking_session_id directly names the blocking session (0 or null means not blocked), and joining to sys.dm_tran_locks on the transaction ID shows the specific lock resource in contention, the same way pg_locks does for PostgreSQL.

Interpreting the lock types you find. A relation-level lock (PostgreSQL) or table-level lock blocks broadly; a tuple or row-level lock is narrower and usually the more common, less disruptive case in day-to-day OLTP (online transaction processing, meaning many small concurrent reads and writes rather than long-running analytical scans) traffic. transactionid as the wait type in PostgreSQL means one transaction is waiting for another to commit or roll back entirely, which is what a plain row-level UPDATE conflict looks like: the second UPDATE isn't waiting on the row's storage, it's waiting on the first transaction's outcome.

Telling a deadlock apart from ordinary blocking. Ordinary blocking, like the chain this whole example walks through, is one-directional: B is stuck behind A, but A itself isn't waiting on anything, so it can sit that way indefinitely, for as long as A holds the lock. A deadlock is a cycle instead: A waiting on a lock B holds while B is simultaneously waiting on a lock A holds, so neither can finish without outside help. PostgreSQL checks for that cycle automatically on a timer (deadlock_timeout, one second by default) and, if it finds one, aborts one of the participating transactions itself with a deadlock detected error. That's the practical tell from pg_stat_activity: a session sitting in pg_blocking_pids() behind a single, non-waiting blocker for more than a second or two is ordinary contention, not a deadlock, because a real deadlock would not survive that long on its own, PostgreSQL would already have broken it. To confirm a deadlock actually happened, look at the logs for the deadlock detected error rather than expecting to catch the cycle live in pg_stat_activity; by the time you query it, the database has usually already resolved it.

Safe remediation, in order of preference.

1. Wait, if the blocking transaction is short-lived and near completion. Most blocking resolves itself in normal operation within milliseconds to a few seconds; only intervene once you've confirmed it's stuck, not just momentarily busy.
2. Cancel the blocking session's current statement, not the connection: SELECT pg_cancel_backend(pid); in PostgreSQL, KILL QUERY <id>; in MySQL. This asks the running statement to stop and roll back, leaving the connection itself alive so the application can retry over the same connection if it's built to do so.
3. Terminate the connection outright only if canceling doesn't clear it (a session can be stuck in a state where it won't respond to a cancel, most commonly waiting on a client-side network read): SELECT pg_terminate_backend(pid); in PostgreSQL, KILL <id>; (as opposed to KILL QUERY) in MySQL. This forcibly closes the connection, which the application will see as a connection error.

Neither option violates data consistency by itself: canceling or terminating a session rolls back its open transaction, exactly the same guarantee the database already gives you for any aborted transaction. The actual risk is application-level, not data-level: whatever partial work that transaction represented needs to be safely retryable, and whoever is holding a connection pool slot (application code borrows an already-open database connection from a shared pool rather than opening a fresh one per request) on that session needs to notice it disappeared and recover cleanly rather than hanging.

Worked example

I reproduced a real blocking chain end to end on a seeded PostgreSQL 16 instance. Session A ran, inside one transaction: BEGIN; UPDATE orders SET status = 'paid' WHERE id = 1; SELECT pg_sleep(15); (holding the row lock open via the sleep, simulating an application that opened a transaction and got stuck before committing). One second later, session B ran UPDATE orders SET status = 'shipped' WHERE id = 1; against the same row. Querying from a third session while both were active:

 pid | application_name | wait_event_type | wait_event    | blocked_by | state  | query
-----+------------------+------------------+---------------+------------+--------+-------------------------------
  92 | order-updater-A  | Timeout          | PgSleep       | {}         | active | ...UPDATE orders SET status='paid'...
  99 | order-updater-B  | Lock             | transactionid | {92}       | active | ...UPDATE orders SET status='shipped'...

pg_blocking_pids(99) returned {92} directly: session B is blocked by session A, full stop, no manual join required. Session A's wait_event_type is Timeout/PgSleep, meaning it isn't blocked on anything else, it's just sitting in pg_sleep(), which is exactly the "stuck application code that forgot to commit" pattern this scenario is modeling. I then ran SELECT pg_cancel_backend(92);. One second later, session 92 had disappeared from pg_stat_activity (its transaction rolled back, since UPDATE plus pg_sleep inside BEGIN never reached COMMIT), and session B's blocked UPDATE went through immediately: querying orders afterward showed status = 'shipped' for that row, confirming B's write landed cleanly with no data corruption, exactly as the "safe remediation" claim above predicts. Note that pg_cancel_backend was sufficient here: A was blocked on its own pg_sleep, which responds to a cancel request, so I never needed the more disruptive pg_terminate_backend.

Trade-offs and pitfalls

Killing the wrong session in a multi-way chain (canceling B instead of A in the example above) accomplishes nothing: B was never holding anything, it was waiting, so the real blocker keeps blocking and you've just added an unnecessary rollback. Canceling a session that's deep into a large multi-statement transaction throws away all of that transaction's work, which is correct from a consistency standpoint but can be expensive in wasted effort if it was 90% done; check how much work is likely to be lost before you cancel, not just how long it's been blocking others. A session that repeatedly shows up as a blocker is a symptom worth escalating past the immediate incident: if the same code path keeps opening a transaction and doing something slow (an external API call, a large batch update) before committing, the fix is shortening that transaction's scope in the application, not learning to cancel it faster every time it recurs.