Three Postgres lock convoys that were eating half our crawl fleet

Postgres lock contention debugging in practice: three convoys that stalled half our crawl fleet, the sampling loop that found them, and the fixes.

Our crawl fleet exists to feed the index behind the AIDocument API: over 1.2 billion documents across more than 127 million domains, with the live counters at /web-index/stats. The economics of that index rest on one property: one crawl serves every caller. A crawler fetches a page once, the page is indexed, and every reader after that gets it from the index instead of fetching and parsing the page themselves. Which means the fleet's throughput sets the index's freshness, and anything that stalls the fleet stalls everything downstream.

Every crawler in that fleet reports its work to a central Postgres instance, the frontier, which owns the crawl queue: mark this URL done, mark that one for retry, skip everything under this domain. Those status writes run under a 5-second statement deadline, on the theory that a crawler stuck on bookkeeping is a crawler that is not crawling. This is the story of the week that deadline started killing us: about half of all status writes, fleet wide, dying at 5 seconds, against a database that looked perfectly healthy.

An idle database and a starving fleet

The dashboards were contradictory in the way that usually means you are measuring the wrong thing. On the fleet side: mark-done and mark-retry calls timing out at their deadlines, roughly one in two, on every box at once. On the database side: 16 cores sitting 88 percent idle, disk writes completing in under a millisecond, and single snapshots of pg_stat_activity showing little beyond idle connections.

We walked the usual suspects. CPU saturation: no, the box was bored. IO: no, sub-millisecond writes. Connection exhaustion: no, plenty of headroom. Lock contention: we ran the standard pg_locks joins a few times and got nothing conclusive, a blocked pid here and there that was gone by the next look.

That last part was the tell, and we misread it at first. A single snapshot of pg_stat_activity is one photograph of a highway: you can easily catch the gap between two waves of traffic and conclude the road is empty. The waits killing our fleet were real, but each one resolved or timed out within seconds, so any individual query against the activity view had decent odds of landing between them.

Sample, do not snapshot

What cracked it was almost embarrassingly simple: a loop. Once per second, for 15 to 30 seconds, query pg_stat_activity, keep only active queries older than 300 milliseconds, and print the wait state and who is blocking whom.

while true; do
  psql -X -c "
    SELECT to_char(now(), 'HH24:MI:SS')                        AS t,
           pid,
           wait_event_type,
           wait_event,
           pg_blocking_pids(pid)                               AS blocked_by,
           (extract(epoch FROM now() - query_start))::int      AS age_s,
           left(regexp_replace(query, '\s+', ' ', 'g'), 90)    AS query
    FROM pg_stat_activity
    WHERE state = 'active'
      AND now() - query_start > interval '300 milliseconds'
    ORDER BY query_start;
  "
  sleep 1
done

Thirty seconds of that output taught us more than a day of snapshots had. Instead of one frame we had a film, and the film showed three distinct patterns repeating. One statement shape was always there, churning. A second appeared in episodes: bursts of rows with wait_event_type = Lock, all pointing at the same blocking pid. And our own mark-done statements kept showing up among the victims of the second. Three patterns, three causes, in escalating order of cost.

Convoy 1: the OR that poisoned the index

The always-there statement was bulk maintenance. When a domain becomes skip-worthy (a robots change, an owner request, a quality rule), a trigger condition fires a bulk UPDATE that retires everything queued under that domain. Its WHERE clause looked harmless:

UPDATE crawl_queue
SET    status = 'skipped'
WHERE  status = 'pending'
  AND (domain = $1 OR domain LIKE '%.' || $1);

Equality on the domain, or a suffix match to catch subdomains. Two problems, and they compound.

First, a LIKE pattern that starts with a wildcard can never use a btree index. A btree is sorted left to right, and '%.example.com' gives it nothing to seek on. Second, and this is the part that surprises people, the OR poisons the indexable arm too. When every arm of an OR can use an index, the planner can combine bitmap scans. When one arm cannot, there is no way to satisfy the predicate without visiting every row, so the whole thing falls back to a sequential scan, indexable arm included.

Our pending set was 31 million rows. Every call to this statement scanned all of them, burning 1 to 5 seconds of CPU per call.

Here is the vicious part. The callers ran this under the same shared 5-second deadline as everything else. A big batch could not finish in 5 seconds, so the statement was cancelled and rolled back, none of the rows got retired, the trigger condition re-accumulated, and the next cycle fired the same statement again. The scan was not occasional. It was continuous, a permanent tenant on a core. And a single core running flat out is nearly invisible on a 16-core utilization graph: that is the 88 percent idle right there, hiding a query that never stops.

The fix was already sitting in our schema. We had a precomputed registrable-domain column (the eTLD+1, so blog.example.com and www.example.com both store example.com), maintained at insert time, with a partial index over pending rows. Equality on that column expresses "this domain or any subdomain" exactly, with no LIKE and no OR:

UPDATE crawl_queue
SET    status = 'skipped'
WHERE  status = 'pending'
  AND  registrable_domain = $1;

Index seek, done in milliseconds. And bulk maintenance got its own 60-second deadline instead of borrowing the hot path's 5. A deadline is a promise about latency, and bulk work and interactive work should never share one.

Convoy 2: the reconciler holding a million locks

The episodic pattern pointed somewhere else. Every few minutes the sampler filled with rows waiting on Lock, and pg_blocking_pids named the same pid for all of them. That pid belonged to a periodic counter reconciler: a job that recomputes per-domain counters (queued, in flight, done) from ground truth and upserts the corrected values.

It upserted millions of rows in one transaction.

In Postgres, a row lock taken by an UPDATE or an upsert is held until the transaction commits or rolls back. Not until the statement finishes: until the transaction ends. So a reconciler that touches millions of counter rows in a single transaction is, functionally, a slowly expanding lock across the whole counter table. Every hot-path statement that ran SELECT ... FOR UPDATE on a counter row the reconciler had already visited joined a convoy behind it. We measured waits of 15 to 33 seconds in the sampler output. Against a 5-second deadline, those are not delays. They are guaranteed deaths.

The fix was to change the hot path's relationship to that lock. The counters are advisory: they steer scheduling, and the reconciler corrects any drift on its next tick. When a value self-heals by design, waiting for it is the wrong trade. So the hot path now takes the row with FOR UPDATE NOWAIT and treats a lock failure as ordinary weather:

SELECT queued, in_flight
FROM   domain_counters
WHERE  registrable_domain = $1
FOR UPDATE NOWAIT;

If the row is locked, Postgres raises SQLSTATE 55P03 (lock_not_available) immediately instead of queueing. The caller catches exactly that code, defers the counter touch, and moves on to real work. Nobody waits behind the reconciler anymore, because nobody is willing to.

Chunking the reconciler's transaction would help too, but the NOWAIT change is the more durable fix: it protects the hot path from every long transaction, including the ones we have not written yet.

Convoy 3: the CTE that un-crawled pages

The third pattern is the one that cost real money. Our mark-done statement, the single hottest write in the system, bundled two jobs into one CTE: flip the queue row to done, and credit a per-domain reputation counter that feeds scheduling.

WITH done AS (
  UPDATE crawl_queue
  SET    status = 'done', finished_at = now()
  WHERE  id = $1
  RETURNING registrable_domain
)
INSERT INTO domain_reputation (registrable_domain, success_count)
SELECT registrable_domain, 1 FROM done
ON CONFLICT (registrable_domain)
DO UPDATE SET success_count = domain_reputation.success_count + 1;

One round trip, tidy, and it reads like good engineering. It is a trap. A statement in Postgres is atomic: it succeeds or fails as a unit, CTEs included. When the reputation arm blocked behind the reconciler's locks (convoy 2), the whole statement sat in the convoy until the 5-second deadline cancelled it, and the cancellation rolled back the part that had already executed. The status flip. The one job that actually mattered.

The consequence: pages we had successfully fetched, rendered, and stored went back into the queue as if nothing had happened, and the fleet crawled them again. From the logs this was invisible. The timeout looked like any other timeout, and the recrawl looked like ordinary work. But every redundant fetch was a real request against a real origin, from a crawler that identifies itself and budgets its request rate per site, so we were spending our own capacity and the origin's goodwill to reacquire bytes we already had.

The fix is a rule we now apply everywhere: never couple a critical write to a best-effort write in one statement. The flip became its own tiny statement that touches only the queue table, and it commits in milliseconds regardless of what the counters are doing. The reputation credit became a separate best-effort statement that takes the counter row with SKIP LOCKED; when the row is unavailable, it logs the dropped credit and returns success, and the failure is never propagated as an error. A dropped credit costs a rounding error in scheduling accuracy. A rolled-back flip costs a full recrawl.

What changed

That last row is a 5.4x recovery, and it came from zero new hardware, zero config tuning, and zero added capacity. The database had been idle the entire time. The bottleneck was the shape of our SQL, three statements out of hundreds, and the only reason we found them is that we stopped taking photographs and started filming.

What we would tell our past selves

• An idle database is not a healthy database. Low CPU and fast disks tell you the server is not saturated; they say nothing about whether your statements are waiting in line.
• Sample, do not snapshot. A once-per-second loop over pg_stat_activity printing wait_event_type, wait_event, and pg_blocking_pids for 30 seconds beats any single query. Waits that live for seconds are invisible to a tool that looks once.
• OR plus a leading-wildcard LIKE is a plan killer. One unindexable arm drags the whole predicate into a sequential scan. Precompute the thing you actually mean (for us, the registrable domain) and compare with equality.
• One big transaction is a lock bomb with a timer. Row locks live until commit, so a million-row transaction is a million held locks, and everything touching those rows convoys behind it.
• Never couple a critical write to a best-effort write in one statement. Statements are atomic, so the optional arm's failure rolls back the mandatory arm's success.
• Give bulk maintenance its own deadline. A 5-second deadline sized for the hot path turns a slow batch job into an infinite loop that never commits.

The fleet in this story feeds every AIDocument we serve. If you would rather read the web from an index that has already fought these fights than run your own crawler and meet your own convoys, sign up for the free tier: 2,000 AIDocuments a month, no card required, and the quickstart takes about five minutes.