Re: Why we lost Uber as a user

* Jim Nasby (Jim.Nasby@BlueTreble.com) wrote:
> On 7/28/16 10:05 AM, Alex Ignatov wrote:
> >>Just curious: what if PostgreSQL supported index that stores "primary
> >>key" (or unique key) instead of tids?
> >
> >You mean IOT like Oracle have?
>
> IIRC, IOT either stores the table in index order, which is something
> different.

IOT is definitely an interesting idea that I'd like to see us pursue,
but I agree that it's something different.

> What Alex is proposing is an index method that stores a datum
> instead of a ctid. You would then use that datum to probe a
> different index to get the ctid. Or put simply, you have a PK index
> that contains ctid's, and a bunch of other indexes that contain a PK
> value instead of ctid's.

Right, that's the MySQL approach, which has advantages and
disadvantages.

> I think it's an idea worth pursuing, but I don't see how you can
> make it work with our MVCC system unless we drop the aversion to
> scanning back into an index as part of an update.

I'm not terribly excited about the MySQL approach, personally, but I
really like the idea of trying to make HOT updates smarter and allow HOT
updates for indexes which don't include TIDs, as Robert and Alvaro are
discussing.

Another thought that was kicking around in my head related to that is if
we could have indexes that only provide page-level information (similar
to BRIN, but maybe a btree) and which also would allow HOT updates.
Those indexes would typically be used in a bitmap index scan where we're
going to be doing a bitmap heap scan with a recheck, of course, though I
wonder if we could come up with a way to do an in-order bitmap index
scan where we sort the tuples on the page and then perform some kind of
mergejoin recheck (or just pull out whatever the lowest-not-seen each
time we sort the tuples on the page).

All very hand-wavy, of course, and it'd make sense to make the concept
work for BRIN before we consider anything else, but it seems like there
could be a use-case for allowing indexes other than BRIN to be built in
a way that allows HOT updates to happen, thus eliminating the cost of
having to update those indexes when the tuple is changed, in many cases.
Of course, those indexes couldn't be used UNIQUE indexes or used for
primary keys, and adjusting the parameters to a BRIN index you could
possibly get a similar index, but this might allow such an index to
still be usable for index-only scans, which a BRIN index will never be
able to provide.

Thanks!

Stephen