Re: best practice for moving millions of rows to child table when setting up partitioning?

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: raghavendra(dot)rao(at)enterprisedb(dot)com
Blog: http://raghavt.blogspot.com/

On Wed, Apr 27, 2011 at 8:18 PM, Mark Stosberg <mark(at)summersault(dot)com> 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 (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>