Re: Dynamic Partitioning using Segment Visibility Maps

On Sunday 06 January 2008 05:48, Markus Schiltknecht wrote:
> What I'm saying is, that SE doesn't partition the segments into
> different table spaces. Thus I don't consider it "database partitioning"
> in the first place. As I currently understand it, it's:
>
> table -- 1:1 -- table space -- 1:n -- partitions -- 1:n -- segments
>

Ah, was a slight misunderstanding of terminology. I see what your getting at.

> > 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.
>
> I fully agree here. How do you plan to solve that problem on top of SE?
>
> > 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,
>
> .... on a single table space ...
>
> > use segment
> > exclusion to handle what is currently handled by partitions,
>
> .... except, that there is no partitioning (!?!) (between table spaces)
>
> > and create
> > a "global index" across all the other data for that other, currently
> > killer, query.
>
> I thought the table you are referring to is bigger than your fastest
> table space? That would even make it impossible.
>

Nope, different table :-) Although the above global/local one would probably
live entirely on the slow disks, using SE and global index to satisfy the
queries. And really our slow disks aren't exactly slow, but do have poor
concurrency, so we put primarily read data on them to keep writes to a
minimum.

> See where I'm coming from? And why I'm stating that SE is an
> optimization (for seq scans), but not partitioning?
>

Yes, but aiui, SE should allow seq scans to achieve performance similar to
partitioning, especially if the planner can exclude segments based on values
in the data.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL