Add Full-Text Search Without LIKE Scans

Build a generated tsvector column, index it with GIN, and query with websearch_to_tsquery and ranking instead of leading-wildcard LIKE.

~35 minpostgresqlsearchindexes

Task

Replace slow ILIKE searches with a maintained tsvector column and ranked full-text queries.

When to use this

Support searches articles by keyword with ILIKE '%term%', which cannot use an index and scans two million rows on every search request.

Prerequisites

PostgreSQL 13 or newer
A table with title and body text columns
The english text search configuration (installed by default)

Steps

1. Add a stored generated tsvector column that weights the title above the body.

ALTER TABLE articles
  ADD COLUMN search_document tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED;

Expected output: Existing rows are computed during the ALTER, and inserts or updates maintain the column automatically.

2. Index the tsvector column with GIN.

CREATE INDEX articles_search_idx ON articles USING gin (search_document);

Expected output: The index is created and supports @@ queries without reading every row.

3. Query with websearch_to_tsquery and rank results by relevance.

SELECT id, title, ts_rank(search_document, query) AS rank
FROM articles, websearch_to_tsquery('english', 'postgres index tuning') AS query
WHERE search_document @@ query
ORDER BY rank DESC
LIMIT 20;

Expected output: The top 20 matches come back ranked; quoted phrases and OR are accepted by websearch syntax.

4. Generate a highlighted snippet around the matching lexemes.

SELECT ts_headline('english', body, websearch_to_tsquery('english', 'index tuning'),
                   'StartSel=<mark>, StopSel=</mark>, MaxFragments=1') AS snippet
FROM articles WHERE id = 42;

Expected output: One fragment of body text with the matching terms wrapped in mark tags.

5. Confirm the plan uses the GIN index rather than a sequential scan.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM articles
WHERE search_document @@ websearch_to_tsquery('english', 'postgres');

Expected output: Bitmap Index Scan on articles_search_idx, with buffers proportional to matches rather than table size.

Troubleshooting

Problem

Search misses exact identifiers such as snake_case names

Solution

The english configuration stems words. Index a second column with the simple configuration for code terms and combine both predicates with OR.

Problem

Partial words return nothing

Solution

Full-text search matches lexemes, not prefixes. Use to_tsquery('postgres:*') for prefixes, or add a pg_trgm index for arbitrary substrings.

Problem

Adding the generated column locks the table for minutes

Solution

A stored generated column rewrites the table. Add it in a maintenance window, or add a plain column, backfill in batches, and maintain it with a trigger before switching.

Limitations

Stemming and stop words depend on the language configuration; mixed-language rows need per-row configs or separate columns.
Full-text search does not do fuzzy matching or substring search; pg_trgm is the complement for those cases.
The generated column increases write cost and rewrites the table when it is first added.

LIKE with a leading wildcard cannot use an index

col ILIKE '%term%' forces PostgreSQL to inspect every row, because a B-tree index is ordered by prefix. On a large table the search box becomes the most expensive query in the system, and it gets worse as content grows. Full-text search takes a different approach: it normalizes text into lexemes, stores them in a tsvector, and indexes that structure with GIN so matching is proportional to the number of results, not the table size.

Keep the vector in the table

A stored generated column is the simplest maintenance story: PostgreSQL computes it on insert and update, and backfills it when the column is added. Weighting the title above the body improves ranking without a second query. If the table is large, the initial ALTER TABLE rewrites it, so schedule it or migrate in steps with a trigger-maintained column.

ALTER TABLE articles
  ADD COLUMN search_document tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED;

Query with the right syntax

websearch_to_tsquery accepts human input safely: quoted phrases, or, and leading minus for exclusion, without throwing syntax errors on stray punctuation. plainto_tsquery is stricter and treats every word as AND. Use ts_rank or ts_rank_cd for ordering, and always apply a LIMIT; ranking is computed only for rows that matched, but a broad term can still match most of the table.

Highlight without sending the whole body

ts_headline extracts a fragment around the match. It is convenient but relatively expensive because it re-parses the document, so run it only for the page of results you return, and cap the fragment count. For a search results page, selecting the headline for twenty rows is fine; doing it for every match is not.

Know what it cannot do

Full-text search is not a substring engine. Searching post will not find postgres unless you use prefix queries, and misspellings return nothing unless you add trigram similarity. Many products combine both: a GIN tsvector index for ranked natural-language queries and a pg_trgm index for “search as you type” and typo tolerance.