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.
