Why? If you have to choose among a couple hundred partition tables, the trigger function body is far from trivial! You really think that calling and running a trigger function for every line is the best solution? 2009/12/17 Sam Jas <samjas33@xxxxxxxxx> > > Rule is not advisable, Trigger is the best solution. > > --- > Thanks > Sam Jas > > --- On Thu, 17/12/09, Vincenzo Romano <vincenzo.romano@xxxxxxxxxxx> wrote: > > From: Vincenzo Romano <vincenzo.romano@xxxxxxxxxxx> > Subject: Table Partitioning Advice Request > To: pgsql-general@xxxxxxxxxxxxxx > Date: Thursday, 17 December, 2009, 11:05 AM > > Hi all. > > I'm planning to implement table partitioning as "suggested" (among > other sources) in the official documentation. > I'm using v8.4.2 at the moment. > > My case is far from the general one as: > 1. I'll never UPDATE or DELETE rows from partitioned tables (only INSERTs) > 2. Rows will be inserted one-by-one or, in the worse case, in bunches > of two or three > 3. Partitioning will be based upon TIMESTAMP ranges > 4. The "virtual" tables should approach (and possibly go past) 100M rows > 5. Most (99%) of the INSERTs (and possibly SELECTs) will actually > operate on a rather small number of partitions (hardly more than 2). > > My main TABLE is like the following one: > > CREATE TABLE events ( > eventtype text not null, > item_id int8 not null, > event_date timestamp not null default now(), > row_date timestamp not null default now(), > event_id serial8 primary key > ); > > where the partitioning would happen over the values of the event_date column. > The row_date columns is to record the row creation TIMESTAMP as events > can be created relatively to the past, the future or the current time. > > In my mind a solution which is simple to maintain is to add a simple > RULE ... ON INSERT for every newly created partition table. > The TRIGGER approach, in my opinion, is much more complex to maintain > as either the body of the function needs to be rewritten as new > partitions are added, or > some external TABLE lookup is needed to choose the actual table name > to be used for a (dynamically created) INSERT. > > Now the questions. > > 1. As the number of RULEs will grow with the time, how will change > the efficiency of the query planner while "browsing" among the RULES? > 2. In the case the previous answer would lead to bad news for me, is > there any better/different approach to partitioning with TRIGGERs? > 3. Is there any more general advise for such approaches? > > -- > Vincenzo Romano > NON QVIETIS MARIBVS NAVTA PERITVS > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > ________________________________ > The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. -- Vincenzo Romano NotOrAnd Information Technologies cel. +39 339 8083886 | gtalk. vincenzo.romano@xxxxxxxxxxx fix. +39 0823 454163 | skype. notorand.it fax. +39 02 700506964 | msn. notorand.it NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
