notes.billmill.org / databases / postgres /

Postgres full text search

last updated: Oct 20, 2023

Postgres has two types of text search, full text and trigram

full text search

documented here: https://www.postgresql.org/docs/14/textsearch.html

Useful article, "optimizing postgres]trigram search": https://alexklibisz.com/2022/02/18/optimizing-postgres-trigram-search.html

why not full-text search?
From some experimenting with Full Text Search, I’ve found the API focuses more narrowly on natural language text (i.e., words, spaces, punctuation), than on general-purpose text (i.e., natural language text and product SKUs and email addresses, …)

why not to use?

gitlab hit scaling limits with both trigram search and full-text search, and documented their journey from postgres to elasticsearch:

GitLab’s evolution from Postgres Trigrams to Elasticsearch Fast Search Using PostgreSQL Trigram Text Indexes (March 2016), Lessons from our journey to enable global code search with Elasticsearch on GitLab.com (March 2019), Update: The challenge of enabling Elasticsearch on GitLab.com (July 2019); Update: Elasticsearch lessons learnt for Advanced Global Search 2020-04-28 (April 2020); Troubleshooting Elasticsearch ↩

↑ up