Re: Table Partitioning Advice Request

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(at)yahoo(dot)com>
>
> Rule is not advisable, Trigger is the best solution.
>
> ---
> Thanks
> Sam Jas
>
> --- On Thu, 17/12/09, Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it> wrote:
>
> From: Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>
> Subject: [GENERAL] Table Partitioning Advice Request
> To: pgsql-general(at)postgresql(dot)org
> 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(at)postgresql(dot)org)
> 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(dot)romano(at)notorand(dot)it
fix. +39 0823 454163  | skype. notorand.it
fax. +39 02 700506964 | msn.   notorand.it
NON QVIETIS MARIBVS NAVTA PERITVS