On Fri, Dec 20, 2013 at 8:52 AM, Dave Johansen <davejohan...@gmail.com>wrote:
> On Thu, Dec 19, 2013 at 10:27 AM, Scott Marlowe > <scott.marl...@gmail.com>wrote: > >> On Thu, Dec 19, 2013 at 9:53 AM, Dave Johansen <davejohan...@gmail.com> >> wrote: >> >> >> > On Sat, Dec 7, 2013 at 1:37 PM, Scott Marlowe <scott.marl...@gmail.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.