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>
In-Reply-To: <CAKAnmm+_avfVEFGgADebEyH=oQrEDuvviOcMYNa+myjJrds8Eg@mail.gmail.com>
From: veem v <veema0000@gmail.com>
Date: Wed, 14 Aug 2024 01:28:24 +0530
Message-ID: <CAB+=1TV0EnXiD3FYnzbgp+zH5mC0oO2SO3sHV7aEr1-2DBXRig@mail.gmail.com>
Subject: Re: Column type modification in big tables
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: Lok P <loknath.73@gmail.com>, sud <suds1434@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f9dafe061f960856"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TV0EnXiD3FYnzbgp%2BzH5mC0oO2SO3sHV7aEr1-2DBXRig%40mail.gmail.com>
Precedence: bulk

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

On Tue, 13 Aug 2024 at 19:39, Greg Sabino Mullane <htamfids@gmail.com>
wrote:

> On Sat, Aug 10, 2024 at 5:06=E2=80=AFPM Lok P <loknath.73@gmail.com> wrot=
e:
>
>
>> 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'v=
e
> given you a low-risk / medium-reward option with check constraints, but f=
or
> the ALTER TABLE options you really need to try it and see (on non-prod).
>
>>
>>
What about if the OP opt a strategy something as below,
1) Detaching the partitions 2)Altering individual partitions with required
column type and length 3)Altering the table 4)Attaching the partitions back
to the main table

This should be faster and also a controlled fashion for each partition
individually.

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

<div dir=3D"ltr"><div dir=3D"ltr"><div><div><br></div></div></div><div clas=
s=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, 13 Aug 2024=
 at 19:39, Greg Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">ht=
amfids@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" =
style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pa=
dding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">On Sat, Aug 10, 2024 at 5=
:06=E2=80=AFPM Lok P &lt;<a href=3D"mailto:loknath.73@gmail.com" target=3D"=
_blank">loknath.73@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote"=
><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px=
 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div di=
r=3D"ltr"><div class=3D"gmail_quote"><div>Can someone through=C2=A0some lig=
ht , in case we get 5-6hrs downtime for this change , then what method=C2=
=A0should=C2=A0we choose for this Alter operation?<br></div></div></div></b=
lockquote><div><br></div><div>We can&#39;t really answer that. Only you kno=
w 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 thi=
s 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 offli=
ne and modify that. I&#39;ve given you a low-risk / medium-reward option wi=
th check constraints, but for the ALTER TABLE options you really need to tr=
y it and see (on non-prod).</div><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex"><br></blockquote></div></div></blockquote><div><br></div>What about=
 if the OP opt a strategy something as below,<div><div>1) Detaching the par=
titions 2)Altering individual partitions=C2=A0with required column type and=
 length 3)Altering the table 4)Attaching the partitions back to the main ta=
ble</div><div><br></div></div><div>This should be faster and also a control=
led fashion for each partition individually.=C2=A0</div></div></div>

--000000000000f9dafe061f960856--