Re: Recommendations for partitioning?

On Thu, Dec 19, 2013 at 10:27 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Thu, Dec 19, 2013 at 9:53 AM, Dave Johansen <davejohansen(at)gmail(dot)com>
> wrote:
> >>
> > On Sat, Dec 7, 2013 at 1:37 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> > wrote:
> >> I'll add that you can use assymetric partitioning if you tend to do a
> >> lot of more fine grained queries on recent data and more big roll up
> >> on older ones. I.e. partition by month except for the last 30 days, do
> >> it by day etc. Then at the end of the month roll all the days into a
> >> month partition and delete them.
> >
> > This sounds like a great solution for us. Is there some trick to roll the
> > records from one partition to another? Or is the only way just a SELECT
> INTO
> > followed by a DELETE?
>
> That's pretty much it. What I did was to create the new month table
> and day tables, alter my triggers to reflect this, then move the data
> with insert into / select from query for each old day partition. Then
> once their data is moved you can just drop them. Since you changed the
> triggers first those tables are no long taking input so it's usually
> safe to drop them now.
>

It would be nice if there was just a "move command", but that seems like
the type of model that we want and we'll probably move to that.

On a semi-related note, I was trying to move from the single large table to
the partitions and doing INSERT INTO SELECT * FROM WHERE ... was running
very slow (I believe because of the same index issue that we've been
running into), so then I tried creating a BEFORE INSERT trigger that was
working and using pg_restore on an -Fc dump. The documentation says that
triggers are executed as part of a COPY FROM (
http://www.postgresql.org/docs/8.4/static/sql-copy.html ), but it doesn't
appear that the trigger was honored because all of the data was put into
the base table and all of the partitions are empty.

Is there a way that I can run pg_restore that will properly honor the
trigger? Or do I just have to create a new INSERTs dump?

Thanks,
Dave