P-7 — Full-Text Search

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

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

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:

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:

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:

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:

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.

-- 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:

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.

-- 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.

-- 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:

-- 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.

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?

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).