Re: table partioning performance

On 1/10/07, Jim C. Nasby <jim(at)nasby(dot)net> wrote:
>
> Except for the simplest partitioning cases, you'll be much better off
> using a trigger on the parent table to direct inserts/updates/deletes to
> the children. As a bonus, using a trigger makes it a lot more realistic
> to deal with an update moving data between partitions.

In our application, data is never moved between partitions.

The problem I found with triggers is the non-robustness of the PLpgSQL
record data type. For example, in an "on insert" trigger, I can't determine
the fields of the NEW record unless I hard code the column names into the
trigger. This makes it hard to write a generic trigger, which I can use for
all our partitioned tables. It would have been somewhat of a pain to write
a separate trigger for each of our partitioned tables.

For that and other reasons, we moved some of the insert logic up to the
application level in our product.

Steve