0

I'm not pretty sure it is database question or development-process question, but I will kindly give it a try.

When creating a new table B with foreign keys to table A, the operation tries to acquires SHARE ROW EXCLUSIVE lock on the referenced table (table A). However if table A is being busy by various UPDATES (ROW EXCLUSIVE locks). This cause my database migration locks for too long time (15+ minutes) and risk my production. In Some occasions I manually assist my db migration by killing the workers directed to table A.

Beside scheduling almost every db migration to occurs in the off-hours (1AM-5AM), can you suggest a better or creative solution to help my team deploy "naive" PR to production.

References:

  1. https://www.postgresql.org/docs/12/sql-createtable.html
  2. https://www.postgresql.org/message-id/75218696-61be-4730-89f6-dd6058fa9eda@a28g2000prb.googlegroups.com
5
  • Why do the "various updates" never finish even within 15 minutes? Maybe the solution is to run those between 1 and 5.
    – jjanes
    Nov 5, 2021 at 2:34
  • It means that during the peak hours my system spawn tens/hundreds of pods that modify the data of table A. I’m guessing here but I think my db migration is waiting in a long queue to start, and this queue is full with other statements of my working pods.
    – Cowabunga
    Nov 5, 2021 at 8:34
  • Your wording suggests you expect to create new tables with foreign keys to existing tables regularly under the name ‘db migration'. If you’re just adding data to your existing tables then what’s the need for the foreign key? If you’re doing this frequently, do you really need to create a new table each time? Nov 5, 2021 at 8:57
  • Adding more context: we are a team of developers building a pipeline of video processing and extraction. I can say that once a week we do django "db migration" (DDL statements), more rarely, about 3-4 weeks we add new table (maybe for new feature or so). During that time we notice that the migration process hangs. Being nervous as we are, we start terminating other processes to mitigate the issue and enable the migration to finish successfully. A good example was from 5 days ago, when we introduce a new empty table with 2 foreign keys to 2 busy tables. This operation took 15 minutes to finish
    – Cowabunga
    Nov 5, 2021 at 10:16
  • If there is a long queue of blocked processes (which itself seems pretty abnormal for a production system not in a maintenance window), why does adding one more "risk my production"?
    – jjanes
    Nov 5, 2021 at 15:23

1 Answer 1

1

It is a problem if you always have long running data modifying transactions on your database. Your diagnosis is correct, and that blocks trigger creation.

To mitigate the negative effects, you could set lock_timeout in the session where you create the trigger:

SET lock_timeout = '10s';
CREATE TRIGGER ...;

That will prevent the statement from waiting for more than ten seconds. You have to retry the operation until you succeed.

Long running transactions cause other problems than that:

  • they block concurrent queries, if they want to modify the same rows

  • they increase the likelihood of a deadlock

  • they prevent autovacuum from removing old row versions, thus potentially bloating database tables

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.