Re: Column type modification in big tables

On Sat, Aug 10, 2024 at 5:06 PM Lok P <loknath(dot)73(at)gmail(dot)com> wrote:

> Can someone through some light , in case we get 5-6hrs downtime for this
> change , then what method should we choose for this Alter operation?
>

We can't really answer that. Only you know what resources you have, what
risk/reward you are willing to handle, and how long things may take. For
that latter item, your best bet is to try this out on the same/similar
hardware and see how long it takes. Do a smaller table and extrapolate if
you need to. Or promote one of your replicas offline and modify that. I've
given you a low-risk / medium-reward option with check constraints, but for
the ALTER TABLE options you really need to try it and see (on non-prod).

it seems the "USING" clause takes more time as compared to normal ALTER.
> But again I don't see any way to see the progress and estimated completion
> time. Can you share your thoughts on this?

There should be no difference if they are doing the same conversion.

Will this approach be faster/better as compared to the simple "alter table
> alter column approach" as above

Seems a lot more complicated to me than a simple ALTER. But measurement is
key. Create a new test cluster using pgBackRest or whatever you have. Then
run your ALTER TABLE and see how long it takes (remember that multiple
columns can be changed in a single ALTER TABLE statement).

Cheers,
Greg