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.