6 Postgres for full-text search
This chapter covers
- Understanding how Postgres supports full-text search
- Converting textual data into lexemes
- Storing lexemes in the database
- Performing full-text search over lexemes using built-in functions and operators
- Optimizing full-text search performance with GIN and GiST indexes
Postgres has supported various text data types and search operators for years. For instance, we can store text values in a column of the TEXT type and then use the equality (=), pattern matching (LIKE and ILIKE), regular expression matching (~ and ~*), and other operators to query the stored text data. While these capabilities work well for simple text data and queries, they are less effective when searching through large text documents that require understanding linguistic features like word variations or relevance ranking.
Full-text search is another core feature of Postgres that lets us perform advanced searches over textual data of varying size and complexity. Unlike basic text comparison or regex matching, it uses linguistic techniques to tokenize, normalize, rank, and index text data, making searches faster and more relevant.