From: Mark Stosberg <mark@summersault.com>
To: pgsql-admin@postgresql.org
Subject: Re: best practice for moving millions of rows to child table when setting up partitioning?
Date: Wed, 27 Apr 2011 16:17:16 -0400
Message-ID: <ip9tkc$jek$1@dough.gmane.org> (raw)
In-Reply-To: <ip9abr$nk8$1@dough.gmane.org>
References: <ip9abr$nk8$1@dough.gmane.org>
On 04/27/2011 10:48 AM, Mark Stosberg 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?
I revised my plan based on feedback and mentioned resources here, and
also due to my own concerns about the resource and performance impact of
using the INSERT .. SELECT pattern on millions of rows at a time.
Here's my revised plan for the transition, which avoids using large
INSERT..SELECT statements, and only requires a minimal amount of the
transition to work to happen once the application has started to insert
data into the child tables. Seem reasonable?
New Plan
---------
1. Create the child tables targeted to contain data from the parent as
standalone tables (including archive tables and the current month).
2. Take a full database backup from this morning and extract the COPY
statement for the parent table. Manually split it up by date to create
multiple copy statements, one for each partition.
3. Run the COPY statements to load the data into each child table. So
far, everything has happened outside of application access.
4. Find the max ID that has been inserted in the current child table.
5. INSERT .. SELECT the missing rows from the last backup from the parent
table to the current child table to be. Again, note the max ID.
6. Now, during a maintenance window:
- alter the child tables to inherit the parent
- Set up the trigger which starts redirecting inserts
from the parent table to the child table.
- INSERT .. SELECT the file the final few missing rows from
the parent to the current child
- TRUNCATE then CLUSTER the parent table.
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: <ip9tkc$jek$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