Hi, Maybe someone on this list actually have already tried this: I'm planning to make a partitioned database. From Postgres documentation I can see, that there are basically two methods to route INSERTS into partitioned table: one. is a TRIGGER other. is a RULE My Table will have over 1000 partitions. Some not so big, but significant number of them will be of multimillion rows. Partitioning will be done using a single column, on equality.... meaning: CREATE TABLE mainlog (sel int, tm timestamp, info text,...); CREATE TABLE mainlog_p1 (CHECK (sel=1)) INHERITS (mainlog); CREATE TABLE mainlog_p2 (CHECK (sel=2)) INHERITS (mainlog); ...etc. If I route INSERT with a TRIGGER, the function would look like: CREATE .... TRIGGER...AS $$ DECLARE x RECORD; BEGIN SELECT id INTO x FROM current_route; NEW.sel := x.id; IF NEW.sel = 1 THEN INSERT INTO mainlog_p1 VALUES (NEW.*); ELSE IF NEW.sel = 2 THEN INSERT INTO mainlog_p2 VALUES (NEW.*); .... END IF; RETURN NULL; $$; If I route INSETS with a RULE, I'd have something like 1000 rules hooked up to MAINLOG, all looking like: CREATE RULE .... ON INSERT ... WHERE EXISTS(SELECT 1 FROM current_route WHERE id = 1) DO INSTEAD INSERT INTO mainlog_p1 VALUES SELECT x.id,new.tm... FROM (SELECT id FROM current_route) x; ... and similar RULES for cases "WHERE id = 2", etc. My question is, where should I expect better performance on those INSERTS). I would prefer a set of RULES (as I wouldn't like to rewrite TRIGGER function every time I add a partition ... a thousand lines function), but since they all must make a select query on CURRENT_ROUTE table, may be that will not be particularly effective? The TRIGGER function does a single query - may be it'll be faster? I was planning to generate some dummy data and run a simulation, but may be someone already has that experience? Or maybe the TRIGGER should look differently? Or the set of RULES? And on a similar token: Is there a way to RAISE an exception *within* RULE definition? The only way I could imagine to achieve that is to create a function raise_exception(), and make a "SELECT raise_eception();" in such case. Within a RULE I cannot "CREATE TABLE", EXECUTE, RAISE, etc .... or there is a way but I just don't know it.... without artificial use of "SELECT function()"? -R
