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

Hi Olivier,

My PG version is 10.
Anyway, thanks a lot for your help.

Best regards.

Olivier Gautherot <ogautherot(at)gautherot(dot)net> 于2020年12月4日周五 下午6:14写道：

> 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> 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
>>
>
> 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
>
>