Hi Olivier, My PG version is 10. Anyway, thanks a lot for your help.
Best regards. Olivier Gautherot <ogauthe...@gautherot.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 <xly...@gmail.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 > >
