Thread: UNIQUE capability to hash indexes

Shubham Barai <shubhambaraiss@gmail.com> writes:
> I wanted to know if anyone is working on these projects from todo list.
> 1.Add UNIQUE capability to hash indexes
> 2.Allow multi-column hash indexes

Not that I've heard of.  It's very hard to muster any enthusiasm for
improving hash indexes unless their lack of WAL-logging is fixed first.
        regards, tom lane

On 2/4/16 1:12 PM, Tom Lane wrote:
> Shubham Barai <shubhambaraiss@gmail.com> writes:
>> I wanted to know if anyone is working on these projects from todo list.
>> 1.Add UNIQUE capability to hash indexes
>> 2.Allow multi-column hash indexes
>
> Not that I've heard of.  It's very hard to muster any enthusiasm for
> improving hash indexes unless their lack of WAL-logging is fixed first.

+1.

I currently steer customers away from hash indexes and have never used
them for any project myself.  They are simply not safe.

--
-David
david@pgmasters.net

Tom Lane wrote:
> Shubham Barai <shubhambaraiss@gmail.com> writes:
> > I wanted to know if anyone is working on these projects from todo list.
> > 1.Add UNIQUE capability to hash indexes
> > 2.Allow multi-column hash indexes
> 
> Not that I've heard of.  It's very hard to muster any enthusiasm for
> improving hash indexes unless their lack of WAL-logging is fixed first.

This is really strange though.  Surely adding WAL-logging is not an
enormous task anymore ... I mean, we're undertaking far larger efforts
now, the WAL logging code is simpler than before, and we even have a
tool (ok, gotta streamline that one a little bit) to verify that the
results are correct.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Tom Lane wrote:
>> Not that I've heard of.  It's very hard to muster any enthusiasm for
>> improving hash indexes unless their lack of WAL-logging is fixed first.

> This is really strange though.  Surely adding WAL-logging is not an
> enormous task anymore ... I mean, we're undertaking far larger efforts
> now, the WAL logging code is simpler than before, and we even have a
> tool (ok, gotta streamline that one a little bit) to verify that the
> results are correct.

ISTR that we discussed this previously and ran into some stumbling block
or other that made it less-than-trivial.  Don't recall what though.
        regards, tom lane

On 02/04/2016 11:34 PM, Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
>> This is really strange though.  Surely adding WAL-logging is not an
>> enormous task anymore ... I mean, we're undertaking far larger efforts
>> now, the WAL logging code is simpler than before, and we even have a
>> tool (ok, gotta streamline that one a little bit) to verify that the
>> results are correct.
>
> ISTR that we discussed this previously and ran into some stumbling block
> or other that made it less-than-trivial.  Don't recall what though.

The last discussion I can recall was Robert's ideas on how to solve the 
splitting of buckets.

http://www.postgresql.org/message-id/CA+TgmoZyMoJSrFxHXQ06G8jhjXQcsKvDiHB_8z_7nc7hj7iHYQ@mail.gmail.com

Andreas

Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > Tom Lane wrote:
> >> Not that I've heard of.  It's very hard to muster any enthusiasm for
> >> improving hash indexes unless their lack of WAL-logging is fixed first.
>
> > This is really strange though.  Surely adding WAL-logging is not an
> > enormous task anymore ... I mean, we're undertaking far larger efforts
> > now, the WAL logging code is simpler than before, and we even have a
> > tool (ok, gotta streamline that one a little bit) to verify that the
> > results are correct.
>
> ISTR that we discussed this previously and ran into some stumbling block
> or other that made it less-than-trivial.  Don't recall what though.

Concurrency of bucket split is the issue. It makes sense to fix the problem
before anyone tries to implement WAL. Otherwise WAL will have to be reworked
from scratch someday.

I had some ideas which I even published:

http://www.postgresql.org/message-id/32423.1427413442@localhost

Then I spent some more time on it sometime in October and improved the concept
a bit (and also found bugs in the version I had published, so please don't
spend much time looking at it). I also wrote a function to check if the
consistent (to be possibly added to pageinspect extension). I even wrote a
function that inserts tuples only into index, not into heap - I suppose that
should make comparison of index performance with and without the patch
simpler.

Now, besides making the patch easier to read, I need to test it
thoroughly. The lack of time is one problem, but I need to admit that it's a
personal issue too :-) So far I think I have a good idea, but now I should try
hard to break it.

Now that I see the problem mentioned again, I feel myself kind of "ignited".
I expect to have some leisure time at the end of February, so I'll test the
patch and post my results early in March.

--
Antonin Houska
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de, http://www.cybertec.at