Hello, I'm working on moving a table with over 30 million to rows to be partitioned. The table seeing several inserts per second. It's essentially an activity log that only sees insert activity and is lightly used for reporting, such that queries against it can safely be disabled during a transition.
I'm looking for recommendations for a way to do this that will be least disruptive to the flow of inserts statements that will continue to stream in. Here's the plan which seems best to me at the moment. Is it is reasonable? 1. Handling creating the empty/future partitions is easy. I have the code for this done already, and will make several partitions in advance of needing them. 2. To create the partitions that should have data moved from the parent, I'm thinking of creating them, and then before they are "live", using INSERT ... SELECT to fill them with data from the parent table. I'll run the INSERT first, and then add their indexes. 3. I will then install the trigger to redirect the inserts to the child table. 4. There will still be a relatively small number of new rows from the parent table to be deal with that came in after the INSERT from #2 was started, so a final INSERT .. SELECT statement will be made to copy the remaining rows. 5. Finally, I'll drop the indexes on the parent table and truncate it. Thanks for advice here. If there's a tutorial out there about this that I've missed, I'm happy to review it instead having it rehashed here. Thanks for the help! Mark -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin