Re: table partioning performance

On 1/6/07, Colin Taylor <colin(dot)taylor(at)gmail(dot)com> wrote:

> Hi there, we've partioned a table (using 8.2) by day due to the 50TB of
> data (500k row size, 100G rows) we expect to store it in a year.
> Our performance on inserts and selects against the master table is
> disappointing, 10x slower (with ony 1 partition constraint) than we get by
> going to the partioned table directly.

Are you implementing table partitioning as described at:
http://developer.postgresql.org/pgdocs/postgres/ddl-partitioning.html ?

If yes, and if I understand your partitioning "by day" correctly, then you
have one base/master table with 366 partitions (inherited/child tables). Do
each of these partitions have check constraints and does your master table
use rules to redirect inserts to the appropriate partition? I guess I don't
understand your "only 1 partition constraint" comment.

We use partitioned tables extensively and we have observed linear
performance degradation on inserts as the number of rules on the master
table grows (i.e. number of rules = number of partitions). We had to come
up with a solution that didn't have a rule per partition on the master
table. Just wondering if you are observing the same thing.

Selects shouldn't be affected in the same way, theoretically, if you have
constraint_exclusion enabled.

Steve