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?

Database Locking & Concurrency Cheat Sheet

Database Partitioning Cheat Sheet