From: Mark Stosberg <mark@summersault.com>
To: pgsql-admin@postgresql.org
Subject: best practice for moving millions of rows to child table when setting up partitioning?
Date: Wed, 27 Apr 2011 10:48:26 -0400
Message-ID: <ip9abr$nk8$1@dough.gmane.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: mark@summersault.com
Subject: Re: best practice for moving millions of rows to child table when setting up partitioning?
In-Reply-To: <ip9abr$nk8$1@dough.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox