To: pgsql-admin@postgresql.org
From: Mark Stosberg <mark@summersault.com>
Subject: best practice for moving millions of rows to child table when setting
	up partitioning?
Date: Wed, 27 Apr 2011 10:48:26 -0400
Lines: 41
Message-ID: <ip9abr$nk8$1@dough.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.14) Gecko/20110223 Thunderbird/3.1.8


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