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: Thu, 15 Aug 2024 17:45:25 +0530
Message-ID: <CAKna9Vahx4ow0mtTEbVSeAU+f6U9v6G+Dkr-ymoyNhUZF_GRWw@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="000000000000e4c014061fb7ccf3"
Archived-At: <https://www.postgresql.org/message-id/CAKna9Vahx4ow0mtTEbVSeAU%2Bf6U9v6G%2BDkr-ymoyNhUZF_GRWw%40mail.gmail.com>
Precedence: bulk

--000000000000e4c014061fb7ccf3
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. *
>

Hello Greg,

In terms of testing on sample data and extrapolating, as i picked the avg
partition sizeof the table (which is ~20GB) and i created a non partitioned
table with exactly same columns and populated with similar data and also
created same set of indexes on it and the underlying hardware is exactly
same as its on production. I am seeing it's taking ~5minutes to alter all
the four columns on this table. So we have ~90 partitions in production
with data in them and the other few are future partitions and are blank.
(Note- I executed the alter with "work_mem=3D4GB, maintenance_work_mem=3D30=
gb,
max_parallel_worker_per_gather=3D8, max_parallel_maintenance_worker =3D16" =
)

So considering the above figures , can i safely assume it will take
~90*5minutes=3D ~7.5hours in production and thus that many hours of downtim=
e
needed for this alter OR do we need to consider any other factors or
activity here?

--000000000000e4c014061fb7ccf3
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.<b> Do a smaller table and=
 extrapolate if you need to.=C2=A0</b></div></div></div></blockquote><div><=
br></div><div>Hello Greg,=C2=A0</div><div><br></div><div>In terms of testin=
g on sample data and extrapolating, as i picked the=C2=A0avg partition size=
of the table (which is ~20GB) and i created a non partitioned table with ex=
actly same columns and populated with similar data and also created=C2=A0sa=
me set of indexes on it and the underlying hardware is exactly same as its =
on production. I am seeing it&#39;s taking ~5minutes to alter all the four =
columns on this table. So we have ~90 partitions in production with data in=
 them and the other few are future partitions and are blank. (Note- I execu=
ted the alter=C2=A0with &quot;work_mem=3D4GB, maintenance_work_mem=3D30gb, =
max_parallel_worker_per_gather=3D8, max_parallel_maintenance_worker =3D16&q=
uot; )</div><div><br></div><div>So considering the above figures , can i sa=
fely assume it will take ~90*5minutes=3D ~7.5hours in production and thus t=
hat many hours of=C2=A0downtime needed for this=C2=A0alter=C2=A0OR do we ne=
ed to consider any other factors or activity here?=C2=A0</div><div>=C2=A0</=
div></div></div>

--000000000000e4c014061fb7ccf3--