To: pgsql-admin@postgresql.org
From: Mark Stosberg <mark@summersault.com>
Subject: Re: best practice for moving millions of rows to child table when
	setting up partitioning?
Date: Wed, 27 Apr 2011 15:35:14 -0400
Lines: 29
Message-ID: <ip9r5i$3c2$1@dough.gmane.org>
References: <ip9abr$nk8$1@dough.gmane.org>
	<BANLkTinnJqqJzB8wRi6KkLqhhGQwkyYcrw@mail.gmail.com>
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
In-Reply-To: <BANLkTinnJqqJzB8wRi6KkLqhhGQwkyYcrw@mail.gmail.com>

> 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. 

Thanks for the replies.

Today I reviewed the section on partitioning from Greg's High
Performance book.

I tried out the method of using an "update trigger" on the parent table
to move rows child tables.

In particular, I wanted to check whether the UPDATE statement would
alter all the rows automatically, or if the underlying trigger would
cause all the rows processed a row at a time.

It appears from my test that the result of the UPDATE was going to
appear all at once. I'm worried about the resource implications of
inserting mullions of rows all at once.

Someone else suggested writing a small program to insert the rows in s
smaller chunks. I think that's the approach I'm going to consider taking.

Thanks for the input!

   Mark