Update: On closer inspection, the lock type was not on the table, but on a tuple. For more information on this locking mechanism see the internal Postgresql tuple locking documentation. Postgres does not have lock promotion as suggested in the debugging section of this post.

I maintain an internal-facing service at Heroku that does metadata processing. It's not real-time, so there's plenty of slack for when things go wrong. Recently I discovered a Postgres performance issue that bogged down the system to the point where no jobs were being executed at all. After hours of debugging, I found that an UPDATE on a single row on a single table was causing the entire table to lock,...

Setting up a database is a relatively straightforward process (Heroku has an add-on for that), but getting it to run well in production is sometimes another matter. As your application grows and your data grows along with it, you will likely find a number of performance bottlenecks specifically related to your database, and this post aims to help you diagnose and address those issues when they arise.

As with all components of your infrastructure it’s important to have early visibility into the performance characteristics of your database. Watching this data as your application grows will give you a much better chance of spotting performance issues and regressions as they’re introduced.

I...