Full-Text Search Cheat Sheet
Covers building full-text search with database-native features like Postgres tsvector and dedicated engines like Elasticsearch, including ranking and indexing.
2 PagesIntermediateMar 20, 2026
PostgreSQL Full-Text Search
Build a searchable tsvector column with a GIN index.
sql
ALTER TABLE articles ADD COLUMN search_vector tsvector;UPDATE articlesSET search_vector = to_tsvector('english', title || ' ' || body);CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);-- Keep the vector current automaticallyCREATE TRIGGER trg_articles_tsvectorBEFORE INSERT OR UPDATE ON articlesFOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger(search_vector, 'pg_catalog.english', title, body);-- QuerySELECT id, title FROM articlesWHERE search_vector @@ to_tsquery('english', 'postgres & index');
Ranking Results
Score and highlight matches with ts_rank and ts_headline.
sql
SELECT id, title, ts_rank(search_vector, query) AS rankFROM articles, to_tsquery('english', 'database & performance') queryWHERE search_vector @@ queryORDER BY rank DESCLIMIT 10;-- ts_headline highlights matching terms for UI displaySELECT ts_headline('english', body, to_tsquery('english', 'database'))FROM articles WHERE id = 42;
Elasticsearch Indexing & Search
Create an index, add a document, and search it.
bash
# Create an index with a mappingcurl -X PUT 'localhost:9200/articles' -H 'Content-Type: application/json' -d '{ "mappings": { "properties": { "title": { "type": "text" }, "body": { "type": "text" } }}}'# Index a documentcurl -X POST 'localhost:9200/articles/_doc/1' -d '{"title":"Postgres Indexing","body":"GIN indexes speed up full text search"}'# Search with relevance scoring (BM25 by default)curl -X GET 'localhost:9200/articles/_search' -d '{ "query": { "match": { "body": "full text search" } }}'
Full-Text Search Concepts
Core terminology behind text search engines.
- Tokenization- Breaking text into individual words/terms, typically lowercased and stripped of punctuation before indexing
- Stemming- Reducing words to a root form (e.g., "running" -> "run") so searches match related word forms
- Stop words- Common words ("the", "a", "is") excluded from indexing/queries since they carry little search value
- Inverted index- Maps each term to the list of documents containing it, the core data structure behind fast text search (GIN, Lucene)
- Relevance scoring (TF-IDF / BM25)- Ranks results by how often a term appears in a document relative to its rarity across the corpus
- Fuzzy / typo-tolerant search- Matches near-misses via edit distance (e.g., Elasticsearch's fuzziness parameter, Postgres pg_trgm extension)
Pro Tip
Database-native full-text search (Postgres tsvector + GIN) is often good enough and avoids running a second system — reach for Elasticsearch/OpenSearch only when you need faceted search, typo tolerance at scale, or relevance tuning beyond what a GIN index and ts_rank can deliver.
Was this cheat sheet helpful?
Explore Topics
#FullTextSearch#FullTextSearchCheatSheet#Database#Intermediate#PostgreSQL#Full#Text#Search#Databases#CheatSheet#SkillVeris
