Thread: Table inheritance and partitioning
I am making the move to partition a table. I am trying to figure out the best way to migrate data to the partitions. I would prefer to not have down time. Does anybody have advice to give on this? Is there any easy way to determine what records are in the master table and which ones are in child tables? I can think of a few migration ideas doing this. Thanks in advance. -- Kevin Barnard kevin.barnard@laser2mail.com
On Wed, May 27, 2009 at 4:52 PM, Kevin Barnard <kevin.barnard@laser2mail.com> wrote: > I am making the move to partition a table. I am trying to figure out the > best way to migrate data to the partitions. I would prefer to not have down > time. Does anybody have advice to give on this? Is there any easy way to > determine what records are in the master table and which ones are in child > tables? I can think of a few migration ideas doing this. If you continue to query the master table, as you move data to the child tables, you won't really have to do much to your app. This is the benefit of the table inheritance. What we did was update our app to insert new data into the proper sub-table. Then we had a program running that would move one hunk of data at a time to from the master table to the proper sub-table. The programs querying the master table were unchanged, and just did the right thing. Once all data was moved, we did a TRUNCATE on the master table, and updated the queries we could so they would access the proper child table directly. Some queries still needed to scan the whole table so were left to query the master table. Once you start playing with it (and the constraint exclusion) it really becomes obvious that it is not all that complicated and magical.
On Wed, 2009-05-27 at 15:52 -0500, Kevin Barnard wrote: > I am making the move to partition a table. I am trying to figure out > the best way to migrate data to the partitions. I would prefer to not > have down time. Does anybody have advice to give on this? One strategy is to create a trigger on the master table that sends the records to the appropriate child table instead. See this presentation from Robert Treat: http://www.slideshare.net/xzilla/postgresql-partitioning-pgcon-2007-presentation When that starts working, you can just slowly move records by doing an INSERT to the child tables and a DELETE from the master in the same transaction (probably in big batches). > Is there > any easy way to determine what records are in the master table and > which ones are in child tables? I can think of a few migration ideas > doing this. If you do "SELECT ONLY ..." it doesn't descend into the child tables. Regards, Jeff Davis