On Thu, 2007-03-29 at 22:15 -0700, Benjamin Arai wrote: > I have one system which I have used partitioning. For this particular > case I have tons of data over about (50 years). What I did is wrote > small loader that breaks data in tables based on date, so I have tables > like abc_2000, abc_2001 etc. The loading script is only a couple > hundred lines of code. The only part that was a little bit of work was > to allow for easy access to the data for the data for the devs. I did > this by writing a few PL functions to automatically union the tables > and produce results. So the function like getData(startData,enddate) > would run a union query for the respective date ranges. >From reading on the list, I think the current recommendations are: have a parent table and then create the partitions as descendants (INHERITS) of the parent table. Use constraints to ensure that nothing gets inserted into the parents, and that only the correct data gets inserted into the descendants (i.e., the constraints enforce that 2002 data won't insert into the 2001 partition, the insert would fail if you tried that). Turn constraint_exclusion on in postgresql.conf (helps the planner ignore partitions that don't qualify), so it doesn't need to scan partitions where there will be no matches anyway. Use rules on the parent to redirect inserts/updates/deletes to the right partition (or trigger, i'm having some problems with both rules and triggers, so I may have the loader insert straight to the right partition instead, although I'm still trying to get the rules right. The advantage of this (parent table plus descendant partitions) is that you can query from the parent, and the descendants will be automatically queried, you'll get all matching rows from any descendants that have them. No need for those unions or pl/pgsql functions to do the unions for you. That said, I need to make my current code work with smaller test data sets so I can isolate the problems I'm having with rules, etc. E.g., my original plan was to detect automatically (in a before trigger) if a partition for the to-be-loaded data set already exists, and if it doesn't, create the partitions and the rules. I then load the data into the parent table and expect the newly created rules to redirect the data into the new partitions. Either my rules are wrogn, or there's something about transaction semantics that's making that fail since the rows are inserting into the parent table (no constraint there yet to disallow inserts into the parent) instead of the right partition. To The List: Are schema changes (such as adding rules and creating child partitions) part of the same transaction or do they happen (magically) outside the current transaction, in a new transaction (e.g., so that the inserts would fail because the inserts are running in an older transaction that can't see the new rule or new partition table yet). As I said, I might just be doing something wrogn there. I've set that project aside for now since it's not urgent (the current unpartitioned system works well enough, I just avoid reindex, vacuum, vacuum full and pg_dump), but I'll start working on it again when I get some free time in a week or two (Holy Week, holiday where I am :-). tiger
