Re: On partitioning

On 12/13/2014 05:57 PM, José Luis Tallón 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.

Reviewing the existing documentation on this topic I have stumbled on an
e-mail by Simon Riggs from almost seven years ago
http://www.postgresql.org/message-id/1199296574.7260.149.camel@ebony.site

.... where he suggested a way of physically partitioning tables by using
segments in a way that sounds to be quite close to what we are proposing
here.

ISTM that the partitioning meta-data might very well be augmented a bit
in the direction Simon pointed to, adding support for "effectively
read-only" and/or "explicitly marked read-only" PARTITIONS (not segments
in this case) for an additional optimization. We would need some syntax
additions (ALTER PARTITION <name> SET READONLY) in this case.
This feature can be added later on, of course.

I'd like to explicitly remark the potentially performance-enhancing
effect of fillfactor=100 (cfr.
http://www.postgresql.org/docs/9.3/static/sql-createtable.html) and
partitions marked "effectively read-only" (cfr. Simon's proposal) when
coupled with "fullscan analyze" vs. the regular sample-based analyze
that autovacuum performs.
When a partition consists of multiple *segments*, a generalization of
the proposed BRIN index (to cover segments in addition to partitions)
will further speed up scans.

Just for the record, allowing some partitions to be moved to foreign
tables (i.e. foreign servers, via postgres_fdw) will multiply the
usefullness of this "partitioned table wide" BRIN index .... now
becoming a real "global index".

> Just my 2c
>
>
> Thanks,
>
> / J.L.
>
>
>