Postgres Full-Text Search skill for Claude Code

Postgres search is done when the query uses a GIN index (proved by EXPLAIN), user input goes through websearch_to_tsquery so no input can raise a syntax error, ranking is computed only over the matched set with a LIMIT, and the language configuration matches the content. If the requirements exceed what FTS does well, say so early.

Is Postgres enough?

Requirement	Postgres FTS	Move to a search engine
Up to ~10M documents, search alongside transactional filters	yes	-
Stemming, stop words, phrase and prefix search	yes	-
Typo tolerance on names/titles	pg_trgm	typo tolerance across long text
BM25-quality ranking with field boosts, facets with counts at scale	weak (ts_rank is not BM25)	yes, or ParadeDB pg_search extension if allowed
Synonyms managed by non-engineers, merchandising rules, analytics	no	Algolia/Meilisearch/Typesense or Elasticsearch
Many languages per field with auto detection	awkward	yes

Default: start in Postgres when data already lives there and search is a feature, not the product. Re-evaluate when ranking quality complaints persist after tuning weights.

Workflow

- [ ] 1. Pick the text search configuration(s) for the content language
- [ ] 2. Add a weighted tsvector stored generated column
- [ ] 3. Index it with GIN
- [ ] 4. Parse user input with websearch_to_tsquery
- [ ] 5. Rank the matched set, limit, then fetch snippets
- [ ] 6. Add trigram fallback for short fields and typos
- [ ] 7. Verify with EXPLAIN and a judged query list

1. Configuration

SELECT cfgname FROM pg_ts_config;          -- english, german, simple, ...
SELECT to_tsvector('english', 'Running routers'), to_tsvector('simple', 'Running routers');

• english (or the content language) stems and removes stop words; simple only lowercases - use it for codes, names, and tags.
• Accent-insensitive search needs a custom configuration with unaccent:

CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE TEXT SEARCH CONFIGURATION en_unaccent (COPY = english);
ALTER TEXT SEARCH CONFIGURATION en_unaccent
  ALTER MAPPING FOR hword, hword_part, word WITH unaccent, english_stem;

• Always pass the configuration explicitly (to_tsvector('english', ...)). The one-argument form depends on default_text_search_config and cannot be used in an index expression.

2. Weighted generated column

ALTER TABLE articles ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(subtitle, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'D')
  ) STORED;

• coalesce every column: concatenating a NULL makes the whole vector NULL.
• Write STORED explicitly. PostgreSQL 18 makes new generated columns virtual by default, and virtual columns cannot be indexed.
• Adding a stored generated column rewrites the table under an ACCESS EXCLUSIVE lock. On large tables, add a plain column, backfill in batches, and maintain it with a trigger, or schedule the rewrite with the user (see schema-migrations).
• Columns from other tables (tags, author names) cannot appear in a generated column; maintain a trigger-updated column or a separate search table instead.
• to_tsvector truncates positions at 16383 and a tsvector is capped at 1 MB; index the first N KB of very long bodies.

3. Index

CREATE INDEX CONCURRENTLY articles_search_idx ON articles USING gin (search);

GIN is the default for FTS. GiST is lossy and smaller but slower to search; use it only for very write-heavy tables after measuring.

4. Query parsing

SELECT websearch_to_tsquery('english', 'wireless "mesh router" -refurbished or ethernet');
-- 'wireless' & 'mesh' <-> 'router' & !'refurbish' | 'ethernet'

Function	Use
websearch_to_tsquery	raw user input; supports quotes, or, -; never errors
plainto_tsquery	AND of all words, no operators
phraseto_tsquery	exact phrase
to_tsquery	trusted, programmatic input only; errors on bad syntax

Prefix search for search-as-you-type: build the query in code by sanitizing tokens and appending :* to the last one, then pass it to to_tsquery('english', 'mesh & rout:*'). Strip characters & | ! ( ) : * < > and quotes from user tokens first.

5. Rank, limit, snippet

WITH q AS (SELECT websearch_to_tsquery('english', $1) AS query),
hits AS (
  SELECT a.id, a.title, ts_rank_cd(a.search, q.query, 32) AS rank
  FROM articles a, q
  WHERE a.search @@ q.query AND a.published
  ORDER BY rank DESC, a.id
  LIMIT 20
)
SELECT h.id, h.title, h.rank,
       ts_headline('english', a.body, q.query,
                   'MaxFragments=2, MinWords=5, MaxWords=20, StartSel=<mark>, StopSel=</mark>') AS snippet
FROM hits h JOIN articles a USING (id), q
ORDER BY h.rank DESC, h.id;

• The @@ filter uses the index; ranking cannot. Ranking reads every matched row, so very broad queries (“the”, single letters) on big tables are slow - require a minimum query length or cap the candidate set.
• Normalization flag 32 (rank / (rank + 1)) bounds scores; 1 divides by document length to stop long documents dominating.
• ts_headline re-parses the original text and is expensive; call it only for the final page of results, never in the ranking step.
• Blend in freshness or popularity explicitly: ts_rank_cd(search, query) * (1 + log(1 + views)) / (1 + extract(epoch FROM now() - published_at) / 86400 / 30). Keep the formula in one SQL function so it is testable.
• ts_headline output contains raw document text: escape it, then re-insert the marks, or HTML-escape the source before highlighting, to avoid XSS.

6. Trigram fallback

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY products_name_trgm ON products USING gin (name gin_trgm_ops);

-- typo-tolerant name match, best first
SELECT id, name, similarity(name, $1) AS sim
FROM products
WHERE name % $1                      -- uses pg_trgm.similarity_threshold (default 0.3)
ORDER BY sim DESC LIMIT 10;

Field	Use
Long prose (articles, descriptions)	FTS
Short names, titles, SKUs, emails, typo tolerance	trigram (%, word_similarity, ILIKE '%x%')
Both	union FTS hits and trigram hits, dedupe, take max score per id

word_similarity / <% matches a query inside a longer string better than whole-string similarity. Trigram indexes also accelerate ILIKE '%term%' for terms of 3+ characters.

Gotchas

• Mismatched configurations between index and query (english vs simple) silently return nothing; keep the config name in one constant.
• Stop words vanish: searching “to be or not to be” with english yields an empty query (NOTICE, zero results). Detect empty querytree(query) and fall back to simple or trigram.
• Stemming surprises: english stems “news” and “new” differently from what users expect; check ts_debug('english', 'text') when a query misses.
• Large GIN indexes with frequent updates accumulate a pending list; fastupdate makes writes cheap but reads slower until vacuum; monitor on write-heavy tables.
• Counting all matches (count(*) over @@) on broad queries is expensive; show “20+ results” or estimate.
• Multi-tenant search: put tenant_id filters in the same query; combine with a btree_gin composite index (USING gin (tenant_id, search)) when tenants are large.

Verification

• EXPLAIN (ANALYZE, BUFFERS) of the search query shows Bitmap Index Scan on articles_search_idx.
• A judged list of 20-30 real queries: for each, the expected document appears in the top 5. Record pass rate before and after weight or config changes.
• Edge inputs return without error: empty string, only stop words, quotes left open, &|!():*, very long input, non-Latin script.
• p95 latency for the top queries on production-sized data meets the target (e.g. under 100 ms).