Re: Why is Hash index not transaction safe. - Mailing list pgsql-novice

On Mon, May 4, 2015 at 11:26 AM Wei Shan <weishan.ang@gmail.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