Postgresql alter table set value11/11/2023 The query in question was a simple update, and it's not even on the most massive table in the DB. It showed that I had queries that had been running for DAYS: 68698 5 days 18:01:26.446979 UPDATE "table" SET WHERE ("uuid" = '') The first thing I did was run heroku pg:diagnose, which shows "red" (critical) and "yellow" (important but less critical) issues. My best guess was the database where the results were being stored was having problems. I checked our datastores, and they were well under their limits, I checked our error tracker and didn't see any smoking guns. The system has been running in production for years, and while there have been occasional performance issues, nothing stood out as a huge problem. I started debugging when the backlog on our system began to grow, and the number of jobs being processed fell to nearly zero. This post is a story about how the problem was debugged and fixed and why such a seemingly simple query caused so much harm. After hours of debugging, I found the problem was an UPDATE on a single row on a single table was causing the entire table to lock, which caused a lock queue and ground the whole process to a halt. Recently I discovered that the system was getting bogged down to the point where no jobs were being executed at all. It's not real-time, so there's plenty of slack for when things go wrong. I maintain an internal-facing service at Heroku that does metadata processing. Postgres does not have lock promotion as suggested in the debugging section of this post. ![]() ![]() ![]() For more information on this locking mechanism see the internal Postgresql tuple locking documentation. Update: On closer inspection, the lock type was not on the table, but on a tuple.
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |