Re: On partitioning

On 12/15/2014 07:42 AM, Claudio Freire wrote:
> [snip]

> If you do that, you start with empty partitions, and each insert 
> updates the BRIN tuple. Avoiding concurrency loss in this case would 
> be tricky, but in theory this could allow very general partition 
> exclusion. In fact it could even work with constraint exclusion right 
> now: you'd have a single-tuple BRIN index for each partition and 
> benefit from it. But you don't need to pay the price of updating BRIN 
> indexes, as min-max tuples for each partition can be produced while 
> creating the partitions if the syntax already provides the 
> information. Then, it's just a matter of querying this meta-data which 
> just happens to have the form of a BRIN tuple for each partition.

Yup. Indeed this is the way I outlined in my previous e-mail.

The only point being: Why bother with BRIN when we already have the 
range machinery, and it's trivial to add pointers to partitions from 
each range?

I suggested that BRIN would solve a situation when the amount of 
partitions is huge (say, thousands) and we might need to be able to 
efficiently locate the appropriate partition. In this situation, a 
linear search might become prohibitive, or the data structure (a simple 
B-Tree, maybe) become too big to be worth keeping in memory. This is 
where being able to store the "partition index" on disk would be 
interesting.

Moreover, I guess that ---by using this approach 
(B-Tree[range]->partition_id and/or BRIN)--- we could efficiently answer 
the question "do we have any tuple with this key in some partition?" 
which AFAICS is pretty close to us having "global indexes".



Regards,
    / J.L.