Full-Text Search

View as Markdown

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

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.

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.

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.

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.

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:

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

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

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