Engineering story · FAFO Core and Function Forge

196 deadlocks to zero

Measuring a write path from the database itself, and finding that one index was the whole problem

PostgreSQLPython benchmark harnessRust write path
Two instruments · one of them tells the truth
The queue
counts intake: "throughput fine"
PostgreSQL tuple deltas
counts committed rows: a crawl
change one thing, run again, compare the two reports
1 indexa unique key on the update column
  • 196 → 0deadlocks during the run, before and after one index
  • 1.25 Mrows committed in 185 seconds after the fix
  • 16,370peak rows committed a second; 6,752 on average

A bulk update workload through my shared write path had slowed to a crawl, with deadlocks every few seconds. The queue in front of the database reported healthy throughput, which was the first clue that the queue was the wrong instrument. I built a harness that measured from the only place that knows how many rows were committed, the database itself, and compared runs before and after a single schema change.

The challenge

The write path is shared by every producer in the estate, so a slow write path slows everything. The obvious metrics were the queue's own counters, and they measure intake: how much work was taken in, and nothing about how much landed. A diagnosis built on intake would have optimised the wrong thing.

Constraints

Shared infrastructure, so the write path could not be taken offline to test it. Ground truth had to be the database; anything else was a proxy.

My approach

Sample the system, the queue and the database every few seconds during a controlled run, and treat PostgreSQL's own tuple statistics as the truth about committed work. Run the workload, change one thing, run it again, and compare the two reports.

One batch, read by three instrumentsQueue counters · work taken in at intakeSystem load, sampled on an interval · context for the runPostgreSQL tuple deltas and commit counts · rows actually committed
proportions illustrative

What I built

A benchmark harness that samples system load, queue depth and PostgreSQL tuple deltas on a fixed interval and renders a before-and-after report.

The fix, which turned out to be a unique index on the update key of the target table. The missing index was forcing the engine into lock patterns that collided under concurrent batches.

Difficult decisions

Rejecting the queue's numbers. The queue said throughput was fine.

Only the database knows how many rows were committed.

Results

Bulk-updating a cluster-health metrics table on my primary host:

Batch latency, on one 0 to 2,000 ms scale Before: 100 to 2,000 ms
After: 7 to 37 ms

Technologies used

PostgreSQL tuple statistics, index design and lock behaviour · Python benchmark tooling · Rust write-path service

What this demonstrates

Disciplined performance diagnosis: choose the source of truth before measuring, change one thing, and let the comparison find the answer. Sometimes the answer is one index.