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

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

ts_rank_cd

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:

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:

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:

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

---

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.

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:

---

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.

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.

---

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:

---

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?

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