Hi Mark, Similar posting on partition table, take this inputs before going forward with partition table.
http://archives.postgresql.org/pgsql-general/2011-04/msg00808.php Best solution given by Greg Smith as well Vick. Best Regards, Raghavendra EnterpriseDB Corporation The Enterprise Postgres Company Email: [email protected] Blog: http://raghavt.blogspot.com/ On Wed, Apr 27, 2011 at 8:18 PM, Mark Stosberg <[email protected]> wrote: > > 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 ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >
