I am trying to implement full text search using postgresql for some images. I am storing some information about my images into a json field in my table. This json has a tags key where I have multiple languages, each one with tags (keywords), something like this:

"tags": {
    "en": ["blue female", "red female"],
    "es": ["hembra azul", "hembra roja"]

At this moment I don't have a clear idea how to store the tsvector considering that I have more languages.

One initial idea was to concatenate all those tsvectors into a single one and store it on a column in my table.

The second idea would be to make a different column for each language and store the corespondent vector into that column.

Which on would be the better one? There is maybe another better approach?

  • Are people going to specify which language they are searching in when they do a search? An important part of FTS is stemming, and stemming is language dependent and must be done the same way on both vector and query. That means that if you want to do searching without specifying the language being searched in, you should turn off stemming. If you do it that way, you might as well make one concatenated tsvector (or functional index on the concatenated vector). – jjanes Jan 27 at 16:44
  • Thank you for your reply jjanes, unfortunately the users can choose in what language they want to make the search – melokki Jan 28 at 8:32

You should definitely use a different column per language.

The main reason is that different languages have different stop words and stemming rules, so if you index something with to_tsvector('spanish', ...), you will not always find it with a to_tsquery('english', ...) and vice versa:

SELECT to_tsvector('spanish', 'hembra azul') @@ to_tsquery('english', 'hembra');
(1 row)

Even better would be not to create a column per language, but only GIN indexes on to_tsvector('english', (tags->'tags'->'en')) and to_tsvector('spanish', (tags->'tags'->'es')). For example:

   image bytea NOT NULL,
   tags jsonb NOT NULL

CREATE INDEX images_tags_en_idx ON images
   (to_tsvector('english', (tags->'tags'->'en')));

CREATE INDEX images_tags_es_idx ON images
   (to_tsvector('spanish', (tags->'tags'->'es')));

Then you can use the first index with

SELECT * FROM images
WHERE to_tsvector('english', (tags->'tags'->'en'))
      @@ to_tsquery('english', 'female');
|improve this answer|||||
  • Thank you very much for your answer Laurenz, but if I create just the GIN index how will I select my data then? – melokki Jan 27 at 13:22
  • I have added an example. – Laurenz Albe Jan 27 at 13:33
  • thank you very much Laurenz, I've tested your solution and it's quite fast, going with this implementation – melokki Jan 30 at 6:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.