On Fri, Oct 11, 2024 at 12:51 AM Erik Wienhold <ewie@xxxxxxxxx> wrote:
On 2024-10-10 20:49 +0200, sud wrote:
> However, we are seeing that one of the databases has multiple hash indexes
> created. So I wanted to understand from experts here, if it's advisable in
> any specific scenarios over B-tre despite such downsides?
Two things come to my mind:
1. Btree puts a limit on the size of indexed values, whereas hash
indexes only store the 32-bit hash code.
2. Of the core index types, only btree supports unique indexes.
Example of btree's size limit:
CREATE TABLE b (s text);
CREATE INDEX ON b USING btree (s);
INSERT INTO b (s) VALUES (repeat('x', 1000000));
ERROR: index row requires 11464 bytes, maximum size is 8191
The docs have more details:
https://www.postgresql.org/docs/current/btree.html
https://www.postgresql.org/docs/current/hash-index.html
Thank you.
Not yet confirmed, but actually somehow we see the DB crashed repetitively a few times and teammates suspecting the cause while it tried extending this hash index. Did you experience any such thing with hash index? However, as you mentioned ,if we have any column with large string/text values and we want it to be indexed then there is no choice but to go for a hash index. Please correct me if I'm wrong.
