Re: Why is Hash index not transaction safe.

Hi Sameer,

Nice to see you around!

The developer wanted to use HASH indexes as the query hitting some columns
are only using equality comparison (=). They said that HASH has a speed of
O(1) whereas B-Tree has a speed of logarithm. I believe it's true if you
look fundamentally at hashtable and B-Tree algorithm.

However, I did my research and found that HASH index has minimal perfomance
improvement over B-Tree in equality comparison.

Thus, we dropped the idea already :)

Cheers!

On 6 May 2015 at 11:19, Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)com> wrote:

>
>
> On Mon, May 4, 2015 at 11:26 AM Wei Shan <weishan(dot)ang(at)gmail(dot)com> wrote:
>
>> Hi all,
>>
>> I read the following about Hash indexes in Heroku's blog (
>> https://devcenter.heroku.com/articles/postgresql-indexes)
>>
>> *Hash Indexes are only useful for equality comparisons, but you pretty
>> much never want to use them since they are not transaction safe, *
>>
>
> What does that "transaction-safe" mean? I guess they are *not crash-safe*
> but this not make much sense to me.
>
>
>> *need to be manually rebuilt after crashes, *
>>
>
> True. Since there is no WAL entry made when you create a Hash Index
>
>
>> *and are not replicated to followers, *
>>
>
> Like said above, there is no WAL entry hence the replica standby (which
> depends on WAL segments to reapply the changes coming from master) can not
> receive the changes
>
>
>> *so the advantage over using a B-Tree is rather small.*
>>
>
> I don't see a correlation here. Reliability and performance are two
> different things for me in this context (though they may impact each other
> in other context or features in PostgreSQL).
>
> But saying *Hash indexes are not crash-safe hence they have very small
> advantage over B-Tree indexes* is probably not apt!
>
> *Hash indexes are not crash-safe and their is small advantage over B-Tree
> indexes* is probably more accurate.
>
>
> Could anyone explain about why is it not transaction safe as compared to
>> B-Tree index.
>>
>
> Any specific reason you plan to use them?
>
>
>>
>> Thanks!
>> --
>> Regards,
>> Ang Wei Shan
>>
>

--
Regards,
Ang Wei Shan