On Mon, 2007-01-08 at 15:02 -0500, Steven Flatt wrote: > 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. If you are doing date range partitioning it should be fairly simple to load data into the latest table directly. That was the way I originally intended for it to be used. The rules approach isn't something I'd recommend as a bulk loading option and its a lot more complex anyway. > Selects shouldn't be affected in the same way, theoretically, if you > have constraint_exclusion enabled. Selects can incur parsing overhead if there are a large number of partitions. That will be proportional to the number of partitions, at present. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
