Implement Full-Text Search Indexes

Your LIKE '%needle%' queries are crawling through every row, and your users are waiting seconds for results that should be instant. You've learned to index columns, but indexes don't help when you're searching inside text. The pain is real: fast app, slow search. In this lesson, you'll implement full-text search indexes in PostgreSQL — a game-changing feature that turns text search from a table scan into an index lookup.

The problem this lesson solves

Full-text search is not ILIKE. When you run WHERE title ILIKE '%postgres%', PostgreSQL must scan every row and check every title for the substring. That's a sequential scan — O(n) — and it gets painfully slow as your table grows to millions of rows. Worse, it can't use a regular B-tree index because the pattern starts with %, making the index useless.

But the pain isn't just speed. ILIKE only does exact substring matching; it doesn't understand word boundaries, stemming, or ranking. Searching for "running" won't match "run", and you get no relevance ordering. You need a way to search by meaning, not just by pattern.

PostgreSQL solves this with full-text search (FTS): a built-in engine that parses text into tokens, normalizes them, and matches queries against those tokens. The missing piece? A full-text search index (specifically a GIN index) that makes these queries fast. Without it, FTS is correct but slow. With it, you get both speed and intelligence.

"The index is the key to turning a full-text query from a scan into a lookup." — The PostgreSQL mantra.

Core concept / mental model

Think of full-text search like a library card catalog. Instead of flipping through every book to find a word, the catalog lists every word and which books contain it. PostgreSQL's FTS works similarly:

1. Document: The text you search — a column or combination of columns.
2. tsvector: The parsed and normalized representation of the document — the catalog cards. It stores lexemes (normalized words) and their positions.
3. tsquery: The parsed query — what you're looking for, also normalized.
4. GIN index: The catalog structure — a reverse index mapping each lexeme to the rows containing it.

Here's a word-based diagram:

Document: "The quick brown fox jumps over the lazy dog"
         |
         v
    tsvector: 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2
         |
         v
GIN index keys: 'brown' -> row1, row42
                'fox'   -> row1, row7

Notice the tsvector stored 'lazi' for "lazy" — stemming reduced the word to its root. That's normalization in action. The GIN index then lets PostgreSQL jump directly to matching lexemes instead of scanning.

How it works step by step

Let's trace the full process from raw text to fast query:

1. Create a tsvector expression — You need a function that converts your text columns into a tsvector. The function to_tsvector(config, document) does this. The config determines language rules (e.g., 'english').

python SELECT to_tsvector('english', 'The quick brown fox');

Output: text 'brown':3 'fox':4 'quick':2

Note that 'the' is a stop word and is removed, and 'quick' is normalized.

1. Create a GIN index on the expression — You can't index a function call directly with a normal index. You create a functional index on the tsvector expression.

sql CREATE INDEX idx_articles_fts ON articles USING gin (to_tsvector('english', title || ' ' || body));

This index stores the lexemes for each document's title+body combination.

1. Write FTS queries — Your queries use to_tsquery (or plainto_tsquery, phraseto_tsquery) and the @@ operator to match the indexed expression.

sql SELECT * FROM articles WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'postgres & indexing');

But careful: even with an index, the query must use the same expression as the index. PostgreSQL can optimize this if the expression matches exactly.

1. Optimize with a generated column — To avoid repeating the tsvector expression in every query, you can add a generated column that stores the tsvector permanently.

sql ALTER TABLE articles ADD COLUMN fts tsvector GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED; CREATE INDEX idx_articles_fts ON articles USING gin (fts);

Now queries just reference fts and PostgreSQL uses the index automatically.

Hands-on walkthrough

Let's build a realistic example. We'll create a products table for an e-commerce site and implement full-text search indexes.

Step 1: Set up the table and sample data

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT
);

INSERT INTO products (name, description) VALUES
  ('Running shoes', 'Lightweight shoes for runners and joggers'),
  ('Running jacket', 'Waterproof jacket for running in rain'),
  ('Yoga mat', 'Non-slip mat for yoga and pilates');

Step 2: Create the tsvector generated column and GIN index

ALTER TABLE products
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', name || ' ' || description)) STORED;

CREATE INDEX products_search_idx ON products USING gin (search_vector);

Step 3: Run a full-text search query

SELECT id, name
FROM products
WHERE search_vector @@ to_tsquery('english', 'runners');

Output:

 id |     name      
----+---------------
  1 | Running shoes
  2 | Running jacket

Notice that running isn't the query word, but "runners" matched both rows because of stemming. The GIN index makes this fast.

Step 4: Check that the index is used

EXPLAIN ANALYZE
SELECT * FROM products
WHERE search_vector @@ to_tsquery('english', 'jogging');

Output (simplified):

Bitmap Heap Scan on products  (cost=...)
  Recheck Cond: (search_vector @@ to_tsquery('english', 'jogging'))
  -> Bitmap Index Scan on products_search_idx

If you see Seq Scan instead, the index isn't being used — we'll troubleshoot that later.

Compare options / when to choose what

You now have several ways to search text. Here's a comparison to guide your choice:

When to choose what?

• Start with LIKE for tiny tables (e.g., < 10,000 rows) with simple use cases.
• Use pg_trgm with GIN for substring matching when you need partial word matching but not linguistic features.
• Use full-text search with GIN for most production text search — it's built-in, fast, and supports ranking.
• Consider Elasticsearch/OpenSearch only when you need faceted search, fuzzy matching, or you're already running a cluster.

Variations: - Use plainto_tsquery for user input where you don't expect boolean operators. - Use phraseto_tsquery for exact phrase matching. - Store the tsvector as a regular column (update via triggers) instead of generated if you need custom normalization.

Troubleshooting & edge cases

Despite the power, FTS has hidden traps. Here are common issues and fixes:

• Index not being used — If your query uses to_tsvector('english', name || ' ' || description) directly but your index is on a generated column, PostgreSQL might not match. Always rely on the indexed column (e.g., search_vector). Use EXPLAIN to verify.

• Wrong configuration — For Chinese, Japanese, or other languages, the default 'english' config won't work well. Use 'simple' or install an extension. Example: to_tsvector('simple', ...) treats every word as-is.

• Stop words removing essential terms — Words like "to" or "be" are removed, which can break searches for product codes (e.g., "be 123"). Use 'simple' if you need exact matching.

• Searching for punctuation or partial words — FTS doesn't match partial tokens. Searching "run" won't match "runner" unless you use stemming. For partial matching, you need pg_trgm.

• Generated column vs trigger — If you use a trigger to maintain a separate tsvector column, ensure it fires on UPDATE as well as INSERT, or you'll get stale data.

• Memory and update overhead — GIN indexes can be large and slow to update. For high-write tables, consider gin with fastupdate (default on) or periodic reindexing.

What you learned & what's next

You now understand why LIKE is a performance killer, how full-text search works by converting text into tsvector and querying with tsquery, and how a GIN index on that expression makes search super fast. You applied this by adding a generated column, creating a GIN index, and verifying with EXPLAIN. You also compared FTS to alternatives and learned to troubleshoot common pitfalls.

You should be able to:

• Explain the core idea behind full-text search indexes.
• Complete a practical exercise: implement FTS on a real table.

What's next? The next lesson in this track focuses on ranking and relevance tuning. You'll learn how to use ts_rank and ts_headline to display highlighted snippets and order results by relevancy — essential for a production-grade search experience.