Re: Dynamic Partitioning using Segment Visibility Maps

Hi,

Robert Treat 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?

Uhm... well, no. I was just pointing out that it's a requirement. It 
depends on how you define things, but I'm seeing it that way:

table -- 1:n -- partition -- 1:1 -- table space -- 1:n -- segments

What I'm advocating is making partitions available to the DBA as some 
kind of a relation, she can query separately and move around between 
table spaces.

>> Why should that not be possible with other schemes? Moving the split
>> point between two partitions involves moving tuples around, no matter if
>> you are going to move them between segments or between relations
>> building the partitions.
> 
> The difference is that, if I currently have a table split by month, I 
> can "re-partition" it into weekly segments, and only shuffle one months data 
> at a time minimize impact on the system while I shuffle it. This can even be 
> used to do dynamic management, where data from the current month is archived 
> by day, data from the past year by week, and data beyond that done monthly.

This should be possible for both schemes, I see no connection to what 
we've discussed. SE doesn't magically give you this level of control you 
are requesting here. Quite the opposite: referring to CLUSTERing to 
makes me wonder, if that's not going to shuffle way too many tuples around.

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

> 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.

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

Regards

Markus