Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux