Module P-7·23 min read

tsvector, tsquery, GIN indexes, relevance ranking, and generated tsvector columns — search without Elasticsearch.

P-7 — Full-Text Search

Q: A developer implements full-text search by creating a GIN index on a `tsvector` generated column. The application displays search results using `ts_headline` to show highlighted snippets. In testing, searching returns results instantly, but when querying the production database containing 5 million rows, the search page takes several seconds to load. What is the most likely architectural cause of this performance degradation?

The query runs `ts_headline` on the result set *before* applying a `LIMIT` clause, or the `ts_headline` function is re-parsing documents because it is computationally expensive and cannot use the GIN index to generate snippets. — `ts_headline` is a notoriously expensive function because it cannot rely entirely on the `tsvector` or the GIN index; it must retrieve the original unparsed text, re-parse it to locate the exact string positions of the search terms, and construct the HTML snippet. If a query matches 50,000 rows, and `ts_headline` is applied *before* the results are paginated (e.g., via `LIMIT 10`), PostgreSQL will compute snippets for all 50,000 rows before discarding 49,990 of them. The correct pattern is to filter and rank first, apply the `LIMIT`, and *then* compute `ts_headline` only on the final subset of rows. Option A is false. Option B is unlikely syntax (`ts_headline` returns text, not boolean). Option D is false (generated columns are computed on write, not read).

Q: You want to implement a robust user-facing search box for your application. Users often type phrases in quotes (`"error handling"`), exclude terms with a minus sign (`-java`), and occasionally make syntax errors like unbalanced quotes or trailing ampersands. Which PostgreSQL function is the safest and most effective choice for converting their raw input into a search query?

`websearch_to_tsquery('english', user_input)` — `websearch_to_tsquery` is specifically designed for public-facing search boxes. It understands common internet search syntax (quoted phrases for exact matches, minus signs for exclusion) and, crucially, it never throws syntax errors for malformed input (like an unclosed quote). Option A (`to_tsquery`) requires strict boolean syntax (e.g., `error & handling`) and will throw a fatal database error if the syntax is slightly wrong, breaking your application. Option B (`plainto_tsquery`) safely handles all input but ignores quotes and minus signs, simply AND-ing all words together. Option C (`phraseto_tsquery`) forces all words to be adjacent, ignoring advanced syntax.

The standard answer to "we need search" is Elasticsearch. Spin up a cluster, sync your data, write query DSL, manage index mappings. For large-scale dedicated search, that trade-off sometimes makes sense. For most application search — finding documents, filtering content, search-as-you-type boxes — you already have everything you need inside PostgreSQL.

This module covers PostgreSQL's full-text search system: how it represents and indexes text, how queries work, how to rank results by relevance, and how to set it up so searches stay fast at scale.

The Problem With `LIKE`

The first instinct for search is LIKE:

sql

SELECT * FROM articles WHERE body LIKE '%postgresql performance%';

This works. It also requires a full sequential scan of the table for every search — no index can help with a leading wildcard. At a thousand rows it is fast. At a million rows it is a problem.

ILIKE (case-insensitive) is slower still. And neither handles linguistic variations: a search for "running" will not find articles containing "runs" or "ran."

Full-text search solves both problems: it is indexable and it understands language.

The Two Core Types

PostgreSQL full-text search is built around two data types:

tsvector — a preprocessed representation of a document. It is a sorted list of lexemes (normalized word stems) with their positions in the original text. When you convert text to tsvector, PostgreSQL normalizes words (removing stop words like "the", stemming "running" → "run"), and records where each term appeared.

tsquery — a search query. It is a boolean expression of lexemes that gets matched against a tsvector.

sql

-- Convert text to tsvector
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- Result: 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2
-- Note: "The", "over" removed (stop words), "jumps"→"jump", "lazy"→"lazi"

-- Convert a search query to tsquery
SELECT to_tsquery('english', 'jump & fox');
-- Result: 'jump' & 'fox'

-- Match a tsvector against a tsquery with the @@ operator
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog')
    @@ to_tsquery('english', 'jump & fox');
-- Result: true

The @@ operator is the match operator. It returns true if the tsvector satisfies the tsquery.

Building Queries with `tsquery`

`to_tsquery`

The most explicit form. Uses boolean operators:

sql

-- AND: both terms must be present
SELECT to_tsquery('english', 'postgres & performance');

-- OR: either term
SELECT to_tsquery('english', 'postgres | mysql');

-- NOT: exclude a term
SELECT to_tsquery('english', 'postgres & !mysql');

-- Phrase search (terms must be adjacent, in order)
SELECT to_tsquery('english', 'postgres <-> performance');

-- Proximity (within N words)
SELECT to_tsquery('english', 'postgres <2> performance');

to_tsquery requires valid tsquery syntax. If the user types bare text, it will error.

`plainto_tsquery`

Takes plain text and converts it to an AND query. Safe for direct user input.

sql

SELECT plainto_tsquery('english', 'postgresql performance tuning');
-- Result: 'postgresql' & 'perform' & 'tune'

`websearch_to_tsquery`

Understands Google-style search syntax. The best choice for search boxes.

sql

SELECT websearch_to_tsquery('english', 'postgresql "full text" -mysql');
-- postgres & 'full' <-> 'text' & !'mysql'

-- Quoted phrases, minus sign for exclusion — familiar to users

websearch_to_tsquery is the right function for production search boxes. It handles messy user input gracefully and supports the syntax users expect.

Searching a Table

Given a table of articles:

sql

CREATE TABLE articles (
  id         BIGSERIAL PRIMARY KEY,
  title      TEXT NOT NULL,
  body       TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

A basic full-text search:

sql

SELECT id, title
FROM articles
WHERE to_tsvector('english', title || ' ' || body)
   @@ websearch_to_tsquery('english', 'postgresql performance');

This works but is slow — it calls to_tsvector on every row for every query. No index is used.

GIN Indexes: Making Search Fast

The solution is to pre-compute the tsvector and index it. The index type for tsvector is GIN (Generalized Inverted Index). A GIN index maps each lexeme to the set of rows that contain it — exactly like the index at the back of a book.

Option 1: Index on an Expression

sql

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

The query must use the exact same expression for the index to be used:

sql

-- This uses the index:
SELECT id, title
FROM articles
WHERE to_tsvector('english', title || ' ' || body)
   @@ websearch_to_tsquery('english', 'postgresql performance');

Expression indexes work but have a maintenance overhead: the expression is recomputed on every INSERT and UPDATE.

Option 2: Generated Stored Column (Recommended)

A better pattern in PostgreSQL 12+: store the tsvector as a generated column and index that.

sql

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

CREATE INDEX idx_articles_search_vector
ON articles
USING GIN (search_vector);

Now queries are clean and simple:

sql

SELECT id, title
FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', 'postgresql performance');

The search_vector column is maintained by PostgreSQL automatically on INSERT and UPDATE. The GIN index is built on the stored column. Query performance is fast.

Weighting Multiple Columns

Different columns have different importance — a match in the title should rank higher than a match in the body. setweight assigns a weight label (A, B, C, D — in descending importance) to a tsvector.

sql

-- Title gets weight A (highest), body gets weight B
ALTER TABLE articles
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'B')
  ) STORED;

Weights are used by the ranking functions to score results. A query matching in the title scores higher than the same query matching only in the body.

Ranking Results

Matching tells you which rows contain the search terms. Ranking tells you which rows are most relevant. PostgreSQL has two ranking functions:

`ts_rank`

sql

SELECT
  id,
  title,
  ts_rank(search_vector, query) AS rank
FROM articles,
     websearch_to_tsquery('english', 'postgresql performance') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

ts_rank computes relevance based on how frequently the query terms appear in the document. Higher frequency = higher rank.

`ts_rank_cd`

sql

SELECT
  id,
  title,
  ts_rank_cd(search_vector, query) AS rank
FROM articles,
     websearch_to_tsquery('english', 'postgresql performance') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

ts_rank_cd ("cover density") also considers how close together the matching terms appear in the document. Generally produces more intuitive rankings when terms appear in proximity to each other.

Combining Rank with Recency

Pure relevance ranking sometimes buries recent results. A common production pattern:

sql

SELECT
  id,
  title,
  created_at,
  ts_rank_cd(search_vector, query) * (
    1.0 / (1 + extract(epoch from now() - created_at) / 86400.0)
  ) AS combined_score
FROM articles,
     websearch_to_tsquery('english', 'postgresql performance') AS query
WHERE search_vector @@ query
ORDER BY combined_score DESC
LIMIT 20;

This boosts newer results, decaying exponentially with age. Adjust the decay constant to taste.

Highlighted Snippets with `ts_headline`

Search results usually show a snippet of the original text with matching terms highlighted. ts_headline does this:

sql

SELECT
  id,
  title,
  ts_headline(
    'english',
    body,
    websearch_to_tsquery('english', 'postgresql performance'),
    'MaxWords=30, MinWords=15, StartSel=<mark>, StopSel=</mark>'
  ) AS snippet
FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', 'postgresql performance')
ORDER BY ts_rank_cd(search_vector, websearch_to_tsquery('english', 'postgresql performance')) DESC
LIMIT 10;

ts_headline options:

MaxWords / MinWords — snippet length
StartSel / StopSel — HTML tags wrapping matched terms
MaxFragments — return multiple non-contiguous snippets
FragmentDelimiter — separator between fragments (default " ... ")

Important: do not run ts_headline on the tsvector column — run it on the original text column. ts_headline is slow (it re-processes the document to find context). Compute it only on the result rows, never in a WHERE clause.

Multi-Column Search

For tables with many text fields, aggregate them all into one search_vector:

sql

CREATE TABLE products (
  id          BIGSERIAL PRIMARY KEY,
  name        TEXT NOT NULL,
  description TEXT,
  tags        TEXT,   -- comma-separated or space-separated tags
  category    TEXT
);

ALTER TABLE products
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(name,        '')), 'A') ||
    setweight(to_tsvector('english', coalesce(tags,        '')), 'A') ||
    setweight(to_tsvector('english', coalesce(category,    '')), 'B') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'C')
  ) STORED;

CREATE INDEX idx_products_search ON products USING GIN (search_vector);

Querying is the same regardless of how many columns feed the vector:

sql

SELECT id, name, category
FROM products
WHERE search_vector @@ websearch_to_tsquery('english', 'ergonomic standing desk')
ORDER BY ts_rank_cd(search_vector, websearch_to_tsquery('english', 'ergonomic standing desk')) DESC
LIMIT 20;

Language Configuration

Every to_tsvector and to_tsquery call takes a language configuration name as the first argument. This controls which stop words are removed and which stemming rules apply.

sql

-- English stemming
SELECT to_tsvector('english', 'running runner runs');
-- 'run':1,2,3  (all three map to the same lexeme)

-- Spanish stemming
SELECT to_tsvector('spanish', 'corriendo corredor corre');

-- Simple: no stemming, no stop words, just lowercase
SELECT to_tsvector('simple', 'The quick brown Fox');
-- 'brown':3 'fox':4 'quick':2 'the':1

Use 'simple' when you need exact-word matching (product codes, usernames) or when working with languages not supported by built-in dictionaries.

To see available configurations:

sql

SELECT cfgname FROM pg_ts_config;

Combining Full-Text Search with Filters

Full-text search works naturally alongside SQL filters. The GIN index is used for the text filter; additional B-tree indexes can be used for other conditions.

sql

-- Full-text search scoped to a specific category and date range
SELECT id, title, published_at
FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', 'index performance')
  AND category = 'database'
  AND published_at > now() - interval '1 year'
ORDER BY ts_rank_cd(search_vector, websearch_to_tsquery('english', 'index performance')) DESC
LIMIT 20;

PostgreSQL's planner will use the GIN index for the text condition and may use a B-tree index on category or published_at depending on estimated selectivity.

Performance Considerations

GIN index build time: GIN indexes are expensive to build on large tables. For tables with millions of rows, build the index during a maintenance window and be aware that inserts and updates are slightly slower while the index is maintained.

ts_headline cost: It re-parses the document to find the best snippet context. Only call it on the final result set (after LIMIT), never in a subquery used for filtering.

ts_rank is computed per row: Sorting a million rows by rank is expensive. Always filter with WHERE search_vector @@ query first, then rank the surviving rows. The GIN index reduces the candidate set to a small fraction of the table before ranking.

websearch_to_tsquery is cheap: Parse the query once (ideally in application code) and pass it as a parameter. Don't reconstruct it in every subquery.

sql

-- Efficient: query computed once, used twice
WITH q AS (
  SELECT websearch_to_tsquery('english', $1) AS query
)
SELECT
  a.id,
  a.title,
  ts_rank_cd(a.search_vector, q.query) AS rank,
  ts_headline('english', a.body, q.query) AS snippet
FROM articles a, q
WHERE a.search_vector @@ q.query
ORDER BY rank DESC
LIMIT 10;

When PostgreSQL FTS Is Not Enough

PostgreSQL full-text search handles the majority of application search requirements. The cases where Elasticsearch or a dedicated search engine makes more sense:

Autocomplete / prefix search: PostgreSQL FTS handles whole words. For character-by-character autocomplete, consider pg_trgm (trigram extension) or a dedicated autocomplete system.
Fuzzy matching / typo tolerance: pg_trgm provides edit-distance-based similarity search.
Real-time, sub-millisecond search across billions of rows: At extreme scale, dedicated search clusters with sharding and caching start to win.
Complex relevance tuning: Elasticsearch's BM25 and field boosting configuration is more flexible than PostgreSQL's ranking functions for search-intensive products.

For everything else — blog search, document search, product search, internal tooling — PostgreSQL is the right tool.

Practical Exercise

Build a searchable article store from scratch:

sql

-- 1. Create the table
CREATE TABLE blog_posts (
  id         BIGSERIAL PRIMARY KEY,
  title      TEXT NOT NULL,
  body       TEXT NOT NULL,
  author     TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- 2. Add the generated tsvector column
ALTER TABLE blog_posts
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title,  '')), 'A') ||
    setweight(to_tsvector('english', coalesce(author, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(body,   '')), 'C')
  ) STORED;

-- 3. Create the GIN index
CREATE INDEX idx_blog_posts_fts ON blog_posts USING GIN (search_vector);

-- 4. Insert some data
INSERT INTO blog_posts (title, body, author) VALUES
  ('Getting Started with PostgreSQL', 'PostgreSQL is a powerful open source relational database...', 'Alice Chen'),
  ('Index Internals and Performance', 'Understanding B-tree structure helps write faster queries...', 'Bob Kim'),
  ('Row-Level Security in Production', 'RLS policies enforce data isolation at the database layer...', 'Alice Chen'),
  ('Full-Text Search Without Elasticsearch', 'PostgreSQL tsvector and GIN indexes provide production-grade search...', 'Carol Wang');

-- 5. Search with ranking
SELECT
  id,
  title,
  author,
  ts_rank_cd(search_vector, q) AS rank
FROM blog_posts,
     websearch_to_tsquery('english', 'postgresql index performance') AS q
WHERE search_vector @@ q
ORDER BY rank DESC;

-- 6. Add highlighted snippets
SELECT
  title,
  ts_headline('english', body, q,
    'MaxWords=20, MinWords=10, StartSel=**, StopSel=**'
  ) AS snippet
FROM blog_posts,
     websearch_to_tsquery('english', 'postgresql index') AS q
WHERE search_vector @@ q;

-- 7. Verify index is used
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM blog_posts
WHERE search_vector @@ websearch_to_tsquery('english', 'postgresql');
-- Look for: Bitmap Index Scan on idx_blog_posts_fts

Summary

PostgreSQL's full-text search system provides production-grade text search without external dependencies:

tsvector stores a normalized, stemmed representation of document text. tsquery expresses search queries with boolean operators. The @@ operator matches them. A GIN index on the tsvector column makes search fast at scale. Generated stored columns keep the vector automatically updated with no application code. ts_rank_cd provides relevance ranking. ts_headline generates highlighted snippets.

For the majority of application search use cases — content search, product search, document discovery — this is all you need. Elasticsearch solves a different problem at a different scale.

Next up: P-8 — Performance Tuning for Application Engineers — EXPLAIN ANALYZE in depth, connection pooling with PgBouncer, key configuration parameters, and eliminating the N+1 query problem.

Knowledge Check

A developer implements full-text search by creating a GIN index on a tsvector generated column. The application displays search results using ts_headline to show highlighted snippets. In testing, searching returns results instantly, but when querying the production database containing 5 million rows, the search page takes several seconds to load. What is the most likely architectural cause of this performance degradation?

You want to implement a robust user-facing search box for your application. Users often type phrases in quotes ("error handling"), exclude terms with a minus sign (-java), and occasionally make syntax errors like unbalanced quotes or trailing ampersands. Which PostgreSQL function is the safest and most effective choice for converting their raw input into a search query?

Test your knowledge with more question sets

Question 3: A team implements a full-text search feature using an expression index: CREATE INDEX idx_search ON documents USING GIN (to_tsvector('english', title || ' ' || body));. The application executes the following search query, but EXPLAIN ANALYZE shows a slow Sequential Scan instead of using the GIN index. Why?

sql

SELECT title FROM documents 
WHERE to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, '')) @@ websearch_to_tsquery('english', 'performance');

A) The @@ operator does not support GIN indexes; it requires a GiST index.
B) The websearch_to_tsquery function generates dynamic queries that cannot be evaluated against pre-computed indexes.
C) The expression in the WHERE clause (to_tsvector(...)) does not exactly match the expression used to create the index.
D) EXPLAIN ANALYZE always executes a sequential scan on local development databases because the tables are too small for the query planner to justify reading the index.

Reveal Answer

Correct Answer: C

Expression indexes in PostgreSQL are highly literal. For the query planner to use an expression index, the expression in the WHERE clause must match the indexed expression exactly character-for-character (conceptually). In this scenario, the index was created using title || ' ' || body, but the query uses coalesce(title, '') || ' ' || coalesce(body, ''). Because these expressions are logically different to the planner, it cannot guarantee the index contains the right data, so it falls back to a full sequential scan, recomputing the coalesce expression for every row. (This is a major reason why storing the tsvector in a generated column is heavily preferred over expression indexes—it eliminates this exact failure mode).

PreviousModule P-6: Authentication, Row-Level Security, and Access Control Next Module P-8: Performance Tuning for Application Engineers

Discussion

Join the discussion

Loading comments...