Some Considerations When Working With Postgres Full-Text Search

Some Considerations When Working With Postgres Full-Text Search

When one uses Postgres as a database, it is tempting to also use Postgres for indexing the data for supporting search features on the site. While ultimately this may be the best fit in many cases, the choice between Postgres Full Text Search (FTS) and external indexing solutions such as ElasticSearch is often done haphazadly, without understanding all the implications from the choice.

Here are some things it is useful to consider when thinking about using Postgres FTS:

  1. Are you supporting multiple languages in the search? Do you wish the search to try to match any language regardlress of the language choice? Depending on how you have implemented your localization in the database, this may be more easy or more difficult.

  2. Is the standard tokenizer sufficient for you? What characters should be matched exactly in your case, and which can be considered word separators?

  3. Do you have fields that need exact matching, such as codes?

  4. Postgres has the concept of stop words, which means that some words are so common they are ignored in the search. Are you going to have problems with this? If you are building cross-language search, stop word in one language might be a valid word in another language.

  5. Do you want to support paging when viewing search results? Does your paging solution require you to provide the number of results in the interface?

  6. What is the minimum number of characters you need to match your query with? If you wish to index with only one character, you may have problems with Postgres.

  7. Do you need prefix-matching? Postgres FTS does not support it directly, but you need to build custom support for it.

  8. Do you wish to show highlighting of the matching text in the front end? How does it need to be communicated? What if there are matches in multiple languages?

  9. Accent characters? How do you want those to behave in the searches?