Use tsvector for Full-Text Search

Why This Matters

Postgres has built-in full-text search capabilities using tsvector and tsquery. When combined with GIN indexes, it provides fast, relevance-ranked search without external search engines like Elasticsearch.

Use Postgres full-text search when:

• Searching text content: Articles, comments, product descriptions
• Need relevance ranking: ts_rank scores results by relevance
• Want to avoid external services: Keep everything in Postgres
• Moderate search volume: Handles millions of documents efficiently

The Problem

Using LIKE or ILIKE for text search is slow and doesn't rank results:

-- Slow sequential scan, no relevance ranking
SELECT * FROM articles 
WHERE content ILIKE '%postgres%';

-- Problems:
-- - Sequential scan on entire table
-- - No relevance ranking
-- - Can't search for multiple words
-- - No stemming (finds "postgres" but not "postgresql")

The Solution

Use tsvector with a GIN index for fast, ranked search:

-- Add tsvector column and GIN index
ALTER TABLE articles 
ADD COLUMN search_vector tsvector;

CREATE INDEX articles_search_idx 
ON articles USING gin (search_vector);

-- Populate search_vector (or use generated column)
UPDATE articles 
SET search_vector = 
  to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''));

-- Search with relevance ranking
SELECT 
  title,
  ts_rank(search_vector, query) AS rank
FROM articles, 
  to_tsquery('english', 'postgres') query
WHERE search_vector @@ query
ORDER BY rank DESC;

Using Generated Columns

Automatically maintain the search vector:

-- Create generated column (Postgres 12+)
ALTER TABLE articles 
ADD COLUMN search_vector tsvector 
GENERATED ALWAYS AS (
  to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''))
) STORED;

-- Index the generated column
CREATE INDEX articles_search_idx 
ON articles USING gin (search_vector);

Search Operators

• & (AND): 'postgres & database' - both words required
• | (OR): 'postgres | mysql' - either word
• ! (NOT): 'postgres !mysql' - postgres but not mysql
• * (prefix): 'postgres*' - matches postgres, postgresql, etc.

Common Mistakes

• Forgetting the GIN index: Without it, tsvector searches are slow - always create a GIN index.
• Not updating search_vector: If using a regular column, update it when content changes. Use generated columns to avoid this.
• Wrong text search config: Use 'english' for English text, or create custom configs for other languages.
• Ignoring ts_rank: Always order by ts_rank to show most relevant results first.