we have table that being used for sending sms and other notification for confirmation as second factor.

Some changes in business logic requires us to alter table to make some columns NOT NULL.

Our stumbling-stone is that table should be accessed during 24/7, application writes to and reads from it pretty frequently and table has tens of millions rows. And when command like

alter column C1 int null

starting to execute it obviously tries to lock table and seemingly cannot do it because table is being used by other requests. That situation end up with that alter command hangs out and nothing happened during 5 minutes. After that timeout we stop it because do not want occasionally crush our system.

Any thoughts about how we can implement this modification without stopping our system?


1 Answer 1

  1. Announce that over the weekend there will be a planned maintenance event where new notifications will be delayed for X hours.
  2. Make a copy of the table. Redirect all reads there.
  3. Disable any writes from the application or queue them up if that's unacceptable.
  4. Alter the table.
  5. Apply any queued up writes.
  6. Set everything back.

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.