On Fri, Dec 4, 2020 at 5:22 PM Michael Lewis <mle...@entrata.com> wrote:
> > On Fri, Dec 4, 2020 at 9:04 AM Kevin Brannen <kbran...@efji.com> wrote: > >> *>From:* Olivier Gautherot <ogauthe...@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. > Rather than union, use inheritance with a constraint on the primary key: it will take care of the union transparently for you. -- Olivier Gautherot <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> Libre de virus. www.avast.com <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
