RE: Alter the column data type of the large data volume table.

>From: Olivier Gautherot <ogautherot(at)gautherot(dot)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. 😊

HTH,
Kevin
.

This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.