Date: Thu, 10 Oct 2024 22:36:47 +0200
From: Erik Wienhold <ewie@ewie.name>
To: sud <suds1434@gmail.com>
Cc: xof@thebuild.com, pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: Question on indexes
Message-ID: <aab7370a-4767-496f-9492-8fc2e10a72d1@ewie.name>
References: <CAD=mzVWLN35FHPxN0Ajh2vnDkFLyg9f0vT6EKF9uPm7CYkHS5g@mail.gmail.com>
 <88e5c335-c70e-4973-9b71-6c12e251e5b7@ewie.name>
 <CAD=mzVWHC8O+fLksgc-uKAcq19xQSVvHz1AsButFmL+xh0_btg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <CAD=mzVWHC8O+fLksgc-uKAcq19xQSVvHz1AsButFmL+xh0_btg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/aab7370a-4767-496f-9492-8fc2e10a72d1%40ewie.name>
Precedence: bulk

On 2024-10-10 21:44 +0200, sud wrote:
> 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.

Your first mail says that you're using version 15.4.  You should
consider upgrading to 15.8 to get the latest bugfixes.

> Did you experience any such thing with hash index?

No.  But I can't remember ever seeing a hash index in the databases that
I've worked on.

> 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.

Define "large".

What kind of text (natural, JSON, XML, base64, DNA sequences, etc.) is
stored in those columns?  Why do you want/need to index those columns?
Because hash indexes only support the equal operator, one can only use
that index to search for exact matches (i.e. values with identical hash
code) which I find strange for values that are so large that btree
cannot be used.  But maybe you have solid use case for that.

If it's natural text and you're using tsvector for full-text search,
then GiST or GIN indexes are a better choice:
https://www.postgresql.org/docs/current/textsearch-indexes.html

-- 
Erik