---
title: "Full Text Search"
subtitle: "November 16 · First extension to the inherited database"
author: "MaDS Databases & SQL"
format:
  revealjs:
    theme: [default, mads-sql-reveal.scss]
    slide-number: c/t
    chalkboard: true
    code-line-numbers: true
    transition: fade
    footer: "Adapted from Alex Reinhart · MADS Computing"
---

## One question will organize today

::: {.question}
How can a receiver turn messy text into a useful, explainable client search?
:::

## Course source and adaptation

::: {.source-note}
Today's sequence follows Alex Reinhart's [Full Text Search](https://www.refsmmat.com/courses/msp-computing/data-engineering/text-search.html), updated for current PostgreSQL functions and applied to the inherited Climate or EV system.

Current reference: [PostgreSQL text-search controls](https://www.postgresql.org/docs/current/textsearch-controls.html).
:::

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

```text
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

```sql
WHERE event_type = 'Flood'
```

```sql
WHERE episode_narrative ILIKE '%evacuat%'
```

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

## Regular expressions find patterns

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

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

## Checkpoint 1 · Define a match

::: {.checkpoint}
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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
ts_rank_cd(search_vector, query) AS rank
```

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

## Checkpoint 2 · Build and rank

::: {.checkpoint}
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

```sql
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

```sql
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

```sql
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

::: {.checkpoint}
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

::: {.project-prompt}
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

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