📇Full-text indexing

Tokens, not raw substrings

Full-text engines tokenize text, apply language rules (stemming, stopwords), and build inverted posting lists so relevance queries scale. That is a different data structure from scanning every row with LIKE '%needle%': plan for ranking, snippet generation, and dictionary maintenance from day one.

SQL

PostgreSQL tsvector / tsquery

ALTER TABLE papers ADD COLUMN tsv tsvector
GENERATED ALWAYS AS (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(abstract,''))) STORED;

CREATE INDEX papers_tsv_idx ON papers USING gin (tsv);

SELECT paper_id, ts_rank(tsv, websearch_to_tsquery('english', 'crispr cas9')) AS score
FROM papers
WHERE tsv @@ websearch_to_tsquery('english', 'crispr cas9')
ORDER BY score DESC
LIMIT 3;

✓Illustrative result set

paper_id	score
8812	0.912
9021	0.844
7650	0.801

⚡ Advanced / dialect note

Choosing the text search config matters for scientific corpora, consider simple dictionaries or custom stopword lists when acronyms dominate.

FTS treated as magic

Leading wildcard search on OLTP-sized tables disguised as “search.”
Ignoring tokenizer behavior for non-English fields, silent bad recall.
Returning huge unordered hit sets to APIs without LIMIT or ranking cutoffs.

Budget writes and hits

Size FTS memory separately from OLTP buffer pools; monitor merge lag on write-heavy corpora.
Expose highlight snippets for UX, not whole blobs; reindex after parser or dictionary changes.
Pair GiN FTS with trigram when users expect partial substring matches.

🧪Hybrid catalogs

Product search rarely wants only one mechanism.

Exact SKU filters may stay B-tree or hash; fuzzy substring matches often use pg_trgm; token relevance belongs in tsvector: compose them deliberately instead of bolting LIKE onto FTS as a crutch.

Operations

Treat dictionary upgrades like schema migrations: replay a sample corpus, compare hit counts and latency, and schedule reindex windows before rolling config to production.

TL;DR

Full-text search indexes tokenized words (with stemming, stopwords, and language-specific parsers) instead of raw substring scans. Use it for user-facing search boxes and document corpora; keep B-tree indexes for exact keys. Maintenance (rebuilds, noise-word lists) and ranking functions are engine-specific, validate relevance on real data, not toy strings.