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>
In-Reply-To: <CAKna9VZJ4fginFJZenGQxWs9eAw9Z8g-YkdnOFcie5RvuJ=5OQ@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Fri, 9 Aug 2024 11:48:35 -0400
Message-ID: <CAKAnmmLv72uk1p8+zmWpkC+BTatrdmRe_NpRbwRsi1LAU-cJFQ@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="000000000000a3c400061f421536"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmLv72uk1p8%2BzmWpkC%2BBTatrdmRe_NpRbwRsi1LAU-cJFQ%40mail.gmail.com>
Precedence: bulk

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

On Fri, Aug 9, 2024 at 6:39=E2=80=AFAM Lok P <loknath.73@gmail.com> wrote:

> Thank you so much. Will definitely try to evaluate this approach. The Onl=
y
> concern I have is , as this data is moving downstream with exactly the sa=
me
> data type and length , so will it cause the downstream code to break whil=
e
> using this column in the join or filter criteria. Also I believe the
> optimizer won't be able to utilize this information while preparing the
> execution plan.
>

Yes, this is not as ideal as rewriting the table, but you asked for
another approaches :) As to the impact of your downstream stuff, I think
you have to try and see. Not clear what you mean by the optimizer, it's not
going to really care about numeric(10) versus numeric(8) or varchar(20) vs
varchar(2). It's possible the varchar -> numeric could cause issues, but
without real-world queries and data we cannot say.


>  Another thing , correct me if wrong, My understanding is  , if we want t=
o
> run the "validate constraint" command after running this "check constrain=
t
> with not valid" command, this will do a full table scan across all the
> partitions , but it's still beneficial as compared to updating the column=
s
> values for each rows. Correct me if I'm wrong.
>

Yes, it needs to scan the entire table, but it's a lightweight lock, won't
block concurrent access, will not need to detoast, and makes no table or
index updates. Versus an entire table rewrite which will do heavy locking,
take up tons of I/O, update all the indexes, and generate quite a lot of
WAL.

Cheers,
Greg

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

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Aug 9, 2024 at 6:39=E2=80=AFAM Lo=
k P &lt;<a href=3D"mailto:loknath.73@gmail.com">loknath.73@gmail.com</a>&gt=
; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quo=
te" 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">Thank you so much. Wi=
ll definitely try to evaluate this approach. The Only concern I have is , a=
s this data is moving downstream with exactly the same data type and length=
 , so will it cause the downstream code to break while using this column in=
 the join or filter criteria. Also I believe the optimizer won&#39;t be abl=
e to utilize this information while preparing the execution plan.<br></div>=
</div></blockquote><div><br></div><div>Yes, this is not as ideal as rewriti=
ng the table, but you asked for another=C2=A0approaches :) As to the impact=
 of your downstream stuff, I think you have to try and see. Not clear what =
you mean by the optimizer, it&#39;s not going to really care about numeric(=
10) versus numeric(8) or varchar(20) vs varchar(2). It&#39;s possible the v=
archar -&gt; numeric could cause issues, but without real-world queries and=
 data we cannot say.</div><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 dir=3D"ltr"><div class=3D"gmail_quote"><div>=C2=A0An=
other thing , correct me if wrong, My understanding is=C2=A0 , if we want t=
o run the &quot;validate constraint&quot; command after running this &quot;=
check constraint with not valid&quot; command, this will do a full table sc=
an across all the partitions , but it&#39;s=C2=A0still beneficial as compar=
ed to updating the columns values for each rows. Correct me if I&#39;m wron=
g.=C2=A0</div></div></div></blockquote><div><br></div><div>Yes, it needs to=
 scan the entire table, but it&#39;s a lightweight lock, won&#39;t block co=
ncurrent access, will not need to detoast, and makes no table or index upda=
tes. Versus an entire table rewrite which will do heavy locking, take up to=
ns of I/O, update all the indexes, and generate quite a lot of WAL.=C2=A0</=
div><div><br></div><div>Cheers,</div><div>Greg</div><div>=C2=A0<br></div></=
div></div>

--000000000000a3c400061f421536--