Re: Syntax for partitioning

On Wed, Nov 9, 2011 at 1:05 PM, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
> Thom Brown <thom(at)linux(dot)com> writes:
>> Whenever I cross the topic of
>> partitioning in PostgreSQL, it's always a tad embarrassing to explain
>> that it's still hacky compared to other database systems (and this
>> came up again last week), so this is of particular interest to me.  At
>
> The more I think about this problem, the more I think that the reason
> why we still don't have declarative partitioning is that it basically
> sucks.  Other vendors offer it because they couldn't do better, but they
> are just asking the DBA to implement a service the system should be able
> to care for itself.

Your last sentence seems to be a better description of PostgreSQL's
current implementation of partitioning via inheritance and constraints
and triggers. Partitioning is a service the system should be able to
care for itself, even if it does need the DBA to declare it. And why
shouldn't it need a DBA to declare it? How is the system supposed to
anticipate that at some point years in the future I will want to run
the command sequence "create foo_archive as select from foo where
year<2009; delete from foo where year<2009", or its partition-based
equivalent, and have it operate on several billion rows cleanly and
quickly? I don't think we can expect the system to anticipate what it
has never before experienced. This is the DBA's job.

> Who knows better than PostgreSQL which part of the data are the most
> often queried and how to best lay them out on disk to ensure QoS?  If
> you think that's the DBA, go ask Tom to implement query hints…
>
> More seriously, partitioning in PostgreSQL could be mostly transparent
> to the users and "just working": it's all about data storage locality
> and we already have a sub-relation storage model. By using segment
> exclusion and some level of automatic clustering (physical organization)
> of data, we could have all the benefits of partitioning without the
> hassle of maintaining yet another explicit level of data definition.

While automatic clustering would be nice, it isn't the same thing as
partitioning.

Cheers,

Jeff