Thread: Why is Hash index not transaction safe.
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, need to be manually rebuilt after crashes, and are not replicated to followers, so the advantage over using a B-Tree is rather small.
Could anyone explain about why is it not transaction safe as compared to B-Tree index.
Thanks!
-- Regards,
Ang Wei Shan
Ang Wei Shan
Wei Shan <weishan.ang@gmail.com> writes: > 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, need to be > manually rebuilt after crashes, and are not replicated to followers, so the > advantage over using a B-Tree is rather small.* > Could anyone explain about why is it not transaction safe as compared to > B-Tree index. They're not crash-safe because they don't have any WAL support, and WAL-based replication doesn't work for the same reason. But I think the bit about not being transaction-safe is nonsense ... regards, tom lane
HI all, I have the file postmaster.pid - I would like to know what the lines mean? I did Google, but didn't find much. ====================================== [pol@localhost inst]$ more ./data/postmaster.pid 7382 /home/pol/Downloads/software/postgres/inst/./data 1430769205 5432 /tmp localhost 5432001 2195471 [pol@localhost inst]$ ======================================= "inst" is the base install of the PostgreSQL instance. 7382 is the process id of the .../inst/bin/postgres -D ./data process I'm curious as to what this line /home/pol/Downloads/software/postgres/inst/./data means The PostgreSQL install is in /home/pol/Downloads/software/postgres/inst/, but what does the bit of the path inst/./data - I'm unclear as to what the . (dot) in the middle of that path means. Is the 1430769205 a UNIX epoch time (seconds since 1970) since the instance started? 5432 is the port (configurable) /tmp - the PostgreSQL temp (sorting) directory? localhost is my machine name (never bothered to give it anything meaningful). It's the last line 5432001 2195471 that really puzzled me - I don't have a clue what they are about. I'd be grateful for any input on this matter, TIA and rgs, Pól Ua Laoínecháin 2015-05-04 5:11 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>: > Wei Shan <weishan.ang@gmail.com> writes: >> 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, need to be >> manually rebuilt after crashes, and are not replicated to followers, so the >> advantage over using a B-Tree is rather small.* > >> Could anyone explain about why is it not transaction safe as compared to >> B-Tree index. > > They're not crash-safe because they don't have any WAL support, and > WAL-based replication doesn't work for the same reason. But I think > the bit about not being transaction-safe is nonsense ... > > regards, tom lane > > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice -- linehanp@tcd.ie Mob: 00 353 86 864 5772
My apologies to the list. I failed to change the email subject. Please reply to the email with the correct title. Pól Ua Laoínecháin -- linehanp@tcd.ie Mob: 00 353 86 864 5772
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
Hi Sameer,
Thus, we dropped the idea already :)
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.kumar@ashnik.com> wrote:
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 Indexand 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 changesso 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
Ang Wei Shan
On Wed, May 6, 2015 at 02:02:23PM +0800, Wei Shan wrote:
> 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 :)
Yes, we have heard this many times, and PG 9.5 will issue a warning if
you create a hash index:
WARNING: hash indexes are not WAL-logged and their use is discouraged
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
On Sun, May 10, 2015 at 1:17 AM, Bruce Momjian <bruce@momjian.us> wrote: > WARNING: hash indexes are not WAL-logged and their use is discouraged > I'd like to understand better why hash index operations are not inserted into WALs. Thanks, Luca