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: Lok P <loknath.73@gmail.com>
Date: Wed, 14 Aug 2024 01:26:36 +0530
Message-ID: <CAKna9VaA5N7sNRmjL7QujG92Mk-rx-7R1nSGhOVNZyXma=8LPw@mail.gmail.com>
Subject: Re: Column type modification in big tables
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: sud <suds1434@gmail.com>, pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000008701b2061f96029e"
Archived-At: <https://www.postgresql.org/message-id/CAKna9VaA5N7sNRmjL7QujG92Mk-rx-7R1nSGhOVNZyXma%3D8LPw%40mail.gmail.com>
Precedence: bulk

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

On Tue, Aug 13, 2024 at 7:39=E2=80=AFPM 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).
>
>
*"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)."*

Is there any possible method(maybe by looking into the data dictionary
tables/views etc) to see the progress of the Alter statement by which we
can estimate the expected completion time of the "Alter" command? I
understand pg_stat_activity doesn't show any completion percentage of a
statement, but wondering if by any other possible way we can estimate the
amount of time it will take in prod for the completion of the ALTER command=
.

--0000000000008701b2061f96029e
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 Tue, Aug 13, 2024 at 7:39=E2=80=AFPM G=
reg Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfids@gmail=
.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"mar=
gin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1=
ex"><div dir=3D"ltr"><div dir=3D"ltr">On Sat, Aug 10, 2024 at 5:06=E2=80=AF=
PM Lok P &lt;<a href=3D"mailto:loknath.73@gmail.com" target=3D"_blank">lokn=
ath.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;b=
order-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><d=
iv class=3D"gmail_quote"><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?<br></div></div></div></blockquote><d=
iv><br></div><div>We can&#39;t really answer that. Only you know what resou=
rces you have, what risk/reward you are willing to handle, and how long thi=
ngs 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 ex=
trapolate if you need to. Or promote one of your replicas offline and modif=
y that. I&#39;ve given you a low-risk / medium-reward option with check con=
straints, but for the ALTER TABLE options you really need to try it and see=
 (on non-prod).</div><div><br></div></div></div></blockquote><div>=C2=A0</d=
iv><div><i>&quot;Do a smaller table and extrapolate if you need to. Or prom=
ote one of your replicas offline and modify that. I&#39;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).&quot;</i><br></di=
v><div><i><br></i></div><div>Is there any possible method(maybe by looking =
into the data dictionary tables/views etc) to see the progress of the Alter=
=C2=A0statement by which we can estimate the expected completion time of th=
e &quot;Alter&quot; command? I understand pg_stat_activity doesn&#39;t=C2=
=A0show any completion percentage of a statement, but wondering if by any o=
ther possible way we can estimate the amount of time it will take in prod f=
or the completion of the=C2=A0ALTER=C2=A0command.</div></div></div>

--0000000000008701b2061f96029e--