Full-text search in PostgreSQL: best practices explained

Most teams reach for Elasticsearch or Algolia the moment search becomes important, but I've learned over the years that PostgreSQL's built-in full-text search is powerful enough to handle production workloads without the operational overhead of another system. In this post, I'll walk you through implementing full-text search in PostgreSQL—covering tsvector columns with GIN indexes, ranking results with ts_rank, combining search with filters in a single query, and adding trigram indexes for fuzzy matching.

Why full-text search matters

Search isn't a nice-to-have feature anymore. Users expect to find what they're looking for quickly, whether they're searching your product documentation, marketplace listings, or support articles. The naive approach—using LIKE with wildcards—falls apart when you have thousands of rows. It's slow, doesn't handle typos, and doesn't understand language nuances like stemming (finding "running" when searching for "run").

PostgreSQL's full-text search solves this without adding infrastructure. I've deployed it in production for e-commerce platforms and SaaS products handling millions of searches monthly. The key insight is that you're trading some upfront schema work for simpler operations and lower latency.

tsvector columns with GIN indexes

At the heart of PostgreSQL's full-text search is the tsvector data type. It's a specialized column type that stores tokenized, normalized text optimized for searching. Instead of storing "The quick brown fox", it stores normalized tokens like "brown", "fox", "quick"—ready for matching.

Let's say you're building a blog platform. You'd want to search posts by title and content. Here's how you set it up:

-- Create the posts table with a tsvector column
CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  search tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', content), 'B')
  ) STORED
);

-- Create a GIN index for fast lookups
CREATE INDEX idx_posts_search ON posts USING GIN (search);

Let me break this down. The search column is a generated column that combines the title and content, processed through to_tsvector with the 'english' language. The setweight function gives more importance to titles (weight 'A') than content (weight 'B')—this affects ranking later.

The GIN index is critical for performance. It creates an inverted index, allowing PostgreSQL to find matching documents in logarithmic time instead of scanning every row. Without it, even with a tsvector column, full-text search queries are slow.

When you insert or update a post, the search column updates automatically. This keeps you honest—no stale indexes to worry about.

Rank results with ts_rank

Finding matching documents is half the battle. The other half is ranking them well. A user searching for "kafka retry" wants the most relevant results first, not just any post mentioning those words.

The ts_rank function scores documents based on how often the query terms appear and their weights. Here's a real example from the outline:

-- Basic search with ranking
SELECT 
  id, 
  title,
  ts_rank(search, websearch_to_tsquery('kafka retry')) AS relevance
FROM posts
WHERE search @@ websearch_to_tsquery('kafka retry')
ORDER BY relevance DESC
LIMIT 20;

The @@ operator checks if a tsvector matches a tsquery. The websearch_to_tsquery function is key—it converts user input like "kafka retry" into a proper query, handling quotes and phrases naturally. Before it existed, you had to use plainto_tsquery (simpler) or to_tsquery (more powerful but requires manual formatting).

ts_rank returns a float between 0 and 1. Posts where search terms appear in the title score higher because of the weight we set earlier. Posts where terms appear in the content score lower. You can tweak the ranking by adjusting weights or using ts_rank_cd (cover density rank) for different semantics.

Combine with filters in one query

Search doesn't exist in isolation. In real applications, users filter results by category, date range, author, or dozens of other fields. You need to combine full-text search with those filters efficiently.

Here's where PostgreSQL shines. You can express everything in a single query:

-- Search + filters in one query
SELECT 
  id, 
  title,
  author_id,
  published_at,
  ts_rank(search, websearch_to_tsquery('kubernetes')) AS relevance
FROM posts
WHERE 
  search @@ websearch_to_tsquery('kubernetes')
  AND author_id = $1
  AND published_at > NOW() - INTERVAL '90 days'
  AND status = 'published'
ORDER BY relevance DESC
LIMIT 20;

The query planner is smart about this. It uses the GIN index to find matching documents, then applies the additional WHERE conditions. With proper indexes on author_id, published_at, and status, this executes in milliseconds even with millions of posts.

This beats fetching search results and filtering in application code. Your database does what it's best at—filtering and sorting large datasets—and your application stays simple.

Add trigram indexes for fuzzy matching

Full-text search works great for exact terms, but what about typos? A user searching for "postgre" (missing an 's') won't find anything with tsvector alone. That's where trigram indexes come in.

A trigram is a three-character substring. The word "postgres" becomes trigrams like "pos", "ost", "stg", etc. PostgreSQL can find similar words by comparing trigrams. Set it up like this:

-- Enable the pg_trgm extension
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Create a trigram index on the title
CREATE INDEX idx_posts_title_trigram ON posts USING GIST (title gist_trgm_ops);

-- Now fuzzy search works
SELECT 
  id,
  title,
  similarity(title, 'postgre') AS sim
FROM posts
WHERE title % 'postgre'  -- % operator means "similar to"
ORDER BY sim DESC
LIMIT 10;

The % operator returns matches similar to the input. The similarity function scores how close the match is. This isn't full-text search—it's substring matching, useful for autocomplete or handling typos.

You can combine trigram indexes with full-text search. For instance, if a tsvector search returns no results, fall back to trigram matching to suggest "Did you mean?" alternatives.

The trade-off is index size. A GIST trigram index takes more space than a GIN tsvector index. For a million-row table, expect a few hundred megabytes. For most applications, that's acceptable.

Common mistakes

Forgetting to index: A tsvector column without a GIN index is pointless. Queries will still scan the whole table. Always create the index before running production queries.

Using the wrong tsquery function: to_tsquery requires you to escape special characters and format queries manually. websearch_to_tsquery is more user-friendly and should be your default. Only use plainto_tsquery if you want all words to be AND'd together without phrase support.

Not setting weights: Every tsvector search feels better when important fields (like titles) rank higher. Use setweight to assign 'A', 'B', 'C', 'D' weights to different fields. Neglecting this results in unintuitive rankings.

Regenerating tsvector on every query: Some teams compute tsvector in a SELECT clause instead of storing it. This defeats the purpose of indexing. Always store tsvector in a column (generated if possible) and index it.

Ignoring language-specific stemming: Using 'english' as the language matters. "Running" stems to "run", matching queries for "run". But if your content is multilingual, you need a strategy—either use a generic language config or store separate tsvector columns per language.

Key takeaways

• PostgreSQL's full-text search via tsvector and GIN indexes handles production search workloads without external systems.
• Use websearch_to_tsquery to convert user input safely into searchable queries.
• Combine full-text search with traditional WHERE filters in one query for efficient, complex searches.
• Set weights on tsvector fields to boost titles over body content in ranking.
• Add trigram (pg_trgm) indexes for fuzzy matching and typo tolerance when needed.
• Always index tsvector columns with GIN; a tsvector without an index is slow.
• Monitor index size and maintenance costs as your data grows, but for most mid-scale applications, the overhead is negligible.