Why unaccent function in Postgres is a stable function? Will the precomputation in column produce the different outputs?

Question

I am optimizing the search query in the Postgres database. The query uses the unaccent function. I want to create a gin-index to make the search query faster. So I had gone through some articles on faking the stable function as an immutable function using wrapper functions. I was thinking of creating a new column for precomputed unaccented texts would be good. But I got to know that the unaccent function can produce different outputs. So what is the preferred way? Precomputation or using a wrapper function to deceive the program?

Answer 1

You can read this Postgres doc about unaccent extension, in my opinion, there are two cases :

1- Add slug columns to tables :

Pros :

• Slugify concerned table columns at once for the existing DB
• Search by slug.

Cons :

• Redundant data, almost if you need to implement multi-criteria searching, in this case you should create a slug(without diacritic signs) for all concerned columns

• Update columns mean update all concerned slug columns

• Create data migrations for the existing database with automated slug creation using Slugify for instance.

2- Use unaccent extension

Pros :

• Don't need to create new db columns
• Stable and trusted extension
• Easy to implement

Cons :

• Need data migration to install the extension on PROD ENV
• In case of changing DBMS, you will need to replace the extension