A slow database, a waiting migration and the evidence between them

“The database is slow after a deployment” describes a sequence of events. It does not explain the failure. Before approving a fix, an engineer needs to know what is waiting, what it is waiting on and why the proposed change will release it.

Consider this illustrative PostgreSQL incident: order requests reach 8.4 seconds at p99, the outbox stops delivering events and database CPU remains at 19%. A schema migration started just before the alert. These are invented example observations, not customer results.

Separate the symptom from the mechanism

The slow request spends almost all its time in a database span. That narrows the investigation, but a long span can include several kinds of waiting. Check connection acquisition, query execution and lock waits separately.

In this case, the waiting reads point to migration session 2317. That migration is itself waiting for reporting session 1284, which has an open transaction and a table lock. The migration requests an AccessExclusiveLock. Later reads can queue behind it even though it has not acquired the lock yet. PostgreSQL calls this a soft block: a conflicting waiter ahead in the queue can block a later request. PostgreSQL documents both hard and soft blockers.

The chain to establish is:

Reporting transaction holds a table lock
  → schema migration waits for an exclusive lock
    → later application reads wait behind the migration
      → order requests and outbox delivery stall

Low CPU is consistent with this explanation, but it does not prove it. The blocking relationships do the important work.

Capture the queue before changing it

Start with a known affected backend PID from your database tooling. The following read-only query shows that session’s immediate blockers. Replace the example PID, run it against the relevant database and use a role authorized to inspect activity. Some session fields are restricted without additional monitoring privileges. See the PostgreSQL activity-view documentation.

SELECT
  a.pid,
  a.application_name,
  a.state,
  a.wait_event_type,
  a.wait_event,
  now() - a.xact_start AS transaction_age,
  pg_blocking_pids(a.pid) AS blocked_by
FROM pg_stat_activity AS a
WHERE a.datname = current_database()
  AND a.pid = 2317;

Follow the returned blocker PIDs in a second observation. Record when each sample was taken. This is a changing system, so separate queries are not an atomic snapshot of the entire lock chain. Avoid turning the diagnostic into a tight polling loop; pg_blocking_pids briefly accesses shared lock-manager state.

Use pg_locks and the activity details to confirm the relation and requested lock modes. An old transaction alone is insufficient. You need the relationship between that transaction, the queued migration and the affected reads. The locking documentation describes which modes conflict.

Make each finding change the next check

The infrastructure investigation identifies the blocking chain. That should redirect telemetry toward the other callers of the same table. In this example, the outbox publisher’s database waits match the migration session, explaining why order delivery stopped alongside the API.

The code investigation then checks the migration runner. Does it bound lock acquisition? What happens after a timeout or cancellation? If it retries indefinitely, cancelling one waiting statement may only remove the queue until the runner tries again.

A concise evidence record would contain:

• Observed: affected reads wait behind session 2317, which waits behind session 1284.
• Observed: the outbox reads the same relation and stops advancing during that window.
• Observed: the deployed runner retries the migration without a bounded lock wait.
• Inferred: removing the queued DDL and preventing immediate retries should allow the affected reads to proceed.
• Still to check: whether another lock or downstream failure will remain after that queue clears.

Each observation needs a source and a time. Keep the inference distinguishable from what was directly measured.

Write down what would disprove the diagnosis

If affected requests still wait after the queued migration is gone, this explanation is incomplete. If the outbox has no matching database wait, investigate its own failure path. A nearby deployment that does not touch the affected path is weak evidence by itself.

These are useful checks before and after an action. They prevent a plausible narrative from becoming the only explanation the team considers.

Verify recovery and test the permanent fix

Review the recovery plan with the owner of the migration. Recheck the session identity and state before any intervention. A PID copied from an earlier screenshot is not a durable action target. This article is a diagnostic walkthrough, not a blanket instruction to terminate sessions.

For prevention, test a local lock timeout and bounded retry policy under the same contention. PostgreSQL’s lock_timeout limits waiting to acquire locks. It is not an overall query-runtime limit. The configuration reference explains the distinction.

A useful regression holds the reporting transaction open, starts the migration and attempts an application read. Verify that the runner exits or retries within the intended bounds and does not repeatedly stall reads. Then check that the migration succeeds when contention is removed.

Recovery evidence should include the original request latency, the lock queue and outbox progress. Save the failed condition and the regression with the incident so the next schema change starts with a check your team has already learned to need.