Re: Declarative partitioning grammar

On Tue, 2008-01-15 at 17:12 +0000, Gregory Stark wrote:

> > Wouldn't Segment Exclusion (maybe together with a specialized form of
> > CLUSTERing) handle that case much better than partitioning? Without the need to
> > name all those thousands of partitions and manage them manually.
>
> Firstly we have a philosophical difference here. I think crossing my fingers
> and hoping for a performance boost from some low level infrastructure that I
> can't measure or monitor is not better than managing things so I know
> precisely what's going on and can see it for myself in the plan.

The arguments put forward here apply equally to visibility maps of any
kind, so should equally be applied to both Heikki's VM proposal and
Itagaki's DSM approach. Both of those proposals rely on the idea that
the writes to tables are either low or isolated to particular areas. If
we do anything that relies upon dynamic state information then the
results are, well...dynamic, i.e. they give a variable performance
boost.

I'm not sure why you think it could not be measured or monitored.
Crossing fingers and hoping isn't really a fair comment, unless your
workload varied dramatically from massively random one day to isolated
the next day. Most workloads don't, including your example case.

Sure, they won't help in all cases, but as I've said elsewhere we can
construct dysfunctional test cases that will defeat other tuning
measures such as HOT, TOAST and many other things. If we want to apply
further tuning to Postgres then we should accept that not all tuning
will apply to all cases.

I've accepted that we should have declarative partitioning to cover many
of the cases people have cited, but that doesn't mean a dynamic
partitioning approach is worthless because they aren't mutually
exclusive techniques.

I think we need to see a design for how the internals of partitioning
might work, if it differs from the existing table-level model. Only then
can we see the downsides of both approaches.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com