Re: On partitioning

On Sun, Dec 14, 2014 at 1:57 AM, José Luis Tallón
<jltallon(at)adv-solutions(dot)net> wrote:
> On 12/13/2014 03:09 AM, Alvaro Herrera wrote:
>>
>> [snip]
>> Arbitrary SQL expressions (including functions) are not the thing to use
>> for partitioning -- at least that's how I understand this whole
>> discussion. I don't think you want to do "proofs" as such -- they are
>> expensive.
>
>
> Yup. Plus, it looks like (from reading Oracle's documentation) they end up
> converting the LESS THAN clauses into range lists internally.
> Anyone that can attest to this? (or just disprove it, if I'm wrong)
>
> I just suggested using the existing RangeType infrastructure for this ( <<,
>>> and && operators, specifically, might do the trick) before reading your
> mail citing BRIN.
> ... which might as well allow some interesting runtime optimizations
> when range partitioning is used and *a huge* number of partitions get
> defined --- I'm specifically thinking about massive OLTP with very deep
> (say, 5 years' worth) archival partitioning where it would be inconvenient
> to have the tuple routing information always in memory.
> I'm specifically suggesting some ( range_value -> partitionOID) mapping
> using a BRIN index for this --- it could be auto-created just like we do for
> primary keys.
>
> Just my 2c

Since we are keen on being able to reuse existing infrastructure, I
think this and RangeType, ArrayType stuff is worth thinking about
though I am afraid we may lose a certain level of generality of
expression we might very well be able to afford. Though that's
something difficult to definitely say without actually studying it a
little more detail which I haven't quite yet. We may be able to go
somewhere with it perhaps. And of course the original designers of the
infrastructure in question would be better able to vouch for it I
think.

Thanks,
Amit