Re: Alter the column data type of the large data volume table. - Mailing list pgsql-general

From Ron
Subject Re: Alter the column data type of the large data volume table.
Date
Msg-id f9d333ca-a981-e8a4-2ac5-b0087652d934@gmail.com
Whole thread Raw
In response to Re: Alter the column data type of the large data volume table.  (Michael Lewis <mlewis@entrata.com>)
List pgsql-general
On 12/3/20 11:26 AM, Michael Lewis wrote:
On Thu, Dec 3, 2020 at 10:18 AM Rich Shepard <rshepard@appl-ecosys.com> 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.

pgsql-general by date:

Previous
From: Rich Shepard
Date:
Subject: Re: Alter the column data type of the large data volume table.
Next
From: Michael Lewis
Date:
Subject: Re: Alter the column data type of the large data volume table.