Re: Recommendations for partitioning?

On Fri, Dec 20, 2013 at 8:52 AM, Dave Johansen <davejohansen(at)gmail(dot)com>wrote:

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

It turns out that this was an error on my part. I was using an old script
to do the restore and it had --disable-triggers to prevent the foreign keys
from being checked and that was the actual source of my problem.