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: <CAKna9Vahx4ow0mtTEbVSeAU+f6U9v6G+Dkr-ymoyNhUZF_GRWw@mail.gmail.com>
Date: Thu, 15 Aug 2024 17:48:10 +0200
Cc: Greg Sabino Mullane <htamfids@gmail.com>,
 sud <suds1434@gmail.com>,
 pgsql-general <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <D8602AF7-7EDE-46EC-B12F-5AE003969994@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>
 <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>
 <CAKna9Vahx4ow0mtTEbVSeAU+f6U9v6G+Dkr-ymoyNhUZF_GRWw@mail.gmail.com>
To: Lok P <loknath.73@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/D8602AF7-7EDE-46EC-B12F-5AE003969994%40gmail.com>
Precedence: bulk


> On 15 Aug 2024, at 14:15, Lok P <loknath.73@gmail.com> wrote:

(=E2=80=A6)

> Hello Greg,=20
>=20
> 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=3D30gb, =
max_parallel_worker_per_gather=3D8, max_parallel_maintenance_worker =3D16"=
 )
>=20
> 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 =
downtime needed for this alter OR do we need to consider any other =
factors or activity here?=20

Are all those partitions critical, or only a relative few?

If that=E2=80=99s the case, you could:
	1) detach the non-critical partitions
	2) take the system down for maintenance
	3) update the critical partitions
	4) take the system up again
	5) update the non-critical partitions
	6) re-attach the non-critical partitions

That could shave a significant amount of time off your down-time. I =
would script the detach and re-attach processes first, to save some =
extra.

Admittedly, I haven=E2=80=99t actually tried that procedure, but I see =
no reason why it wouldn=E2=80=99t work.

Apart perhaps, from inserts happening that should have gone to some of =
those detached partitions. Maybe those could be sent to a =E2=80=98default=
=E2=80=99 partition that gets detached at step 7, after which you can =
insert+select those from the default into the appropriate partitions?

But you were going to test that first anyway, obviously.

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