On Jan 6, 2008 3:00 AM, Robert Treat <
xzilla@users.sourceforge.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.