CODEWITHSUNDEEP
postgresqlindexingquery-optimizationpsqlunaccent
Krunal Goswami
Krunal Goswami

Reputation: 11

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

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?

Upvotes: 1

Views: 541

Answers (1)

Abderazak Amiar
Abderazak Amiar

Reputation: 808

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

Upvotes: 0

Related Questions