PGDay : How does Full Text Search works?

During last PGDay I gave a presentation how Full Text Search works in PostgreSQL.

This feature is unfortunately not well known. I see several reasons for this:

  • Complexity: The FTS uses unknown notions from DBA: stemming, vector representation of a document …
  • The tendency to use a dedicated tool for full-text search: ElasticSearch, SOLR …
  • PostgreSQL’s advanced features are not known.

However, there are several advantages to use the PostgreSQL FTS:

  • We keep one language: the SQL
  • No duplication of data between the database and the indexing engine
  • It retains consistency:
    • No need to synchronize data in your database with indexing engine
    • A document deleted from the database will not be forgotten in indexing engine
  • We benefit from all advanced features of PostgreSQL, including powerful indexes.
  • The engine is expandable, we can customize the FTS configuration

When I started diving in the FTS I was soon put off by terms that were unknown to me, special syntax with new operators. Instead of making a presentation on the wide possibilities of FTS, I focused on presenting how it work from the base. That is, all the mechanics behind.

Here is the video of the talk:

Here the slides.

Adrien Nayrat
Adrien Nayrat
PostgreSQL DBA Freelance

Postgres and opensource passionate