Re: Change from BIGINT to INT in prod.

On Thu, Nov 5, 2020 at 11:03 AM Walters Che Ndoh <chendohw(at)gmail(dot)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.

David J.