Обсуждение: Alter the column data type of the large data volume table.

Hi all,

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?

Solutions that have been tried:
Adding temporary columns was too time-consuming, so I gave up.
Using a temporary table, there is no good way to migrate the original table data to the temporary table

Thanks in advance.

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

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.

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

regards.

Michael Lewis <mlewis@entrata.com> 于2020年12月4日周五 下午1:04写道：

Please do reply all.

Not sure what you mean about it taking too much time. It's rewriting a bunch of data. It's going to take a while. The best you can do is break the work up into small pieces and commit each piece.

On Thu, Dec 3, 2020, 7:11 PM charles meng <xlyybz@gmail.com> wrote:

Thanks for your help, I think the first method I tried (adding temporary column) is similar to what you said, but it takes too much time for me.

Thanks again.

Michael Lewis <mlewis@entrata.com> 于2020年12月4日周五 上午1:11写道：

On Wed, Dec 2, 2020 at 11:53 PM charles meng <xlyybz@gmail.com> wrote:

Hi all,

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?

Solutions that have been tried:
Adding temporary columns was too time-consuming, so I gave up.
Using a temporary table, there is no good way to migrate the original table data to the temporary table

Thanks in advance.

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.

>From: Olivier Gautherot <ogautherot@gautherot.net>

>>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.

 

Seems like a sound idea in general. I’d probably rename the tables, let’s call them “big_hist” for the old big table and “big_split” for the new partitioned table that being used go forward – assuming the original table was called “big”. Then create a View that will look at both of those but call it the same as the old table, and let the view do a type cast on the old key like big_hist.id::bigint so it matches the new type, because the view will probably be a union and the type need to match. That way your application only has to pause long enough to do a few meta-commands then it all can resume, and like Olivier pointed you, you can fix the data by moving it from big_hist to big_split in the background as you have time.

 

I’d probably put it all in a transaction too:

 

Create table … -- all the commands to create your patitioned table big_split here

Begin;

Alter table big rename to big_hist;

Create view big select * from big_split union select id::bigint, /* other cols */ from big_hist;

Commit;

 

Try it on a dev system and if it works you’re off and running. I’d expect the view to slow things down a little, but probably not too much if you have good indexes. But at least you could transition without major downtime and then rename “big_split” back to “big” and drop “big_hist” when you’ve finished the transition. I might even be tempted to add a trigger so that all new inserts into “big” really go into “big_split” so “big_hist” doesn’t grow any more. Your imagination is probably the limit. 😊

On Fri, Dec 4, 2020 at 9:04 AM Kevin Brannen <KBrannen@efji.com> wrote:

>From: Olivier Gautherot <ogautherot@gautherot.net>

>>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.

 

Seems like a sound idea in general. I’d probably rename the tables, let’s call them “big_hist” for the old big table and “big_split” for the new partitioned table that being used go forward – assuming the original table was called “big”. Then create a View that will look at both of those but call it the same as the old table, and let the view do a type cast on the old key like big_hist.id::bigint so it matches the new type, because the view will probably be a union and the type need to match. That way your application only has to pause long enough to do a few meta-commands then it all can resume, and like Olivier pointed you, you can fix the data by moving it from big_hist to big_split in the background as you have time.

 

I’d probably put it all in a transaction too:

 

Create table … -- all the commands to create your patitioned table big_split here

Begin;

Alter table big rename to big_hist;

Create view big select * from big_split union select id::bigint, /* other cols */ from big_hist;

Commit;

 

Try it on a dev system and if it works you’re off and running. I’d expect the view to slow things down a little, but probably not too much if you have good indexes. But at least you could transition without major downtime and then rename “big_split” back to “big” and drop “big_hist” when you’ve finished the transition. I might even be tempted to add a trigger so that all new inserts into “big” really go into “big_split” so “big_hist” doesn’t grow any more. Your imagination is probably the limit. 😊

Yes, "instead of" triggers for insert/update/delete will make the change transparent to the application side, other than the potential for slowness while in the process of moving the data and still using the view.

Also, I'd advocate for UNION ALL to avoid the "distinct" work that is required for plain UNION. I wish ALL were default behavior and "UNION DISTINCT" was how get that behavior.

If partitioning is going to happen anyway in the near future, now is a great time to make that happen. Unfortunately, PG13 is required to use logical replication from a non-partitioned table to a partitioned table so moving the data still requires some creative work.