public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alban Hertroys <[email protected]>
To: Lok P <[email protected]>
Cc: Greg Sabino Mullane <[email protected]>
Cc: sud <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Column type modification in big tables
Date: Thu, 15 Aug 2024 17:48:10 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKna9Vahx4ow0mtTEbVSeAU+f6U9v6G+Dkr-ymoyNhUZF_GRWw@mail.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>
> On 15 Aug 2024, at 14:15, Lok P <[email protected]> wrote:
(…)
> 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=4GB, maintenance_work_mem=30gb, max_parallel_worker_per_gather=8, max_parallel_maintenance_worker =16" )
>
> So considering the above figures , can i safely assume it will take ~90*5minutes= ~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?
Are all those partitions critical, or only a relative few?
If that’s 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’t actually tried that procedure, but I see no reason why it wouldn’t work.
Apart perhaps, from inserts happening that should have gone to some of those detached partitions. Maybe those could be sent to a ‘default’ 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.
view thread (22+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Column type modification in big tables
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox