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

charles meng <xlyybz@xxxxxxxxx> · Fri, 4 Dec 2020 18:39:22 +0800

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

Best regards.

Olivier Gautherot <ogautherot@xxxxxxxxxxxxx> 于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@xxxxxxxxx> 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