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>
In-Reply-To: <CAKna9Vb0ABStAWogCsXK+jTT9ZuLESJ0_-r3Wtvd=rZpYMYwxw@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Tue, 13 Aug 2024 10:08:52 -0400
Message-ID: <CAKAnmm+_avfVEFGgADebEyH=oQrEDuvviOcMYNa+myjJrds8Eg@mail.gmail.com>
Subject: Re: Column type modification in big tables
To: Lok P <loknath.73@gmail.com>
Cc: sud <suds1434@gmail.com>, pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000006f1dff061f91280e"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmm%2B_avfVEFGgADebEyH%3DoQrEDuvviOcMYNa%2BmyjJrds8Eg%40mail.gmail.com>
Precedence: bulk

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

On Sat, Aug 10, 2024 at 5:06=E2=80=AFPM Lok P <loknath.73@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 completio=
n
> 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

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

<div dir=3D"ltr"><div dir=3D"ltr">On Sat, Aug 10, 2024 at 5:06=E2=80=AFPM L=
ok P &lt;<a href=3D"mailto:loknath.73@gmail.com">loknath.73@gmail.com</a>&g=
t; wrote:</div><div class=3D"gmail_quote"><div>=C2=A0</div><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid r=
gb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_quot=
e"><div>Can someone through=C2=A0some light , in case we get 5-6hrs downtim=
e for this change , then what method=C2=A0should=C2=A0we choose for this Al=
ter operation?<br></div></div></div></blockquote><div><br></div><div>We can=
&#39;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 l=
atter item, your best bet is to try this out on the same/similar hardware a=
nd 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&#39;ve given y=
ou a low-risk / medium-reward option with check constraints, but for the AL=
TER TABLE options you really need to try it and see (on non-prod).</div><di=
v><br></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">it seems the =
&quot;USING&quot; clause takes more time as compared to normal ALTER. But a=
gain I don&#39;t see any way to see the progress and estimated completion t=
ime. Can you share your thoughts on this?</blockquote><div><br></div><div>T=
here should be no difference if they are doing the same conversion.</div><d=
iv><br></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">=C2=A0Will t=
his approach be faster/better as compared to the simple &quot;alter table a=
lter column approach&quot; as above</blockquote><div><br></div><div>Seems a=
 lot more complicated to me than a simple ALTER. But measurement is key. Cr=
eate a new test cluster using pgBackRest or whatever you have. Then run you=
r ALTER TABLE and see how long it takes (remember that multiple columns can=
 be changed in a single ALTER TABLE statement).=C2=A0</div><div><br></div><=
div>Cheers,</div><div>Greg</div><div><br></div></div></div>

--0000000000006f1dff061f91280e--