Re: Proposal: global index

Hi Chris,

On 18.08.2017 16:15, Chris Travers wrote:
> I would really like to see global indexes. It would make things a lot
> easier for things like unique constraints across table inheritance trees.
>
> On Fri, Aug 18, 2017 at 11:12 AM, Ildar Musin <i(dot)musin(at)postgrespro(dot)ru
> <mailto:i(dot)musin(at)postgrespro(dot)ru>> wrote:
>
> Hi hackers,
>
> While we've been developing pg_pathman extension one of the most
> frequent questions we got from our users was about global index
> support. We cannot provide it within an extension. And I couldn't
> find any recent discussion about someone implementing it. So I'm
> thinking about giving it a shot and start working on a patch for
> postgres.
>
> One possible solution is to create an extended version of item
> pointer which would store relation oid along with block number and
> position:
>
> struct ItemPointerExt
> {
> Oid ip_relid;
> BlockIdData ip_blkid;
> OffsetNumber ip_posid;
> };
>
> and use it in global index (regular index will still use old
> version). This will require in-depth refactoring of existing index
> nodes to make them support both versions. Accordingly, we could
> replace ItemPointer with ItemPointerExt in index AM to make unified
> API to access both regular and global indexes. TIDBitmap will
> require refactoring as well to be able to deal with relation oids.
>
>
> So, to be clear on-disk representations would be unchanged for old
> indexes (ensuring that pg_upgrade would not be broken), right?

Yes, the idea is to keep old indexes untouched so that there would be no
need in any further conversion. And global indexes in turn will have
extended TID format.

>
>
> It seems to be quite an invasive patch since it requires changes in
> general index routines, existing index nodes, catalog, vacuum
> routines and syntax. So I'm planning to implement it step by step.
> As a first prototype it could be:
>
> * refactoring of btree index to be able to store both regular and
> extended item pointers;
>
>
> Do you foresee any performance implementation of handling both?

It's hard to say until there is some working prototype. I think there
can be (and most like will be) some overhead due to unified API (and
hence addition conversion operations). It will require benchmarking to
say how bad is it.

> --
> Best Regards,
> Chris Travers
> Database Administrator
>
> Tel: +49 162 9037 210 | Skype: einhverfr | www.adjust.com
> <http://www.adjust.com/>
> Saarbrücker Straße 37a, 10405 Berlin
>

--
Ildar Musin
i(dot)musin(at)postgrespro(dot)ru