SQL

Full-text search

MATCH and BM25 over any column, with automatic full-text indexing.

Search is part of an ordinary query. There is no full-text CREATE INDEX form and no shadow table: name the columns to search and the engine handles the rest.

SELECT product_id, name
FROM products
WHERE MATCH(name) AGAINST 'espresso grinder'

Several columns at once, or every column in the row:

SELECT name FROM products WHERE MATCH(name, brand) AGAINST 'copper kettle'
SELECT name FROM products WHERE MATCH(*) AGAINST 'yirgacheffe'

MATCH(*) searches every declared column, including numbers and dates through their canonical text form, so MATCH(*) AGAINST '2025' finds rows by a date column as well as by text. Internal row locators are never searchable or returned by SELECT *.

Planned 1.0 document rendering

The 1.0 engine uses document-renderer version 2. It searches the stored SQL value rather than the spelling used to insert it. These forms follow PostgreSQL's default text output:

TypeSearch text in 1.0
TIMEHH:MM:SS, with fractional seconds when present and trailing zeros dropped. 10:00:00.500 and 10:00:00.5 both search as 10:00:00.5.
SQL arraysPostgreSQL array text, with braces, element quoting, escaping, NULL elements, and non-default lower bounds. Original JSON-array escape spellings are not searched.
JSONThe stored JSON text, as PostgreSQL returns json::text; its whitespace, number spelling and string escapes remain significant to tokenization.
JSONBPostgreSQL's stored JSONB text: canonical object order and string escaping, with the stored numeric display scale.

For example, a 1.0 TIME value above contains the token 5, not 500. An actual newline inside an array element splits terms; the original JSON escape \n no longer adds an n to a term. JSON and JSONB follow their distinct SQL text rules instead of the 0.x tagged JSON renderer. Ordinary text, numbers, DATE, timestamp and INTERVAL documents keep their existing rendering rules. Boolean columns remain excluded; an all-NULL document still returns SQL NULL.

The array document limit counts characters in PostgreSQL's rendered text, not its binary storage bytes. Array quoting recognizes only PostgreSQL's whitespace set (space, tab, LF, CR, vertical tab and form feed), plus its special array characters. Other Unicode whitespace such as a non-breaking space does not add quotes. A leading U+FEFF remains value content.

These are deliberate 1.0 changes. The shipped 0.x engine keeps renderer version 1 and its existing results. The 1.0 storage cutover starts a new database and rebuilds FTS data; existing 0.x postings are never reused. The unexported 1.0 scan route supports MATCH and BM25 now; its durable accelerator and public cutover remain in development.

Ranking

BM25 scores a row against the same query, and is an ordinary expression — select it, order by it, filter on it:

SELECT name,
       BM25(name, brand) AGAINST 'single origin ethiopia' AS score
FROM products
WHERE MATCH(name, brand) AGAINST 'single origin ethiopia'
ORDER BY score DESC
LIMIT 20

Scores use the whole column's term statistics, so they are comparable across rows in a way a naive term count is not.

Search text can be a parameter, which keeps one statement reusable as the user types:

const sql =
  "SELECT name FROM products WHERE MATCH(name) AGAINST $1 ORDER BY BM25(name) AGAINST $1 DESC";
const result = await db.query(sql, { params: [searchText] });

Prefix matching

A trailing * matches by prefix, which is what a search-as-you-type box needs:

SELECT name FROM products WHERE MATCH(name) AGAINST 'grind*'

Multiple terms are combined; a row matches when it contains all of them.

Indexes build themselves

A MATCH on an unindexed column scans and re-verifies, which is fast enough on small tables and slow on large ones. Above a threshold — 4,096 visible rows by default — the first MATCH on an append-only column schedules a background index build and answers from the scan meanwhile. Correctness never waits on it. The build runs a few milliseconds at a time between other work, and its stored chunks grow as a large corpus needs, so it completes whatever the table's size.

To build one explicitly, before a user's first search rather than during it:

await db.buildFtsIndex("products", "name");

Later inserts land as small index deltas so commits stay proportional to the rows they add. When the tail exceeds 16 delta-bearing commits, the committing engine schedules a rebuild even if nobody searches the column again. The persistent tail and the work of the next search therefore do not grow with an all-day write session.

Compaction keeps the index. A fold of an append-only table rewrites its rows without changing any of them, so searches keep using the same index before, during, and after it.

Append-only columns only

A full-text index can only cover a table that has not been updated or deleted from:

Full-text indexes support append-only tables; orders has keyed mutations

MATCH still works on such a table — it just scans. The index is a pruning accelerator that the scan re-verifies, so a missing, stale, or invalidated index costs time, never correctness.

An index stops at the first update, delete, or upsert of its table, and at the first fold of a table with a primary key. Searches then scan, and the next garbage collection deletes the index's stored postings.

What it does to a term

Terms are Unicode-normalized (NFKC), lowercased, and split on non-alphanumeric boundaries. CJK runs are indexed as character bigrams, so those languages search without a dictionary. There is no stemming, no stop-word list, and no language configuration: running does not match run. That floor is deliberate — a tokenizer that guesses a language is wrong for somebody, and this one a caller can predict and pre-process around.

Tuning the threshold

const db = new MinnowDatabase(store, { ftsAutoIndexRows: 20_000 });

Raise it when tables are small and searches rare. Set it to 0 to build on the first search at any table size; set it above the largest expected table to keep searches scan-only and manage indexes yourself.

On this page