PostgreSQL Full-Text Search for Lexical Retrieval

Full-text search retrieves by words and linguistic forms, making it strong for exact names, error codes, product identifiers, and rare terms that dense embeddings may blur. PostgreSQL converts documents to weighted tsvector values, queries to tsquery, uses GIN indexes for matching, and ranks results. Combine lexical and semantic retrieval rather than forcing one to solve both.

What will you be able to do?

Create stored search vectors and GIN indexes.
Parse user queries safely with language configurations.
Weight titles and body text.
Rank lexical candidates.
Prepare results for hybrid fusion.

How do you create a search document?

sql

ALTER TABLE app.chunks
ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (
  setweight(to_tsvector('english', coalesce(heading_text, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(content, '')), 'B')
) STORED;

CREATE INDEX chunks_search_vector_gin
ON app.chunks USING gin (search_vector);

This fixed English configuration is only an example. For multilingual content, use per-language columns/partitions or an update trigger that selects an allowlisted configuration. Configuration names are identifiers and should not come directly from a request.

How do users query it?

sql

WITH q AS (
  SELECT websearch_to_tsquery('english', $1) AS query
)
SELECT c.id, c.content,
       ts_rank_cd(c.search_vector, q.query) AS lexical_score
FROM app.chunks c
CROSS JOIN q
WHERE c.tenant_id = $2
  AND c.search_vector @@ q.query
ORDER BY lexical_score DESC, c.id
LIMIT 50;

websearch_to_tsquery accepts familiar web-search syntax and avoids raw to_tsquery syntax errors for ordinary user text. Still limit query length and complexity.

What about exact identifiers?

Keep normalized identifier columns with B-tree or trigram indexes where needed. Full-text tokenization can split punctuation in SKUs, versions, stack traces, or paths. Hybrid retrieval may include an exact-identifier branch with a strong business rule.

How do you verify it?

Test stemming, stop words, phrases, negation, exact IDs, misspellings, and every supported language. Inspect to_tsvector output so you see lexemes and positions. Confirm GIN usage with EXPLAIN, and compare lexical recall with dense retrieval on identifier-heavy queries.

What breaks in production?

English stemming is applied to every language.
Raw to_tsquery receives untrusted syntax.
Exact identifiers are tokenized into useless pieces.
Ranking scores from different retrieval methods are added without calibration.
Search-vector updates add heavy write cost unexpectedly.

AI pair-work prompt

Design PostgreSQL lexical search for these languages, identifiers, fields, and query styles [details]. Propose tsvector construction, weights, safe query parser, GIN indexes, exact-ID branches, fixtures, and metrics. Explain multilingual limitations.

Verification contract: Evaluate lexical search alone before hybrid fusion and include rare identifiers plus no-result cases.

Check your understanding

Why is lexical search strong for error codes?
What does a GIN index accelerate?
Why not feed raw user text to to_tsquery?

Official references