MIME-Version: 1.0
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> <D8602AF7-7EDE-46EC-B12F-5AE003969994@gmail.com>
In-Reply-To: <D8602AF7-7EDE-46EC-B12F-5AE003969994@gmail.com>
From: Lok P <loknath.73@gmail.com>
Date: Fri, 16 Aug 2024 02:04:47 +0530
Message-ID: <CAKna9VaO9adxGVebe6ySKV3QdH0ZR6N9ksnV==XOWtdT3uThtw@mail.gmail.com>
Subject: Re: Column type modification in big tables
To: Alban Hertroys <haramrae@gmail.com>
Cc: Greg Sabino Mullane <htamfids@gmail.com>, sud <suds1434@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000cecffd061fbec67f"
Archived-At: <https://www.postgresql.org/message-id/CAKna9VaO9adxGVebe6ySKV3QdH0ZR6N9ksnV%3D%3DXOWtdT3uThtw%40mail.gmail.com>
Precedence: bulk

--000000000000cecffd061fbec67f
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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

>
> > On 15 Aug 2024, at 14:15, Lok P <loknath.73@gmail.com> wrote:
>
> (=E2=80=A6)
> Are all those partitions critical, or only a relative few?
>
> If that=E2=80=99s 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?

--000000000000cecffd061fbec67f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Thu, Aug 15, 2024 at 9:18=E2=80=AFPM A=
lban Hertroys &lt;<a href=3D"mailto:haramrae@gmail.com">haramrae@gmail.com<=
/a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0=
px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><=
br>
&gt; On 15 Aug 2024, at 14:15, Lok P &lt;<a href=3D"mailto:loknath.73@gmail=
.com" target=3D"_blank">loknath.73@gmail.com</a>&gt; wrote:<br>
<br>
(=E2=80=A6)<br>Are all those partitions critical, or only a relative few?<b=
r>
<br>
If that=E2=80=99s the case, you could:<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 1) detach the non-critical partitions<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 2) take the system down for maintenance<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 3) update the critical partitions<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 4) take the system up again<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 5) update the non-critical partitions<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 6) re-attach the non-critical partitions<br>
<br>
That could shave a significant amount of time off your down-time. I would s=
cript the detach and re-attach processes first, to save some extra.<br><br>=
</blockquote><div><br></div><div>Thank you so much.=C2=A0</div><div><br></d=
iv><div>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=C2=A0=
key defined=C2=A0on table level but not partition to partition. So will det=
aching the partitions and then altering column of each detached partition a=
nd 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 m=
y understanding correct here?</div></div></div>

--000000000000cecffd061fbec67f--