On Sat, Aug 10, 2024 at 5:06 PM Lok P <loknath...@gmail.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
