Re: Dynamic Partitioning using Segment Visibility Maps

On Fri, 2008-01-11 at 20:03 +0100, Gavin Sherry wrote:

> 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?

Sorry, thought I had somewhere in all those emails...

If you really do have 16TB of data and its in the use case of mostly
read only, volatile in last portion of table, then it would be
straightforward to increase segment size.

Whatever form of partitioning we go for we do need to allow 1-2,000
partitions fairly easily. We can't sustain a sequential method of
applying the rules, which gives O(n) behaviour. We need some form of
indexing/tree search mechanism that gives roughly O(logN) behaviour.

> > 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.

If we do find regressions, then I'd suggest we look at ways of turning
it on/off automatically. We can keep track of the volatility in the map.
We can also have an off switch if you're really against it. But I'm
still skeptical.

I think we can sustain the last few segments in a table being volatile,
as long as the greater proportion of segments are not.

The volatile part of the table is the subject of most of the queries
anyway, so excluding it from seq scans isn't that important. Index
access to historical data doesn't slow down whether or not you have a
volatile map.

> 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'.

You do need to VACUUM anyway, so thats no problem. Sure you have to scan
it to derive the boundary values, but thats one scan that saves many in
the future.

> 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.

SE isn't aimed at solely INSERT-only data. It's much wider than that.

An ERP/SCM type application would easily benefit, say where orders are
received and processed within a relatively short period, but order data
needs to be maintained online for 2+ years. Anything where the table
grows either by date, or by increasing key, that has a read-only "tail".
That's a lot of applications and a ton of data. It might not apply to
the "Customer" table in that app, but it will apply to Orders, OrderItem
etc.

We can compress older read-only data as a way of shrinking file size.
That's way easier, plus it applies to more real-world cases than trying
to remove all the visibility stuff. The Insert-only people will still be
able to take advantage of it compression, but the more general purpose
people will never be able to make use of the visibility removal code.

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