SQL Full-Text Search: From LIKE to tsvector

Most applications start searching text the same way: WHERE title LIKE '%keyword%'. It works for a few thousand rows. Then the table grows, users expect relevance ranking, and that pattern-matching query becomes a full table scan that takes seconds instead of milliseconds. SQL full-text search solves this by treating text as structured, searchable tokens rather than raw character sequences.

This guide focuses on PostgreSQL, which ships the most complete built-in full-text search of any relational database. The concepts transfer to MySQL’s FULLTEXT indexes and SQL Server’s full-text catalogs, but the syntax differs.

The problem with LIKE

SELECT id, title
FROM articles
WHERE title ILIKE '%database%';

This query has three problems. First, the leading wildcard prevents any B-tree index from helping, so the engine scans every row. Second, it matches substrings blindly: “database” matches, but so does “undatabaseable” if such a word existed. Third, there is no concept of relevance. A title that mentions “database” five times ranks the same as one that mentions it once.

Pattern matching is fine for admin tools and small tables. For user-facing search, you need something designed for language.

Core concepts: tsvector and tsquery

PostgreSQL full-text search rests on two data types.

tsvector is a sorted list of lexemes (normalized words) with position information. You create one from text using to_tsvector:

SELECT to_tsvector('english', 'The quick brown fox jumps over lazy dogs');
-- 'brown':3 'dog':8 'fox':4 'jump':5 'lazi':7 'quick':2

Notice that “The” was dropped (it is a stop word), “jumps” became “jump” (stemming), and “lazy” became “lazi”. Each lexeme carries its position in the original text.

tsquery represents a search predicate. You create one with to_tsquery or the more forgiving plainto_tsquery:

SELECT plainto_tsquery('english', 'quick fox');
-- 'quick' & 'fox'

The match operator is @@:

SELECT *
FROM articles
WHERE to_tsvector('english', body) @@ plainto_tsquery('english', 'quick fox');

This returns rows where the body contains both “quick” and “fox” after stemming and stop-word removal.

Adding a stored tsvector column

Calling to_tsvector on every row for every query is expensive. The standard optimization is a dedicated column that stores the precomputed vector.

ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles
SET search_vector = to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''));

Keep it in sync with a trigger:

CREATE OR REPLACE FUNCTION articles_search_trigger()
RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.body, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_articles_search
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW
  EXECUTE FUNCTION articles_search_trigger();

The setweight function assigns a weight category (A through D) to lexemes. Words from the title get weight A, body gets weight B. This matters for ranking.

Indexing with GIN

A GIN (Generalized Inverted Index) index on the tsvector column makes searches fast:

CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

With this index, the @@ operator uses an index scan instead of a sequential scan. On a table with a million rows, this is the difference between 2ms and 2000ms.

GIN indexes are larger than B-tree indexes and slower to update, but they handle the many-to-many mapping between lexemes and rows that full-text search requires. For write-heavy tables, consider GIN with fastupdate = off or a GiST index, which is smaller but slightly slower to query.

Ranking results

Matching is binary: a row either matches or it does not. Ranking adds relevance scores.

SELECT
  id,
  title,
  ts_rank(search_vector, query) AS rank
FROM
  articles,
  plainto_tsquery('english', 'database performance') AS query
WHERE
  search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

ts_rank considers how often the query terms appear and their positions. ts_rank_cd uses cover density, which also rewards terms appearing close together.

Because we assigned weights earlier, title matches (weight A) contribute more to the rank than body matches (weight B). You can customize the weight multipliers:

-- weights array: {D, C, B, A}
ts_rank('{0.1, 0.2, 0.4, 1.0}', search_vector, query)

Advanced query syntax

plainto_tsquery treats input as an AND of all terms. For more control, use to_tsquery directly:

-- OR search
to_tsquery('english', 'postgres | mysql')

-- phrase search (words adjacent)
phraseto_tsquery('english', 'connection pooling')

-- negation
to_tsquery('english', 'caching & !redis')

-- prefix matching
to_tsquery('english', 'data:*')

Prefix matching with :* is useful for autocomplete. Combined with LIMIT and the GIN index, it responds in single-digit milliseconds.

Highlighting matched terms

ts_headline generates HTML snippets with matched terms wrapped in tags:

SELECT
  ts_headline('english', body, plainto_tsquery('english', 'index performance'),
    'StartSel=<mark>, StopSel=</mark>, MaxWords=35, MinWords=15'
  ) AS snippet
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'index performance');

Be aware that ts_headline re-processes the original text, not the tsvector, so it is slower. Avoid calling it on hundreds of rows. Apply it only to the top results after LIMIT.

Multi-language support

PostgreSQL ships dictionaries for many languages. Specify the configuration when creating vectors:

-- German text
to_tsvector('german', 'Die schnelle braune Fuchs')

-- Use a column to store the language per row
to_tsvector(articles.language::regconfig, articles.body)

For multilingual content, store one tsvector per language or use the simple configuration, which skips stemming and stop words entirely.

When to use an external search engine

PostgreSQL full-text search handles most applications well, but it has limits. Consider Elasticsearch, Typesense, or Meilisearch when you need:

• Fuzzy matching and typo tolerance. Postgres FTS does not handle misspellings. The pg_trgm extension helps with trigram similarity, but it is a different mechanism.
• Faceted search. Filtering by category, price range, and rating simultaneously while counting results per facet is not something FTS does natively.
• Distributed search across terabytes. If your text corpus exceeds what fits comfortably in one Postgres instance, a dedicated search cluster scales horizontally.
• Real-time indexing with sub-second visibility. GIN index updates can lag slightly under heavy write loads.

For most CRUD applications, blogs, e-commerce catalogs under a few million rows, and internal tools, Postgres FTS is more than sufficient and avoids the operational overhead of running a separate search service.

Practical checklist

1. Replace LIKE '%term%' with to_tsvector and plainto_tsquery.
2. Add a stored tsvector column and keep it updated with a trigger.
3. Create a GIN index on that column.
4. Use setweight to prioritize titles over body text.
5. Rank results with ts_rank or ts_rank_cd.
6. Use ts_headline sparingly, only on the final result set.
7. Benchmark with EXPLAIN ANALYZE to confirm the index is being used.

Full-text search in SQL is not a second-class feature. For the majority of applications, it eliminates the need for a separate search service while keeping your data in one place and your queries in one language.