public inbox for [email protected]
help / color / mirror / Atom feedFrom: Lok P <[email protected]>
To: Alban Hertroys <[email protected]>
Cc: Greg Sabino Mullane <[email protected]>
Cc: sud <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Column type modification in big tables
Date: Fri, 16 Aug 2024 02:11:03 +0530
Message-ID: <CAKna9VbVXcD7tR+1V08EJ0j8HVF+HwooZ3bPyiBKpxvg-ZQ3MQ@mail.gmail.com> (raw)
In-Reply-To: <CAKna9VaO9adxGVebe6ySKV3QdH0ZR6N9ksnV==XOWtdT3uThtw@mail.gmail.com>
References: <CAKna9VaJ_qHKBnw4O-VT3xGmzqThCuZ=LFXx-hPdw7E6RoqmeA@mail.gmail.com>
<CAD=mzVUmXmkdvvMG30G1=D4Kq3WqnzGo=0ov9JnRCs1p=KJiTQ@mail.gmail.com>
<CAKna9VZRc4+Vzbt6qPGMCauE84isPtz-wE_KX9AOt7WKfhwjiQ@mail.gmail.com>
<CAD=mzVUX13ZM16kP4QhY+F5XiLr=ezCXftKOTKA4eUvhphgOJw@mail.gmail.com>
<CAKna9Vb_mx+dX02XOV6mpr8RFC-5io38kM6=4xRHQj_MUvQ+aQ@mail.gmail.com>
<CAKAnmmJ6fqyYafLB_im75oxxfTuCLUY0ftBPU57pUm0g+pm6FQ@mail.gmail.com>
<CAKna9VZJ4fginFJZenGQxWs9eAw9Z8g-YkdnOFcie5RvuJ=5OQ@mail.gmail.com>
<CAKAnmmLv72uk1p8+zmWpkC+BTatrdmRe_NpRbwRsi1LAU-cJFQ@mail.gmail.com>
<CAKna9VZGwtNx9NAZ0QjdT-WhtFETAaFzpUsvM6R90mjaAoP3vA@mail.gmail.com>
<CAD=mzVVS6HbV25M7EA+TJYk22G=GJvQK5Gbe9eeifYSmadYtNA@mail.gmail.com>
<CAKna9Vb0ABStAWogCsXK+jTT9ZuLESJ0_-r3Wtvd=rZpYMYwxw@mail.gmail.com>
<CAKAnmm+_avfVEFGgADebEyH=oQrEDuvviOcMYNa+myjJrds8Eg@mail.gmail.com>
<CAKna9Vahx4ow0mtTEbVSeAU+f6U9v6G+Dkr-ymoyNhUZF_GRWw@mail.gmail.com>
<[email protected]>
<CAKna9VaO9adxGVebe6ySKV3QdH0ZR6N9ksnV==XOWtdT3uThtw@mail.gmail.com>
On Fri, Aug 16, 2024 at 2:04 AM Lok P <[email protected]> wrote:
>
> On Thu, Aug 15, 2024 at 9:18 PM Alban Hertroys <[email protected]> wrote:
>
>>
>> > On 15 Aug 2024, at 14:15, Lok P <[email protected]> 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?
>
Additionally , if we are okay with the 7.5hrs of down time , is my
calculation/extrapolation of total time consumption based on a sample
table, for direct alter, accurate? Because, in that case , I was thinking
it's less complex and also less error prone to just do it in a single alter
command rather than going for multiple steps of detach, alter, attach
partition.
view thread (22+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Column type modification in big tables
In-Reply-To: <CAKna9VbVXcD7tR+1V08EJ0j8HVF+HwooZ3bPyiBKpxvg-ZQ3MQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox