# Full-Text Search

Full-text search in Nucleus is an index on a table column, not a separate store. Rows
are matched with the `@@` operator and ranked with `BM25()`, so search results are
ordinary rows: joinable, filterable, and covered by the same transactions and
row-level security policies as everything else.

Because keyword search and vector search both return rows from the same table, hybrid
search is a plain SQL query — no fusion built-in, no second system to keep in sync.

## Creating an Index

```sql
CREATE TABLE articles (
  id        INT PRIMARY KEY,
  title     TEXT,
  body      TEXT,
  category  TEXT,
  embedding VECTOR(384)
);

CREATE INDEX articles_body_fts ON articles USING FTS (body);
```

`USING BM25` is accepted as a synonym.

The index is maintained by `INSERT`, `UPDATE`, and `DELETE` like any other index, and
is rebuilt from committed rows if a transaction aborts. There is nothing to
re-synchronise by hand.

**Requirement:** the table needs an integer `PRIMARY KEY`. Documents are keyed on it so
that maintenance survives deletes, which shift physical row positions. Tables without
one can still use `@@` — they just don't get an index.

## Matching

`column @@ 'query'` is true when the column contains **every** term in the query, after
stemming and stopword removal.

```sql
SELECT id, title
FROM articles
WHERE body @@ 'machine learning'
  AND category = 'tech'
ORDER BY published_at DESC
LIMIT 10;
```

`@@` is defined on the row's own text, so it returns the same rows whether or not an
index exists. The index makes it faster; it never changes the answer.

## Ranking

`BM25(column, 'query')` scores a row against the corpus statistics of that column's
index.

```sql
SELECT id, title, BM25(body, 'machine learning') AS score
FROM articles
WHERE body @@ 'machine learning'
ORDER BY score DESC
LIMIT 10;
```

Okapi BM25 with the standard parameters — `k1 = 1.2` (term frequency saturation) and
`b = 0.75` (document length normalisation). Shorter documents containing more
occurrences of rarer terms score higher.

`BM25()` requires an FTS index on the column, because inverse document frequency and
average document length are properties of the corpus, not of one row. Without an index
it reports that, rather than scoring against an empty corpus.

## Hybrid Search

Reciprocal Rank Fusion combines a keyword ranking and a vector ranking by their
positions rather than their scores, which sidesteps the problem that BM25 scores and
cosine distances are not on a comparable scale.

```sql
WITH kw AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY BM25(body, 'machine learning') DESC) AS r
  FROM articles
  WHERE body @@ 'machine learning'
  LIMIT 50
),
sem AS (
  SELECT id, ROW_NUMBER() OVER (
    ORDER BY VECTOR_DISTANCE(embedding, VECTOR('[0.1, 0.2, ...]'), 'cosine')
  ) AS r
  FROM articles
  LIMIT 50
)
SELECT COALESCE(kw.id, sem.id) AS id,
       COALESCE(1.0 / (60 + kw.r), 0) + COALESCE(1.0 / (60 + sem.r), 0) AS score
FROM kw FULL OUTER JOIN sem ON kw.id = sem.id
ORDER BY score DESC
LIMIT 10;
```

Both halves read the same table in the same snapshot, through the same policies.

The constant `60` is the conventional RRF damping factor; raising it flattens the
contribution of top ranks. Because fusion is expressed in the query rather than hidden
in a built-in, you can weight the two halves differently, add a third ranking, or
replace the fusion entirely without waiting for a new function.

## PostgreSQL Compatibility

The PostgreSQL spelling works and returns the same rows:

```sql
SELECT * FROM articles
WHERE TO_TSVECTOR(body) @@ PLAINTO_TSQUERY('machine learning');
```

```sql
-- Boolean match test
SELECT * FROM articles WHERE TS_MATCH(body, 'machine learning');

-- Highlight matching terms with <em> tags
SELECT TS_HEADLINE(body, 'rust') FROM articles;
-- → "<em>Rust</em> is a systems programming language"

-- Convert text to a stemmed query
SELECT PLAINTO_TSQUERY('machine learning');
-- → "machine & learn"
```

Two differences worth knowing:

- **`TS_RANK` is not BM25.** Like PostgreSQL's `ts_rank`, it scores a single
  `(document, query)` pair with no corpus, so it has no inverse document frequency and
  no length normalisation. It is fine for a rough within-document signal, and it will
  order results differently from `BM25()`. Use `BM25()` for relevance ranking.
- **`TO_TSVECTOR` returns text**, a normalised term string, not a distinct `tsvector`
  type with lexeme positions. It composes correctly with `@@` and `PLAINTO_TSQUERY`;
  it does not render like PostgreSQL's `'learn':2 'machin':1`.

## Behaviour to Expect

- **Corpus statistics are not snapshot-isolated.** `N`, average document length, and
  document frequencies come from the current index rather than from your transaction's
  snapshot, so a score can shift as other sessions write. This matches how Lucene,
  Elasticsearch, and PostgreSQL statistics behave; the *set of rows* you get back is
  snapshot-exact, only the ranking weights are shared.
- **Under row-level security**, `@@` and `BM25()` remain available and filter through
  policy like any other predicate. Index acceleration is skipped, so search falls back
  to a scan; results are unchanged. Corpus statistics are aggregate and are not
  partitioned by policy — a document frequency counts rows the querying role cannot
  read, the same way PostgreSQL's planner statistics do.
- **Inside an open transaction**, index acceleration is likewise skipped so that
  uncommitted rows cannot leak into another session's candidate set. `@@` still
  evaluates correctly against your own uncommitted rows.

## Stemming

Six built-in language stemmers normalise words to their root form:

| Language | Examples |
|----------|----------|
| English (default) | running → run, learning → learn |
| German | Übungen → Übung |
| French | étudiantes → étudiant |
| Spanish | corriendo → corr |
| Italian | velocemente → veloce |
| Portuguese | correndo → corr |

## Tokenization Pipeline

1. Split on non-alphanumeric characters
2. Lowercase
3. Filter stopwords (48 common English words)
4. Apply language-specific stemming

Both sides of a comparison run through the same pipeline, so a query term matches a
document term whenever their stems agree.

## Document Store Surface (Legacy)

Nucleus also exposes the inverted index directly, keyed by a document id you supply:

```sql
SELECT FTS_INDEX(1, 'Rust is a systems programming language');
SELECT FTS_SEARCH('systems programming', 10);
-- → [{"doc_id":1,"score":2.45}]
SELECT FTS_FUZZY_SEARCH('systms programing', 2, 10);
SELECT FTS_REMOVE(1);
```

This store is independent of your tables. Nothing ties `doc_id` 1 to any row, so
keeping it consistent with a table is the application's job — a missed `FTS_REMOVE`
leaves a deleted row searchable. It is also unavailable while row-level security is
active, because it has no policy-aware access path.

Prefer `CREATE INDEX ... USING FTS` for anything that indexes table data. The document
store remains for corpora that genuinely have no table behind them, and for fuzzy and
faceted search, which the table-attached index does not yet expose.

## Use Cases

- **Site search** — full-text search across pages and posts
- **Product search** — find products by description, ranked by relevance
- **Log analysis** — search through structured log messages
- **Knowledge base** — search documentation and articles
- **Retrieval for AI** — hybrid keyword + vector retrieval in one query, one snapshot