Re: Column type modification in big tables - Mailing list pgsql-general

From Lok P
Subject Re: Column type modification in big tables
Date
Msg-id CAKna9VaO9adxGVebe6ySKV3QdH0ZR6N9ksnV==XOWtdT3uThtw@mail.gmail.com
Whole thread Raw
In response to Re: Column type modification in big tables  (Alban Hertroys <haramrae@gmail.com>)
Responses Re: Column type modification in big tables
List pgsql-general

On Thu, Aug 15, 2024 at 9:18 PM Alban Hertroys <haramrae@gmail.com> wrote:

> On 15 Aug 2024, at 14:15, Lok P <loknath.73@gmail.com> wrote:

(…)
Are all those partitions critical, or only a relative few?

If that’s the case, you could:
        1) detach the non-critical partitions
        2) take the system down for maintenance
        3) update the critical partitions
        4) take the system up again
        5) update the non-critical partitions
        6) re-attach the non-critical partitions

That could shave a significant amount of time off your down-time. I would script the detach and re-attach processes first, to save some extra.


Thank you so much. 

The partition table which we are planning to apply the ALTER script is a child table to another big partition table. And we have foreign key defined on table level but not partition to partition. So will detaching the partitions and then altering column of each detached partition and then re-attaching will revalidate the foreign key again? If that is the case then the re-attaching partition step might consume a lot of time. Is my understanding correct here?

pgsql-general by date:

Previous
From: Alban Hertroys
Date:
Subject: Re: Column type modification in big tables
Next
From: Lok P
Date:
Subject: Re: Column type modification in big tables