PostgreSQL Full-text Search

1) The problem before syntax

Text search becomes risky when “a few attractive results” is treated as equivalent to a defensible search system. This lesson uses a small documentation catalog with title and body fields. The deliverable is not merely a successful query; it is PostgreSQL Full-text Search Evidence Record v1, which keeps configuration, searchable document representation, query parsing, matching, ranking, index/plan evidence, relevance judgment, and one residual limit separate. The goal is a bounded, falsifiable pipeline—not a claim that full-text search is semantic search or that it solves every retrieval problem.

2) Route and lesson boundary

DAT-006 is the hard prerequisite for the evidence/contract reasoning reused here. This lesson owns PostgreSQL lexical retrieval only; DAT-009 owns the next step into pgvector and hybrid retrieval. Embeddings, ANN tuning, fusion, and semantic retrieval are therefore deferred. The question here is narrower: which configuration, parser, and weighting choices are justified for one domain, what did PostgreSQL actually observe, and which part remains a product relevance judgment?

3) Mental model: keep the chain decomposed

Use this chain:

title/body → explicit text-search configuration → tsvector → tsquery parser → @@ match → rank → labelled relevance check → index/EXPLAIN observation → bounded decision

Each arrow is a separate contract. to_tsvector transforms text into normalized lexemes under the selected configuration. User input must become a tsquery. The @@ operator answers the database matching condition; it does not establish business relevance. ts_rank and ts_rank_cd order text-search evidence, but a larger score is not ground-truth relevance. The final decision therefore combines database observations with a representative labelled fixture and states what remains uncertain.

4) Make configuration and normalization explicit

A text-search configuration determines how a parser and dictionaries transform tokens into lexemes. Relying silently on the database default creates a hidden dependency: a different environment or product language can change the effective behavior. The lab requires the configuration to be recorded explicitly and at least one normalization result to be inspected. Comparing english and simple can expose stemming and stop-word behavior. That comparison is evidence about behavior, not a universal claim that one configuration is always superior.

5) Document vectors, weights, and query parsers are contract choices

A tsvector is the normalized searchable representation and can combine multiple fields. Using setweight to distinguish title from body changes ranking behavior; a ranking improvement still needs evaluation against labelled queries. On the query side, plainto_tsquery converts unformatted text into surviving lexemes joined by AND, while websearch_to_tsquery accepts a web-search-like input style including quoted phrases, OR, and negation. Parser choice should follow the actual user-input contract rather than the convenience of a demo. The repaired fixture includes document 7 as a hard negative for Q2: it contains both “pool” and “connection” without the quoted-phrase adjacency, so parser behavior is falsifiable rather than merely described.

6) Worked example: an attractive list is not enough

Take the query “connection timeout.” First read the query label and expected-relevant document IDs from the fixture. Record the chosen configuration and parser, inspect a representative tsvector and tsquery, collect @@ matches, and compute ranks. Then compare the result list with the labelled relevance fixture. If a document receives a high rank but is not labelled relevant, preserve that contradiction: it demonstrates that the ranking function orders evidence available inside the text-search model while relevance remains an application evaluation question. The decision row must say what is accepted, rejected, and still unknown.

7) Required failure modes

Configuration mismatch: run the same representative query with an intentionally mismatched configuration and compare lexemes and matches. A changed result is evidence that configuration is part of the retrieval contract.

Stop-word-heavy query: select an input that exposes dictionary behavior. Record the observation without generalizing one case to all languages.

Ranking is not truth: find a query with plausible matches but weak labelled relevance. Do not turn a high rank into a correctness claim.

Index is not guaranteed plan choice: define a GIN index and record EXPLAIN. On a small table the planner may still prefer a sequential scan. Index existence and observed planner choice are different pieces of evidence. For an expression index, the indexed text-search expression must also stay semantically aligned with the explicit configuration used by the query; otherwise “an index exists” is not evidence that this query can use that index.

8) Experienced path: compress syntax, not evidence

If to_tsvector, tsquery, and GIN are already familiar, skip introductory syntax quickly. Four requirements remain: a prediction before execution, a negative probe, a relevance fixture, and a residual-limit statement. The experienced route is not permission to replace evidence with intuition. One successful query or one fast plan cannot close the retrieval contract by itself.

9) Alternatives and trade-offs stay bounded

ILIKE or substring search is not categorically wrong. A small domain, exact substring requirement, or simple prefix behavior may justify a simpler mechanism. Full-text search becomes useful when normalization, query semantics, weighting, and text-search indexing are valuable to the product problem. It is also not a substitute for semantic vector search; those mechanisms have different evidence and failure boundaries. This lesson defends only the decision inside its own scope and defers semantic retrieval to DAT-009.

10) Guided and independent practice

For the guided pass, first write an objective such as: “For English support queries, search title and body under an explicit configuration and evaluate three labelled queries for match and rank behavior.” Before running a failure probe, predict the observable. The starter provides schema, corpus, query labels, and evidence structure, but it does not solve the final configuration, field weights, query parser, index decision, or acceptance row.

For independent transfer, a multilingual help center adds a second content field and a new query style. Decide whether the existing configuration, weighting, parser, and index evidence still apply. Update the Evidence Record without introducing vector retrieval, and identify at least one earlier assumption that no longer holds.

11) Verification and the Evidence Record

For every query, capture at least: Query, Config, Document Vector Evidence, Query Parser, Match, Rank, Expected Relevant, Index-Plan Observation, Decision, and Residual Limit. Keep database observation separate from inference. If a real PostgreSQL runtime was not executed, do not fabricate matches, ranks, or plans; runtime status remains NOT_RUN. Strong evidence includes a positive baseline, negative/failure evidence, a repair or bounded decision, changed-context transfer, and an explicit residual limitation.

12) Assessment and transfer

A passing result can explain why configuration must be explicit, preserve the distinction between tsvector and tsquery, choose a parser from the input contract, separate @@ matching from relevance judgment, treat rank as bounded evidence rather than truth, and describe GIN as the preferred text-search index type without guaranteeing planner use. The transfer case must reuse the reasoning method independently rather than copy a conclusion from the starter.

13) Sources and freshness

The source boundary is deliberately limited to three official PostgreSQL 18 sources: PG18-FTS, PG18-FTS-CONFIG, and PG18-GIN. They cover full-text search behavior, configuration, and GIN context. The frozen retrieval date is 2026-09-02 and the review due date is 2026-10-17. On 2026-09-15, the same official locators were rechecked and no material drift was found for the claims used by this lesson. If versioned parser/ranking behavior or index guidance changes, affected claims must be reviewed again; source scope does not expand silently.