Re: Alter the column data type of the large data volume table.

Hi Charles,

On 04-12-2020 9:44, Olivier Gautherot wrote:
> Hi Charles,
>
> On Fri, Dec 4, 2020 at 9:12 AM charles meng <xlyybz(at)gmail(dot)com
> <mailto:xlyybz(at)gmail(dot)com>> wrote:
>
> What I mean is that it will take a lot of time to complete all
> data processing.I have to say that it is a good solution to adjust
> the column type without affecting online users.
>
> I found a tool on github, see the link below, unfortunately, this
> is for MySQL...
>
> https://github.com/github/gh-ost <https://github.com/github/gh-ost>
>
>
> MySQL has its own strategy with regard to column handling so what
> works there does not necessarily fit here.
>
> There are some good ideas in this thread but I would start with a few
> checks:
>
> 1) What version of PG are you using ?
>
> 2) If you can try on your current setup (not necessarily in
> Production) to add a column typed bigint - if it is recent enough it
> will be a simple catalog update. Michael's suggestion is viable
>
> 3) Given the massive number of rows, I would update as suggested,
> progressively in batches of a few tens of thousands. Make sure you
> commit and vacuum after each to retrieve the space (or you may end up
> with a storage space issue in addition to all the rest). In the
> meantime, add a trigger to set the new column to the index value. Once
> the new column is complete, drop the old column and set the new one as
> primary key (it may take a while to recalculate the index).
>
> 4) If your table is still growing, I would definitely look into
> partitioning as it can hardly make things worse.
>
> 5) If you're brave enough, convert your current table as a partition
> (rename it to something like table_hist), duplicate the table model
> under the same name as now (adjusting the primary key type) and set
> the INHERITS on the primary key range. The inheritance should take
> care of the type conversion (haven't tried it but it's worth a try).
> If it works, you will reach your goal without downtime or significant
> overhead.

Sorry, just tried this one and it failed: type mismatch.

Cheers
--
Olivier Gautherot

--
El software de antivirus Avast ha analizado este correo electrónico en busca de virus.
https://www.avast.com/antivirus