Re: Syntax for partitioning

On 9 November 2011 21:05, 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.
>
> 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