Re: Table Partitioning Advice Request

2009/12/18 Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>:
> The main reason to avoid rules is that they're much less efficient
> than triggers.  We use partitioning at work for our stats db, and
> partition by day, and we have about 2 years worth of stats data, so
> our insert trigger has about 700 if / elseif / else conditions in it,
> and the trigger is still very very fast.  I tried it with rules before
> and it was way too slow.

I still don't understand the poor efficiency of the rule system. I presume it's
in the implementation, not the concept.

Well, for the sake of efficiency I would like to avoid that if/elseif chain
with an extra support table to look for the right partition.
Unless you have a different advise against dynamic SQL (EXECUTE...)
to define the partition table name.

> Note that the absolute fastest way to insert to the right child table
> is to do it from the application side, choosing the right child table
> there.

I definitely disagree on this advise. For the sake of speed I could
also run some library level (and thus application level) database
solution.
The main objective for using an RDBMS (at least in my view) is to
gather in a single place (the RDBMS) the data storage and
management stuff.
Moving policies to the application level would just scatter that
stuff with faster but more error prone solutions.
And the aplpication needs to know the partitioning policies.

> We automated our trigger creation.  While the trigger is huge in terms
> of number of lines, speed wise the creation of the new trigger each
> night at midnight is measured in milliseconds

Correct.
There is still another point for me to investigate into this partitioning
area. When the partitions get created.
I see from your answers that everyone is expecting the partitions to
be already in place at the moment the trigger or the rule get fired.
In my application you can generate events for the past or the future
where there's no partition yet.

--
Vincenzo Romano
NON QVIETIS MARIBVS NAVTA PERITVS