Horton

Re: On partitioning

Hannu Krosing <hannu@2ndquadrant.com>

View thread View on postgr.es
From: Hannu Krosing <hannu@2ndQuadrant.com>
To: Andres Freund <andres@2ndquadrant.com>, Hannu Krosing <hannu@2ndQuadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Alvaro Herrera <alvherre@2ndquadrant.com>, Greg Stark <stark@mit.edu>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2014-09-01T17:09:18Z
Lists: pgsql-hackers 
On 09/01/2014 05:52 PM, Andres Freund wrote:
> On 2014-08-29 20:12:16 +0200, Hannu Krosing wrote:
>> It would need to replace plain tid (pagenr, tupnr) with triple of (partid,
>> pagenr, tupnr).
>>
>> Cross-partition indexes are especially needed if we want to allow putting
>> UNIQUE constraints on non-partition-key columns.
> I actually don't think this is necessary. I'm pretty sure that you can
> build an efficient and correct version of unique constraints with
> several underlying indexes in different partitions each. The way
> exclusion constraints are implemented imo is a good guide.
>
> I personally think that implementing cross partition indexes has a low
> enough cost/benefit ratio that I doubt it's wise to tackle it anytime
> soon.
Also it has the downside of (possibly) making DROP PARTITION either
slow or wasting space until next VACUUM.

So if building composite unique indexes over multiple per-partition
indexes is doable, I would much prefer this.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ