DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=3l+j5x721qpVKrcfzj1qxAmMDJLvARqJp6mKP9+4JjRjIDx4m7fnuo+N3yuWAQxf6Wh7ldyFguYteF4Zy8aSsiK1rIuAaeDXNt56GHKCUWjZ5HU4O/97eNrd5qb7FcWgoeLL+is9ddZuC+5CY+IE3/cow75ffCzdFLit2Lv8Fks=;
Message-ID: <659395.83416.qm@web39708.mail.mud.yahoo.com>
Date: Wed, 27 Apr 2011 12:37:48 -0700 (PDT)
From: Bob Lunney <bob_lunney@yahoo.com>
Subject: Re: best practice for moving millions of rows to child table when
	setting up partitioning?
To: pgsql-admin@postgresql.org, Mark Stosberg <mark@summersault.com>
In-Reply-To: <ip9abr$nk8$1@dough.gmane.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Mark,=0A=0AComments inline below for items 2 and 3.  What you are planning =
should work like a charm.=0A=0ABob Lunney=0A=0A--- On Wed, 4/27/11, Mark St=
osberg <mark@summersault.com> wrote:=0A=0A> From: Mark Stosberg <mark@summe=
rsault.com>=0A> Subject: [ADMIN] best practice for moving millions of rows =
to child table when setting up partitioning?=0A> To: pgsql-admin@postgresql=
.org=0A> Date: Wednesday, April 27, 2011, 10:48 AM=0A> =0A> Hello,=0A> =0A>=
 I'm working on moving a table with over 30 million to rows=0A> to be=0A> p=
artitioned. The table seeing several inserts per second.=0A> It's=0A> essen=
tially an activity log that only sees insert activity=0A> and is=0A> lightl=
y used for reporting, such that queries against it=0A> can safely be=0A> di=
sabled during a transition.=0A> =0A> I'm looking for recommendations for a =
way to do this that=0A> will be least=0A> disruptive to the flow of inserts=
 statements that will=0A> continue to=0A> stream in.=0A> =0A> Here's the pl=
an which seems best to me at the moment. Is it=0A> is=0A> reasonable?=0A> =
=0A> 1. Handling creating the empty/future partitions is easy. I=0A> have t=
he=0A> code for this done already, and will make several=0A> partitions in =
advance=0A> of needing them.=0A> =0A> 2. To create the partitions that shou=
ld have data moved=0A> from the parent,=0A> I'm thinking of creating them, =
and then before they are=0A> "live",=0A> using INSERT ... SELECT to fill th=
em with data from the=0A> parent table.=0A> I'll run the INSERT first, and =
then add their indexes.=0A> =0A=0AUse "create table as select ..." (CTAS) i=
nstead of creating the table, then inserting.  Since the table is created a=
nd populated atomically there is no need to log the inserts in WAL, and the=
 operation is much faster.=0A=0A> 3. I will then install the trigger to red=
irect the inserts=0A> to the child=0A> table.=0A> =0A=0AIf possible, its be=
tter to have the code simply do inserts directly into the child table - aft=
er all, if the partitioning is based on date, both the code and database kn=
ow the date, so the code knows to which child table it should write at any =
given moment.=0A=0A> 4. There will still be a relatively small number of ne=
w=0A> rows from the=0A> parent table to be deal with that came in after the=
 INSERT=0A> from #2 was=0A> started, so a final INSERT .. SELECT statement =
will be made=0A> to copy the=0A> remaining rows.=0A> =0A> 5. Finally, I'll =
drop the indexes on the parent table and=0A> truncate it.=0A> =0A> Thanks f=
or advice here. If there's a tutorial out there=0A> about this that=0A> I'v=
e missed, I'm happy to review it instead having it=0A> rehashed here.=0A> =
=0A> Thanks for the help!=0A> =0A> =A0 =A0 Mark=0A> =0A> =0A> -- =0A> Sent =
via pgsql-admin mailing list (pgsql-admin@postgresql.org)=0A> To make chang=
es to your subscription:=0A> http://www.postgresql.org/mailpref/pgsql-admin=
=0A>