Re: Why is Hash index not transaction safe.

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
>