On 1/6/07, Colin Taylor <colin.taylor@xxxxxxxxx> 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
