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, 2020 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, 2020 at 8:32

1 Answer 1


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');
  • 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, 2020 at 13:22
  • I have added an example. Jan 27, 2020 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, 2020 at 6:06
  • If we have 4 langauge we need 4 index and 4 query use or (to_tsvector('english', english_text) or (to_tsvector('german', german_text). How about when a column can be english and german or other language. We only create one custom confiugre?
    – jiamo
    Jul 28, 2020 at 7:38
  • @jiamo Then the different indexes will have to be created on the same column. Since stemming is different for different languages, this may result in surprising false positive results, but that cannot be helped if you don't know the language in advance. Aug 10, 2020 at 6:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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