|
>From: Olivier Gautherot <ogautherot@xxxxxxxxxxxxx>
>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 . |
