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

Re: Dynamic Partitioning using Segment Visibility Maps

From: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
To: Markus Schiltknecht <markus(at)bluegap(dot)ch>
Cc: 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 17:25:55
Message-ID: 200801061225.56512.xzilla@users.sourceforge.net (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: jMartinezDate: 2008-01-06 17:32:58
Subject: Re: pg 8.3 Windows installer suggestion
Previous:From: Louis-David MitterrandDate: 2008-01-06 16:12:00
Subject: Re: beta4 accepts foreign key of different type thancolumn

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