Re: Dynamic Partitioning using Segment Visibility Maps

On Saturday 05 January 2008 10:42, Markus Schiltknecht wrote:
> > The main proposal deliberately has few, if any, knobs and dials. That's
> > a point of philosophy that I've had views on previously: my normal
> > stance is that we need some knobs to allow the database to be tuned to
> > individual circumstances.
> >
> > In this case, partitioning is way too complex to administer effectively
> > and requires application changes that make it impossible to use for
> > packaged applications. The latest Oracle TPC-H benchmark uses 10 pages
> > of DDL to set it up and if I can find a way to avoid that, I'd recommend
> > it to all. I do still want some knobs and dials, just not 10 pages
> > worth, though I'd like yours and others' guidance on what those should
> > be. Oracle have been responding to feedback with their new interval
> > partitioning, but its still a multi-table approach in essence.
>
> I can absolutely support your efforts to minimize knobs and
> configuration DDL. However, my current feeling is, that segments based
> partitioning complicates things, because the DBA doesn't have tools and
> commands to handle segments.
>

Personally I cant say it complicates things, because it isn't clear how it
will be managed. :-)

> To satisfy all the different requirements of partitioning with segments
> based partitioning, we'd have to allow a table to span multiple table
> spaces. I'm not very keen on going that way.
>

Why?

> However, what I certainly like is the automated split point definition.
> Instead of having to create tables by hand and "linking" them via
> inheritance and constraint exclusion, I have something very similar in
> mind, like what you proposed for marking read-only segments. Something
> like:
>
> SPLIT TABLE customers AT cust_name > 'n';
>
> or:
>
> SPLIT TABLE inventory AT inv_id % 4 >= 2;
>
> In my imagination, this should automatically create the underlying
> relations, i.e.:
>
> NOTICE: relations inventory__l and inventory__r have been created.
>
> That way, the DBA could then handle those like normal relations,
> querying them or moving them to different table spaces like all other
> normal relations.
>
> In a way, that's not so different from possible extensions on top of
> Segment Exclusion, except that the DBA additionally get a relation name
> to be able to address the set of segments which form a partition. Or put
> it the other way around: go for Segment Exclusion, but add some sort of
> a sentinel relation for each set of segments, to make them reachable for
> the DBA.
>

So the one thing that always scares me about these "define it all and let the
database sort it out" methods is they seem to lead to cases where the system
ends up rewriting the data to fit into some new partition layout. One thing
that is nice about the current partitioning scheme is you can control the
impact of this behavior in these scenarios, but moving around small portions
of the table at a time.

> > My observation of partitioned databases is that they all work
> > beautifully at the design stage, but problems emerge over time. A
> > time-based range partitioned table can often have different numbers of
> > rows per partition, giving inconsistent response times. A
> > height-balanced approach where we make the partitions all the same size,
> > yet vary the data value boundaries will give much more consistent query
> > times and can be completely automated much more easily.
>
> Uh.. well, consistent query time isn't the first thing I'm expecting
> from partitioning by time ranges. If I wanted consistent query times I'd
> rather use hash partition or something, no?
>

More to the point (I think) is that people define access to the data based on
the meaning of the data, not how it is stored on disk. For example, in some
tables we only need to be active on 1 months worth of data... how that is
laid out on disk (# partitions, which tablespaces) is a means to the end of
working actively on 1 months worth of data. I can't think of many cases where
people would actually say the want to work actively on the most recent GB of
data.

> I'd even state, that one *wants* inconsistent response times when using
> time based range partitioning, by moving old, seldom used data to slower
> storage and keeping only a small amount of often used tuples on the
> faster disks, for example.
>

Yes, we do this quite a bit; and at least one of our partitioned tables (in
total) is larger in size than our tablespace with the fastest disks.

> > The SVM concept doesn't cover everything that you can do with
> > partitioning, but my feeling is it covers the main use cases well.
>
> As I regard manageability to be the main advantage of partitioning,
> which you've intentionally left out for now, I disagree here.
>
> How could SVM or Segment Exclusion potentially be covering what hash
> partitioning does? Maybe together with the ability to store different
> segments of a table on different table spaces. That could be considered
> an approach to range partitioning. But then, that would be the
> partitioning, and not SVM or Segment Exclusion. To me, both of SVM and
> SE look much more like an optimization for certain special cases and
> don't have much to do with partitioning.
>

Even if this were true, it might still be a useful optimization. One table I
am thinking of in particular in my system has one query we need to run across
partitions, which ends up doing a slew of bitmap index scans for all the
partitions. If using segment exclusion on it meant that I could get a global
index to help that query, I'd be happy.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL