Full-Text Search Indexes
Specialized indexes that tokenize, stem, and index text content for natural language search queries, enabling fast keyword and phrase matching.
Description
Full-text search (FTS) goes beyond LIKE '%keyword%' (which cannot use indexes and requires sequential scans) by parsing text into tokens, normalizing them (stemming, lowercasing, removing stop words), and storing them in a searchable index. In PostgreSQL, text is converted to a tsvector (a sorted list of lexemes) and search queries are expressed as tsquery objects. A GIN index on the tsvector column enables sub-millisecond search across millions of documents.
PostgreSQL's built-in FTS supports language-aware stemming (so 'running' matches 'run'), ranking by relevance (ts_rank, ts_rank_cd), phrase matching ('web <-> development'), prefix matching ('post:*'), and highlighting matched terms (ts_headline). You can configure custom dictionaries, stop words, and text search configurations per language. For multi-column search, create a computed tsvector that combines weighted fields: setweight(to_tsvector(title), 'A') || setweight(to_tsvector(body), 'B').
PostgreSQL FTS is sufficient for many applications, but it has limitations compared to dedicated search engines like Elasticsearch or Meilisearch: no built-in typo tolerance, limited faceting support, no distributed search across multiple nodes, and weaker relevance tuning. If you need autocomplete with typo tolerance, faceted filtering, or search across millions of documents with sub-100ms response times, consider a dedicated search engine with PostgreSQL as the source of truth, synchronized via change data capture or periodic reindexing.
Prompt Snippet
Implement PostgreSQL full-text search using a generated tsvector column with GIN index: ALTER TABLE articles ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(body, '')), 'B')) STORED. Create a GIN index: CREATE INDEX idx_articles_search ON articles USING gin(search_vector). Query with ts_rank for relevance ordering: SELECT *, ts_rank(search_vector, query) AS rank FROM articles, plainto_tsquery('english', $1) query WHERE search_vector @@ query ORDER BY rank DESC LIMIT 20. For typo tolerance and autocomplete, evaluate Meilisearch synced via a PostgreSQL LISTEN/NOTIFY trigger.Tags
Related Terms
Database Indexing Strategies
Creating and managing indexes to accelerate query performance while balancing write overhead and storage costs.
Database Views
Named SQL queries stored in the database that act as virtual tables, simplifying complex queries and providing a stable abstraction layer over the underlying schema.
JSONB Columns (PostgreSQL)
Binary JSON columns in PostgreSQL that enable storing and querying semi-structured data with indexing support, bridging relational and document database capabilities.