public inbox for [email protected]
help / color / mirror / Atom feedFrom: Lok P <[email protected]>
To: 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:45:25 +0530
Message-ID: <CAKna9Vahx4ow0mtTEbVSeAU+f6U9v6G+Dkr-ymoyNhUZF_GRWw@mail.gmail.com> (raw)
In-Reply-To: <CAKAnmm+_avfVEFGgADebEyH=oQrEDuvviOcMYNa+myjJrds8Eg@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>
On Tue, Aug 13, 2024 at 7:39 PM Greg Sabino Mullane <[email protected]>
wrote:
> On Sat, Aug 10, 2024 at 5:06 PM Lok P <[email protected]> wrote:
>
>
>> 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=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?
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]
Subject: Re: Column type modification in big tables
In-Reply-To: <CAKna9Vahx4ow0mtTEbVSeAU+f6U9v6G+Dkr-ymoyNhUZF_GRWw@mail.gmail.com>
* 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