Re: Partitioning wiki page

On Tue, 2008-12-16 at 21:48 -0500, Emmanuel Cecchet wrote:

> I have put a first draft of the wiki page dedicated to the table
> partitioning development at
> http://wiki.postgresql.org/wiki/Table_partitioning
>
> The page is still incomplete and need much more work but it should be a
> good starting point for discussions and a way to move forward.

I'm glad you're looking to work on it. I have a few comments.

At the moment, all you've done is show examples of how Oracle does
partitioning and restate my requirements document, significant chunks
completely verbatim. If you do that I think it fair to ask for a
specific credit on that. But even so, thank you for the compliment.

Others have suggested different syntax also, so you need to come up with
arguments in favour of this particular syntax. Yes, Oracle does it, but
that doesn't mean its the best way of doing it. Just check out the
number of pages of DDL needed to configure a TPC-H test. That just
happens to be the way they first thought of doing it and everybody has
followed them. Bear in mind they have introduced new ways of doing this
since then.

Oracle's new interval partitioning sounds great, but it ignores the
reality that most data varies considerably over time, either growing or
fluctuating. I much prefer the idea of a size-equalized partitioning
scheme, as implemented by Coppereye's Greenwich. That method gives equal
sized partitions even when data varies. It also needs less DBA input
because you don't even need to specify the time period. (But it probably
also requires autonomous transactions to create new partitions when
required, or at least a way of lock/release that doesn't last until the
end of very long transactions).

So my inclination would be to implement auto-partitioning first and then
spend more time on the optimizer and performance related aspects of this
topic, rather than on user interface.

Also, if we were going to have hash partitioning, I would have thought
we would use a virtual hash, like Teradata. But I'm not sure we want
hash partitioning unless we have multiple nodes (which we don't).

We're in the middle of a CommitFest, so I won't comment further and I
guess you won't get many people passing comment yet either.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support