From: Steven Flatt <steven.flatt@gmail.com>
To: Colin Taylor <colin.taylor@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: table partioning performance
Date: Mon, 8 Jan 2007 15:02:24 -0500
Message-ID: <357fa7590701081202p726c049ej61390dd1816d1369@mail.gmail.com> (raw)
In-Reply-To: <84d3067d0701062037k3229c9ddjbde73b1fd79ee209@mail.gmail.com>
References: <84d3067d0701062037k3229c9ddjbde73b1fd79ee209@mail.gmail.com>
On 1/6/07, Colin Taylor <colin.taylor@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: steven.flatt@gmail.com, colin.taylor@gmail.com
Subject: Re: table partioning performance
In-Reply-To: <357fa7590701081202p726c049ej61390dd1816d1369@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox