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.