Re: Dynamic Partitioning using Segment Visibility Maps

On Sat, 2008-01-05 at 16:30 -0500, Robert Treat wrote:

> I'm not following this. If we can work out a scheme, I see no reason not to
> allow a single table to span multiple tablespaces.

That seems to be something we might want anyway, so yes.

> The difference is that, if I currently have a table split by month, I
> can "re-partition" it into weekly segments, and only shuffle one months data
> at a time minimize impact on the system while I shuffle it. This can even be
> used to do dynamic management, where data from the current month is archived
> by day, data from the past year by week, and data beyond that done monthly.

Understood

> On many other databases, if you change the partition scheme, it requires
> exclusive locks and a shuffleing of all of the data, even data whose
> partitions arent being redefined. Even worse are systems like mysql, where
> you need to rewrite the indexes as well. To me, these requirements always
> seem like show stoppers; I generally can't afford to lock a table while the
> database rewrites a billion rows of data.

Agreed

> In any case, my thinking is if we had the segment exclusion technique, I could
> convert that partitioned table into a regular table again, use segment
> exclusion to handle what is currently handled by partitions, and create
> a "global index" across all the other data for that other, currently killer,
> query.

Yes, that's what I have in mind.

Can I ask that you produce a "gap analysis" between what you have now
and what you would have in the future, so we can see what omissions or
errors there are in the segex proposal?

If we had indexes that spanned partitions, would we find that some of
the queries that were producing seq scans will now produce better join
and index plans, do you think?

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