Re: Question on indexes

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

--
Erik