Re: Dynamic Partitioning using Segment Visibility Maps

On Jan 6, 2008 3:00 AM, Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> wrote:

> On Saturday 05 January 2008 14:02, Markus Schiltknecht wrote:
> > >> 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?
> >
> > Uh.. if a table (RELKIND_RELATION) can only span one table space, as it
> > is now, all of its segments are in the same table space. I don't quite
> > call that partitioning. Well, sure, you could call it so, but then, each
> > and every Postgres table is already partitioned in 1G segments.
> >
> > It all depends on the definitions, but in my world, horizontal
> > partitioning for databases involves multiple table spaces (and is quite
> > useless without that). Calling anything else partitioning is confusing,
> > IMO.
>
> 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. Do you see a problem
> with
> that?
>

If we can span two different partitions under two tablespaces, it would
help concepts like parallel queries, parallel updates etc in data
warehousing. If we allow a single table to span across multiple tablespaces,
the question arises how we would be able to move around different parts of
the table,as we like. Segments, i believe doesn't let the user/DBA draw the
split-points.

>
> In a more general sense, a global index is a an index that spans multiple
> partitions, as opposed to a local index, which is an index on specific
> partitions; postgresql current supports the latter, not the former.
>
> 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.
>
>
> That's a good idea. Local index , then would be equivalent to partial
indexes. But single Table partition maintenance might not be as flexible as
the multi-table partition. In real partitioning, dropping a single partition
should not affect the local indexes of other partitions. But i think that
would be very difficult to implement under this scheme.

Thanks,
Gokul.