This article was originally published on AI Study Room. For the full version with working code examples and related articles, visit the original post.

Full-Text Search in PostgreSQL: tsvector, tsquery, GIN Indexes

Full-Text Search in PostgreSQL: tsvector, tsquery, GIN Indexes

Full-Text Search in PostgreSQL: tsvector, tsquery, GIN Indexes

Full-Text Search in PostgreSQL: tsvector, tsquery, GIN Indexes

Full-Text Search in PostgreSQL: tsvector, tsquery, GIN Indexes

Full-Text Search in PostgreSQL: tsvector, tsquery, GIN Indexes

Full-Text Search in PostgreSQL: tsvector, tsquery, GIN Indexes

Full-Text Search in PostgreSQL: tsvector, tsquery, GIN Indexes

Full-Text Search in PostgreSQL: tsvector, tsquery, GIN Indexes

Full-Text Search in PostgreSQL: tsvector, tsquery, GIN Indexes

Full-Text Search in PostgreSQL: tsvector, tsquery, GIN Indexes

Full-Text Search in PostgreSQL: tsvector, tsquery, GIN Indexes

Full-Text Search in PostgreSQL: tsvector, tsquery, GIN Indexes

Full-Text Search in PostgreSQL: tsvector, tsquery, GIN Indexes

PostgreSQL's full-text search (FTS) provides built-in text search capabilities without external dependencies. While not as feature-rich as Elasticsearch or Meilisearch, it handles a large class of search needs efficiently.

The FTS Pipeline

Full-text search in PostgreSQL follows this flow:

• Parsing : Break text into tokens (lexemes).

2\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\. Normalization : Convert tokens to a standard form via a dictionary (stemming, stop words). 3\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\. Indexing : Store the normalized tokens in a tsvector. 4\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\. Querying : Match a tsquery against the tsvector using a GIN index.

tsvector and tsquery

tsvector

A tsvector is a sorted list of distinct lexemes with positional information:

SELECT to_tsvector('english',

'The quick brown fox jumps over the lazy dog');

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- 'brown':3 'dog':9 'fox':4 'jump':5 'lazy':8 'quick':2

Notice "jumps" became "jump" (stemming), and "the", "over" are removed (stop words).

tsquery

A tsquery represents a search query:

SELECT to_tsquery('english', 'fox & dog');

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- 'fox' & 'dog'

SELECT to_tsquery('english', 'jump | run');

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- 'jump' | 'run'

SELECT to_tsquery('english', '!cat');

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- !'cat'

SELECT to_tsquery('english', 'quick <-> brown');

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- 'quick' <-> 'brown' (adjacency: quick

---

Read the full article on AI Study Room for complete code examples, comparison tables, and related resources.

Found this useful? Check out more developer guides and tool comparisons on AI Study Room.