On Wed, Nov 9, 2011 at 1:05 PM, Dimitri Fontaine <dimi...@2ndquadrant.fr> wrote: > Thom Brown <t...@linux.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
