On 9 November 2011 21:05, 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. > > 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…
That also sounds like an argument against indexes. Since the system knows which parts of data are most often queried, surely it should be managing indexes, not the DBA? I imagine structuring data after the fact would involve rewriting data whereas planning for upcoming data is something DBAs are expected to do anyway using constraints, triggers, indexes etc. And as it stands, what the DBA has to do at the moment is to exploit table inheritance, apply constraints to each of the child tables (mainly for constraint exclusion), then create a trigger to support various types of update/insert/delete. So what we have now is very un-user-friendly, and tends to surprise end users. The flow of data isn't part of the table definition, it's custom-programmed into an event on the parent table. And partitioning may not just be about performance, it can be about organising data and making it more manageable. Although I agree that if it came to partitioning across nodes for parallel access, PostgreSQL could be in a position to make decisions about how that is distributed. > 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. That could be unworkable in a high-load OLTP environment. > In particular, what part of the declarative partitioning system is > intended to take care about creating the right partitions before new > data are sent to them? > > In a first implementation, we could decide to "partition" the data over > an index that the DBA has to pick, and then maintain a "segment index" > which is a map of the data distribution in the physical segments, for > the indexed columns. The granularity could be different and maybe > dynamic so that you start at a block level map index for very little > tables and grow up to a segment map index for huge tables that require > thousands of segments, 1GB each. > > Then the system have to organize data modifications so that it optimizes > the ranges to be the smallest possible on each map entry. And the > executor then has to be able to consult that map at query time and > exclude whole segments of data (or blocks for little tables) when the > segment indexing is able to exclude data. With some tricks because we > realize that depending on the size of the portions you skip you might > not benefit from moving the head on the platter in another way than what > the ongoing seqscan does, but we already have GUCs about that. > > We might also need some internal facilities to lock out per segment (or > rather "map entries") rather than per table so that we have something > like a WHERE clause support for TRUNCATE. Would this solve the same set of problems that partitioning attempts to address? And what about the example case of quarterly data? In your proposed design could you drop an entire set of data without a DELETE? And maybe I'm not looking at it from the right angle. (probably) Although I appreciate some thought is needed about how useful partitioning implementations in other database systems really are. And now to demonstrate some additional ignorance on my part... does the standard cover this? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers