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
<mailto: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 <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
--
El software de antivirus Avast ha analizado este correo electrónico en busca de
virus.
https://www.avast.com/antivirus
