Ranking search results is the feature that turns a filter into a search engine, and it is also the feature most databases make you leave the database to get, because the usual answer is “the FTS index has a rank column if you set it up our way, otherwise stand up Elasticsearch,” which is how a PHP app that needed to sort four hundred rows by relevance ends up with a JVM, a cluster, and a sync job. We wanted relevance scoring to behave like LENGTH() or COALESCE(): an expression you drop into a SELECT, usable in ORDER BY and WHERE and HAVING, composable with everything else the planner already knows, and requiring nothing to exist before you call it. That is what mongreldb_fts_rank(text, query) is, a scalar UDF registered directly into the query engine, and the interesting part is not the formula inside it but the decision to make ranking an ordinary function at all, because that decision is what lets it score things no index will ever see.

What the function actually does

The engine side of the query layer is DataFusion over Arrow record batches, so a UDF in MongrelDB is not a row-at-a-time callback the way sqlite3_create_function was back when we all registered PHP closures into SQLite and wondered why the query took nine seconds; FtsRankUdf implements ScalarUDFImpl, receives the whole batch as columnar Arrow arrays, tokenizes the query once, and returns a Float64 array with one score per row, which means the per-row cost is a couple of vector passes over memory the engine already had in flight. The scoring is BM25-inspired term saturation, keeping the classic k1 = 1.2 and b = 0.75 constants in the code because every BM25 implementation since the nineties has started from them, though the length-normalization term is collapsed out, the document length over average document length ratio is fixed at one, which makes b cancel arithmetically, and what survives is the term-frequency saturation curve tf * (k1 + 1) / (tf + k1):

SELECT id, title,
       mongreldb_fts_rank(content, 'database performance') AS score
FROM articles
WHERE mongreldb_fts_rank(content, 'database performance') > 0
ORDER BY score DESC
LIMIT 10;

The tokenizer splits on any non-alphanumeric character and lowercases, Unicode-aware because Rust’s char::is_alphanumeric is, so '日本語' and 'naïve' tokenize the way you would hope rather than the way a 2003-era C tokenizer would have mangled them. The signature accepts Utf8, LargeUtf8, Utf8View, and the binary string types, and the function is marked Immutable, which matters more than it looks, because an immutable marker is what lets the planner constant-fold the query argument, cache results, and reorder the expression around joins without second-guessing whether the score will change between calls.

Why a function and not an index flag

The reason to write our own instead of wiring ranking to the full-text index is that an index-coupled rank can only ever rank what the index indexed, and the queries that actually hurt are the ones where the text comes from somewhere the index does not cover: a computed column, a json_extract of a nested field, a concatenation of two columns, a subquery result. A scalar function does not care where its input came from, it ranks whatever string the expression tree hands it, so the same mongreldb_fts_rank call that scores a plain content column also scores json_extract(meta, '$.summary') or title || ' ' || coalesce(subtitle, '') with zero setup, and it shows up inside CTEs and window functions because to the planner it is just another expression. Retrieval and scoring being separate layers is the whole design: the FM-index and the fts_docs virtual table answer “which documents might match” at scale, and the UDF answers “how well does this specific string match” for everything else, including the long tail of tables that will never justify an index at all.

From the Kit side it is one line of ordinary SQL through the same sqlRows surface everything else uses:

const rows = db.sqlRows(
  "SELECT id, title, mongreldb_fts_rank(content, 'database performance') AS score " +
  "FROM articles ORDER BY score DESC LIMIT 10"
);

No new client method, no new wire shape, no version negotiation, because a function the server already knows is the cheapest feature to ship across three SDKs and a PHP client: if the engine can parse it, every client can call it, which is the same trick we pulled with the rest of the extended SQL surface and the reason the conformance fixtures can pin ranking behavior byte-for-byte across TypeScript, Rust, and Python.

The honest tradeoff

The simplification worth being upfront about is IDF: real BM25 weights terms by how rare they are across the whole corpus, and a scalar UDF that sees one document at a time has no corpus, so the function omits inverse document frequency entirely and every query term weighs the same, which is a deliberate scope cut rather than an approximation, since there is nothing to approximate from. For the workload this UDF is aimed at, ranking a few hundred or few thousand candidate rows that a WHERE clause already narrowed down, term saturation plus term frequency gets you essentially the same ordering, and nobody has ever noticed IDF missing on a four-hundred-row result set. When you do need corpus-accurate relevance, the escape hatch is already in the engine: the fts_docs virtual table module maintains a real inverted index with global statistics, and that is where accurate BM25 lives, with the UDF remaining the right tool for ad-hoc scoring on regular tables. That split is the modern equivalent of a lesson from the MySQL years: MATCH ... AGAINST was fine until you wanted to rank anything it had not indexed, at which point you were stuck, and the fix was never a better index flag but a scoring primitive that stood on its own.