On Thu, Dec 19, 2013 at 9:53 AM, Dave Johansen <davejohansen@gmail.com> wrote: >> > On Sat, Dec 7, 2013 at 1:37 PM, Scott Marlowe <scott.marlowe@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.
