Skip site navigation (1) Skip section navigation (2)

Re: Dynamic Partitioning using Segment Visibility Maps

From: "Gokulakannan Somasundaram" <gokul007(at)gmail(dot)com>
To: "Robert Treat" <xzilla(at)users(dot)sourceforge(dot)net>
Cc: "Markus Schiltknecht" <markus(at)bluegap(dot)ch>, "Simon Riggs" <simon(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org, "Andrew Sullivan" <ajs(at)crankycanuck(dot)ca>
Subject: Re: Dynamic Partitioning using Segment Visibility Maps
Date: 2008-01-06 08:17:01
Message-ID: 9362e74e0801060017r1f6d5587wb8eac8906bf667e9@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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.

In response to

pgsql-hackers by date

Next:From: Gokulakannan SomasundaramDate: 2008-01-06 08:24:02
Subject: Re: Dynamic Partitioning using Segment Visibility Maps
Previous:From: Tom LaneDate: 2008-01-06 08:15:20
Subject: Re: [PATCH] pg_hba.conf.sample: mention www.postgresql.org

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group