Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51\))
Subject: Re: Column type modification in big tables
From: Alban Hertroys <haramrae@gmail.com>
In-Reply-To: <CAKna9Vb_mx+dX02XOV6mpr8RFC-5io38kM6=4xRHQj_MUvQ+aQ@mail.gmail.com>
Date: Thu, 8 Aug 2024 20:55:22 +0200
Cc: sud <suds1434@gmail.com>,
 pgsql-general <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <7D2FBDFB-40DD-4458-B213-03EB7311EC4D@gmail.com>
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>
To: Lok P <loknath.73@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/7D2FBDFB-40DD-4458-B213-03EB7311EC4D%40gmail.com>
Precedence: bulk


> On 8 Aug 2024, at 20:38, Lok P <loknath.73@gmail.com> wrote:
>=20
> Thank you so much.=20
>=20
> Can anybody suggest any other possible way here. As, we also need to =
have the existing values be updated to the new column value here using =
update command (even if it will update one partition at a time). And as =
I see we have almost all the values in the column not null, which means =
it will update almost ~5billion rows across all the partitions. So my =
question is , is there any parameter(like work_mem,maintenance_work_mem =
etc) which we can set to make this update faster?
>  or any other way to get this column altered apart from this method?

Just a wild thought here that I=E2=80=99m currently not able to check=E2=80=
=A6 Can you add views as partitions? They would be read-only of course, =
but that would allow you to cast the columns in your original partitions =
to the new format, while you can add any new partitions in the new =
format.

I suspect it=E2=80=99s not allowed, but perhaps worth a try.

Alban Hertroys
--
There is always an exception to always.