Re: Dynamic Partitioning using Segment Visibility Maps

On Fri, Jan 11, 2008 at 11:49:50AM +0000, Simon Riggs wrote:
> On Fri, 2008-01-11 at 10:25 +0100, Gavin Sherry wrote:
> > >
> > > Of course. It's an identical situation for both. Regrettably, none of
> > > your comments about dynamic partitioning and planning were accurate as a
> > > result.
> >
> > That's not true. We will still have planning drive the partition
> > selection when the predicate is immutable, thus having more accurate
> > plans.
>
> Not really.
>
> The planner already evaluates stable functions at plan time to estimate
> selectivity against statistics. It can do the same here.
>
> The boundary values can't be completely trusted at plan time because
> they are dynamic, but they're at least as accurate as ANALYZE statistics
> (and probably derived at identical times), so can be used as estimates.
> So I don't see any reason to imagine the plans will be badly adrift.

Okay, it's good that you want the planner to look at those. Did you
consider the point I made about the sheer amount of data the planner
would have to consider for large cases?

> We're back to saying that if the visibility map is volatile, then SE
> won't help you much. I agree with that and haven't argued otherwise.
> Does saying it make us throw away SE? No, at least, not yet and not for
> that reason.

Yes, I'm not against SE I just think that only having it would see a
serious regression for larger user. Personally, I think SE would be a
great idea for append only tables since it removes the thing I'm most
worried about with it: the need to vacuum to 'turn it on'.

>
> SE does what I was looking for it to do, but doesn't do all of what
> you'd like to achieve with partitioning, because we're looking at
> different use cases. I'm sure you'd agree that all large databases are
> not the same and that they can have very different requirements. I'd
> characterise our recent positions on this that I've been focused on
> archival requirements, whereas you've been focused on data warehousing.

I think that sums it up, although I'd also say that declarative
partitioning is suitable for all those with largish amounts of data
which they know how they want stored. This points to another case that
SE suits: those who don't know how or (maybe more importantly) don't
care to manage their data.

I'll go back to what I said above. SE looks like a good performance
boost for archival read only data. If we tighten up the definitions of
how some tables can be used -- append only -- then we can remove the
vacuum requirement and also change other characteristics of the storage.
For example, reduced visibilty information, compression, etc. These are
hot topics for people with that kind of data.

> The difference really lies in how much activity and of what kind occurs
> on a table. You don't unload and reload archives regularly, nor do you

I couldn't agree more.

> perform random updates against the whole table. I'm sure we'd quickly
> agree that many of the challenges you've seen recently at Greenplum
> would not be possible with core Postgres, so I'm not really trying too
> hard to compete.

There we diverge. Yes, Greenplum produces systems for very large amounts
of data, peta-byte range in fact. However, the architecture --
individual post-masters on a single CPU with their own storage -- means
that we see the problems of non-distributed database users when we look
at data at the node level. This is why I say that VACUUMing such
systems, under the SE model, after you do a load of data is just
impossible (I know that after time the cost of vacuum will stabilise but
there's always the initial data load).

Thanks,

Gavin