LLM Best Practices

Postgres: Full-Text Search

Last updated 3 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

Postgres full-text search is enough when search is a feature inside a transactional system. tsvector holds tokenized, stemmed terms, tsquery expresses the search, and a GIN index makes it fast; pg_trgm adds fuzzy and substring matching. Store a weighted tsvector in a STORED generated column, query with websearch_to_tsquery, and switch to a dedicated engine when search is the product. The umbrella rules live in postgres.

Store a weighted tsvector in a STORED generated column

A generated column keeps the tsvector in sync with its source. Write STORED explicitly: since Postgres 18, generated columns are virtual by default, and a virtual column cannot be indexed.

ALTER TABLE articles ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED;
 
CREATE INDEX articles_search_gin ON articles USING GIN (search);

to_tsvector('english', ...) lowercases, drops stop words, and stems (running to run). For multilingual content, store the language in a regconfig column and pass it to to_tsvector. Use the same configuration at query time; mismatched configurations silently miss results. See postgres-indexes for the index decision tree.

Query with websearch_to_tsquery

Pick the parser by input source: websearch_to_tsquery for end-user text (quotes, - exclusion, or), plainto_tsquery to require all words, phraseto_tsquery for ordered phrases.

SELECT id, title FROM articles
WHERE search @@ websearch_to_tsquery('english', 'postgres -mysql "full text"')
LIMIT 50;

Weight fields and rank results

setweight tags tokens A to D. The default ts_rank weights are {0.1, 0.2, 0.4, 1.0} for {D, C, B, A}; pass a custom array as the first argument to change them. Use A for titles, B for body text, and C or D for metadata and comments.

ts_rank_cd also accounts for term proximity. The normalization bitmask controls document length effects: 0 ignores length (the default), 2 divides by document length, 16 divides by 1 + log(unique words), and 32 divides the rank by itself plus 1 to map it into 0 to 1.

SELECT id, title, ts_rank_cd(search, query, 32) AS rank
FROM articles, websearch_to_tsquery('english', $1) AS query
WHERE search @@ query
ORDER BY rank DESC LIMIT 50;

Ranking is the part teams outgrow first. Postgres ranks by term frequency and proximity and has no BM25.

pg_trgm indexes three-character chunks. It supports similarity (%), fast ILIKE '%foo%', and typo-tolerant matching.

CREATE EXTENSION pg_trgm;
CREATE INDEX users_name_trgm ON users USING GIN (name gin_trgm_ops);
 
SELECT id, name, similarity(name, $1) AS score
FROM users WHERE name % $1 ORDER BY score DESC LIMIT 10;

Use it for autocomplete, name search, and SKU lookup. It does not stem, so combine it with tsvector when both fuzzy and stemmed matching matter.

Debug zero-result searches with ts_debug

A bad text search configuration shows up as no results. Run SELECT * FROM ts_debug('english', 'running quickly'); on a known document to see how each token is classified, and \dF in psql to list available configurations.

Switch engines when search is the product

Move to Elasticsearch, OpenSearch, Meilisearch, or Typesense when you need synonyms, per-field analyzers, BM25 relevance, rich faceting, geo-relevance, or very large corpora at low latency. Move to a vector store when relevance is semantic rather than lexical; see rag. A hybrid works: keep Postgres as the source of truth, stream changes with logical replication or CDC into the search engine, and hydrate results from the database. Before blaming Postgres, check the plan with postgres-explain; most slow-search tickets are a missing index.

Backlinks