The Curious Case of the Table-Locking UPDATE Query

Summary

The post recounts a PostgreSQL performance incident in which metadata processing stopped as jobs accumulated and an UPDATE targeting a single UUID remained active for days. Diagnostics showed the query consuming more than 80% of database time, while manual execution did not reproduce the delay. A pg_stat_activity and pg_locks query exposed many waiting statements and an AccessExclusiveLock entry whose locktype was tuple, leading the investigation toward PostgreSQL row locking and contention. The eventual cause was threaded code that issued concurrent updates to the same row for each job; removing that concurrency, aborting old backends with pg_cancel_backend, and deploying the change restored processing and began churning through the backlog. A correction notes that the reported lock was on a tuple rather than the table, and that PostgreSQL does not have lock promotion as suggested in the debugging discussion.

Takeaways

1. 1

   The reported UPDATE was shown in diagnostics as running for 5 days 18:01:26.446979, and heroku pg:outliers attributed more than 80% of database time to it.

2. 2

   Each job created a concurrent promise that could update the same database row; sequential updates reduced the chance of same-row contention, at the cost of less efficient Ruby-side I/O use.

3. 3

   The correction states that the lock was on a tuple, not the table, and that PostgreSQL does not have lock promotion as suggested in the original debugging discussion.