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)
Weighted, Multi-Column Search Vectors
Rank title matches above body matches using setweight and combined vectors.
sql
UPDATE articles SET search_vector = setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(summary, '')), 'B') || setweight(to_tsvector('english', coalesce(body, '')), 'D');-- ts_rank_cd factors in weight AND proximity/cover density of matching termsSELECT id, title, ts_rank_cd(search_vector, query, 32) AS rank -- 32 = normalize by doc lengthFROM articles, to_tsquery('english', 'postgres <-> performance') queryWHERE search_vector @@ queryORDER BY rank DESC;-- <-> is FOLLOWED BY: matches "postgres performance" as adjacent phrase, not just co-occurring terms
Trigram Fuzzy & Typo-Tolerant Search
pg_trgm enables similarity matching and ILIKE-speed substring search via GIN/GiST.
sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;-- Trigram index makes substring/ILIKE queries and similarity() fastCREATE INDEX idx_articles_title_trgm ON articles USING GIN (title gin_trgm_ops);-- Find near-matches even with typos (similarity threshold 0..1)SELECT title, similarity(title, 'postgress indexng') AS scoreFROM articlesWHERE title % 'postgress indexng' -- % operator uses pg_trgm.similarity_thresholdORDER BY score DESC LIMIT 10;-- Combine with tsvector: trigram for typo tolerance, tsvector for relevance/stemmingSELECT set_limit(0.25); -- lower threshold = more permissive fuzzy matches
Custom Dictionaries & Search Configurations
Tune stemming, synonyms, and stop words per language or domain.
sql
-- Inspect and clone the built-in english configuration to customize itCREATE TEXT SEARCH CONFIGURATION app_english (COPY = english);-- Add a synonym dictionary so "js" and "javascript" match each otherCREATE TEXT SEARCH DICTIONARY app_synonyms ( TEMPLATE = synonym, SYNONYMS = app_synonyms -- reads $SHAREDIR/tsearch_data/app_synonyms.syn);ALTER TEXT SEARCH CONFIGURATION app_english ALTER MAPPING FOR asciiword WITH app_synonyms, english_stem;-- Use it explicitly instead of the 'english' defaultSELECT to_tsvector('app_english', 'Learn JS fundamentals');-- Inspect exactly how a query gets parsed and normalizedSELECT * FROM ts_debug('app_english', 'running quickly');
Elasticsearch Facets & Highlighting
Build filterable facets and highlighted snippets on top of a match query.
json
{ "query": { "bool": { "must": { "match": { "body": "database indexing" } }, "filter": { "term": { "category": "engineering" } } } }, "aggs": { "by_category": { "terms": { "field": "category.keyword", "size": 10 } }, "by_year": { "date_histogram": { "field": "published_at", "calendar_interval": "year" } } }, "highlight": { "fields": { "body": { "fragment_size": 150, "number_of_fragments": 2 } } }}
Advanced Search Engine Concepts
Terminology for scaling and tuning search beyond a single-column index.
- Hybrid search- Combines lexical (BM25/tsvector) scoring with dense vector similarity, then merges ranks (e.g. reciprocal rank fusion) for better recall on semantic queries
- Faceted search- Aggregating result counts by category/attribute alongside the query, letting users filter by facet without a second round trip
- Edge n-gram / autocomplete- Indexing prefixes of terms (e.g. "data", "datab", "databa") to power type-ahead suggestions with a single term-prefix query
- Index sharding & replicas- Elasticsearch/OpenSearch split an index into primary shards for write scaling and replicas for read throughput and failover
- Reindexing / zero-downtime alias swap- Build a new index version, backfill it, then atomically repoint a read alias — avoids downtime when a mapping change requires a full rebuild
- Query-time vs index-time boosting- Index-time boosts bake a static weight into the score at ingest; query-time boosts (function_score) apply dynamic weights like recency at search time
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
