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>
In-Reply-To: <CAD=mzVVS6HbV25M7EA+TJYk22G=GJvQK5Gbe9eeifYSmadYtNA@mail.gmail.com>
From: Lok P <loknath.73@gmail.com>
Date: Sun, 11 Aug 2024 02:36:42 +0530
Message-ID: <CAKna9Vb0ABStAWogCsXK+jTT9ZuLESJ0_-r3Wtvd=rZpYMYwxw@mail.gmail.com>
Subject: Re: Column type modification in big tables
To: sud <suds1434@gmail.com>, Greg Sabino Mullane <htamfids@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000bd5a56061f5aa3de"
Archived-At: <https://www.postgresql.org/message-id/CAKna9Vb0ABStAWogCsXK%2BjTT9ZuLESJ0_-r3Wtvd%3DrZpYMYwxw%40mail.gmail.com>
Precedence: bulk

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

On Sat, Aug 10, 2024 at 5:47=E2=80=AFPM sud <suds1434@gmail.com> wrote:

>
>
> My 2cents.
> If you have enough time then from a simplicity point of view, your single
> line alter command may look good, but how are you going to see  the amoun=
t
> of progress it has made so far and how much time it's going to take to
> finish. And you got ~6hrs of down time but if it fails at 5th hour then y=
ou
> will be in a bad position.
>
>
>
Does it mean that , if we get enough downtime then , we should rather go
with the option of recreating the table from scratch and populating the
data from the existing table and then rename it back? It does look more
complicated considering many steps like creating indexes, constraints back
and renaming it and then running vacuum and analyze etc.

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?

--000000000000bd5a56061f5aa3de
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 Sat, Aug 10, 2024 at 5:47=E2=80=AFPM s=
ud &lt;<a href=3D"mailto:suds1434@gmail.com">suds1434@gmail.com</a>&gt; wro=
te:<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"><div dir=3D"=
ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div dir=3D"ltr"=
 class=3D"gmail_attr"><br></div><div>My 2cents.=C2=A0</div><div>If you have=
 enough time then from a simplicity point of view, your single line alter c=
ommand may look good, but how are you going to see=C2=A0 the amount of prog=
ress it has made so far and how much time it&#39;s going to take to finish.=
 And you got ~6hrs of down time but if it fails at 5th hour then you will b=
e in a bad position.=C2=A0</div><div><br></div><div><br></div></div></div><=
/blockquote><div>=C2=A0</div><div>Does it mean that , if we get enough=C2=
=A0downtime then , we should rather go with the option of recreating the ta=
ble from scratch and populating the data from the existing table and then r=
ename it back? It does look more complicated considering many steps like cr=
eating indexes, constraints back and renaming it and then running vacuum an=
d analyze etc.=C2=A0</div><div><br></div><div>Can someone through=C2=A0some=
 light , in case we get 5-6hrs downtime for this change , then what method=
=C2=A0should=C2=A0we choose for this Alter operation?</div></div></div>

--000000000000bd5a56061f5aa3de--