On 12/3/20 11:26 AM, Michael Lewis wrote:
On Thu, 3 Dec 2020, Michael Lewis wrote:
> On Wed, Dec 2, 2020 at 11:53 PM charles meng <xlyybz@gmail.com> wrote:
>> I have a table with 1.6 billion records. The data type of the primary key
>> column is incorrectly used as integer. I need to replace the type of the
>> column with bigint. Is there any ideas for this?
> You can add a new column with NO default value and null as default and have
> it be very fast. Then you can gradually update rows in batches (if on
> PG11+, perhaps use do script with a loop to commit after X rows) to set the
> new column the same as the primary key. Lastly, in a transaction, update
> any new rows where the bigint column is null, and change which column is
> the primary key & drop the old one. This should keep each transaction
> reasonably sized to not hold up other processes.
Tell me, please, why
ALTER TABLE <tablename> ALTER COLUMN <columnname> SET DATA TYPE BIGINT
will not do the job?
I've found some varchar columns in a couple of tables too small and used the
above to increase their size. Worked perfectly.
Regards,
Rich
Afaik, it will require an access exclusive lock for the entire time it takes to re-write the 1.6 billion rows and update all indexes. That sort of lock out time doesn't seem workable in many production systems.
Yet another argument for partitioning!
1. Split split all the partitions from the main table,
2. drop the PK,
3. do all the ALTER statements in parallel,
4. recreate the PK indices, then
5. join them back to the main table.
Not instant, but faster than updating 1.6Bn rows in one single giant statement.
(Of course, that doesn't help OP with his current problem.)
--
Angular momentum makes the world go 'round.
