Full Text Search

November 16 · First extension to the inherited database

MaDS Databases & SQL

One question will organize today

How can a receiver turn messy text into a useful, explainable client search?

Course source and adaptation

Today’s sequence follows Alex Reinhart’s Full Text Search, updated for current PostgreSQL functions and applied to the inherited Climate or EV system.

Current reference: PostgreSQL text-search controls.

By the end of class

You should be able to:

• distinguish substring, regular-expression, and linguistic search,
• normalize text without destroying provenance,
• build tsvector and tsquery values,
• rank results and justify a GIN index,
• document what the search misses.

Text is data with hidden structure

Possible inherited fields:

• Storm Events narratives and episode narratives,
• station or facility names,
• route or corridor descriptions,
• source notes and validation messages.

First ask what a “match” should mean to the client.

Preserve raw; derive searchable

raw source text → decoded text → normalized/searchable representation

Keep the raw field and its source. Search preparation should be reproducible.

Start with exact and substring logic

WHERE event_type = 'Flood'

WHERE episode_narrative ILIKE '%evacuat%'

ILIKE is useful for exploration, but it does not understand words, relevance, or language.

Regular expressions find patterns

WHERE episode_narrative ~* '\m(evacuat|shelter)\w*'

Regex is good for form. It is not automatically a relevance model.

Checkpoint 1 · Define a match

Choose one inherited text field and client intent. Write an exact, ILIKE, or regex query, then list one false positive and one false negative it could create.

PostgreSQL parses text into lexemes

SELECT to_tsvector(
  'english',
  'Roads were flooding; flooded roads remained closed.'
);

The result normalizes related word forms and records positions.

A query is also structured

SELECT websearch_to_tsquery(
  'english',
  'flooded road -coastal'
);

websearch_to_tsquery accepts forgiving web-style input and does not raise syntax errors for raw user text.

Match vector to query

WHERE to_tsvector(
        'english',
        coalesce(episode_narrative, '')
      ) @@ websearch_to_tsquery('english', %s)

Use coalesce; text concatenation with NULL can erase the whole searchable document.

Weight fields by meaning

setweight(to_tsvector('english', coalesce(event_type, '')), 'A') ||
setweight(to_tsvector('english', coalesce(episode_narrative, '')), 'B')

The field weights encode a product decision. Document it.

Ranking makes retrieval useful

ts_rank_cd(search_vector, query) AS rank

Rank orders matches; it does not certify relevance or truth.

Checkpoint 2 · Build and rank

Return the top 10 inherited records for a client search using websearch_to_tsquery and ts_rank_cd. Include stable tie-breaking.

Compare the top results to your ILIKE version.

Store the derived vector when search repeats

ALTER TABLE storm_events
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
  to_tsvector('english', coalesce(episode_narrative, ''))
) STORED;

The generated expression keeps it synchronized.

GIN supports containment search

CREATE INDEX storm_events_search_gin
ON storm_events
USING GIN (search_vector);

Indexes speed a pattern; they do not improve relevance.

Inspect before and after

EXPLAIN (ANALYZE, BUFFERS)
SELECT event_id
FROM storm_events
WHERE search_vector @@
  websearch_to_tsquery('english', 'flash flood');

Use a representative table and search—not a tiny demo guaranteed to scan.

Search has evidence boundaries

A narrative search can miss:

• events with empty narratives,
• synonyms outside the chosen dictionary,
• codes or place names tokenized unexpectedly,
• concepts implied but not stated,
• source records absent from the database.

Checkpoint 3 · Audit ten results

Label ten returned records relevant/not relevant and inspect five expected records that did not match. Recommend one query, field-weight, or normalization change.

Do not tune only to one convenient example.

Project transfer

Add search only if it helps the inherited client. Otherwise, document why structured filtering is the more honest interface.

The receiver owns the decision and its acceptance test.

Homework starts here

Homework 3 may use full-text search as its tested extension. Include the client intent, query behavior, ten-result audit, plan evidence, and known misses.

The pattern to keep

define match meaning
  → preserve raw text
  → choose exact / pattern / linguistic search
  → rank deterministically
  → index repeated retrieval
  → audit matches and misses
  → state the evidence boundary

Next: audit what Azure manages and what the project team still owns.