On Thu, Nov 5, 2020 at 11:03 AM Walters Che Ndoh <chendohw@gmail.com> wrote:
Dear All,
I am trying to change some specific columns on some tables in prod and looking at the best ways to do it with very minimal downtime.
So when i try running alter table table_name alter column column_name type INT; , it is hanging and starts providing a lock on the table.
I'm not seeing any practical difference between the table being locked and the table being empty. I'm not seeing the point of doing this specific conversion at all really - and without understanding how these tables fit into the bigger scheme of things it is difficult to provide useful suggestions.
So my question is will it be a good idea to bring back up the DB with applications connecting and same time restoring the data from those specific tables?
If this is not a good idea...any suggestions on how i can make these changes to the LIVE DB with minimal downtime?
IMO this is impossible to answer generically. Downtime is probably not the only measure you care about - if queries start taking 10 times as long to complete than before, but you are still "up", you may very well still have issues.
If you cannot avoid doing this marginally useful exercise consider whether it can be done in stages. Setup things so the new state and the current state can run concurrently and then slowly move records from the current state to the new one. Triggers and views can help here.