Full-Text Search

ScramDB includes built-in functions for text search: one to tokenize a document and count how often a term appears, and one to score how relevant a document is to a query using BM25, the ranking formula behind most modern search engines. Both run as plain SQL over your existing tables, so search results always reflect your latest writes.

Note: The fts_* and bm25_* functions come from ScramDB's built-in packages. Install and register them once first, see Install and use a package.

We will use a small library of documents:

CREATE TABLE articles (
    id    BIGINT PRIMARY KEY,
    title VARCHAR(200),
    body  TEXT
);

INSERT INTO articles (id, title, body) VALUES
    (1, 'Ranking basics', 'ranking sorts results the best ranking wins'),
    (2, 'Search intro',   'search finds documents that match a query'),
    (3, 'Ranking deep',   'good ranking blends term frequency and length');

Count how often a term appears

fts_term_frequency(doc, term) lowercases the document, splits it on anything that is not a letter or digit, and counts exact matches of the term. It returns an integer. Matching ignores case and punctuation:

SELECT fts_term_frequency('The quick brown fox. The lazy dog. The end.', 'the');
-- 3

Simple keyword search

To find documents that contain a term, filter on a term frequency above zero:

SELECT id, title
FROM articles
WHERE fts_term_frequency(body, 'ranking') > 0;

You can rank by raw frequency, but a document that simply repeats a word is not necessarily the most relevant. For real relevance ranking, use BM25.

Rank by relevance with BM25

bm25_term_score scores how relevant a single document is to a single search term. It rewards documents where the term is frequent, discounts terms that appear in almost every document, and normalizes for document length so a long document does not win just for being long.

It takes the term and corpus statistics as explicit arguments:

Argument	Meaning
tf	how many times the term appears in this document
doc_len	the length of this document (in words)
avg_doc_len	the average document length across the collection
doc_freq	how many documents contain the term at all
corpus_size	the total number of documents
k1	term-frequency saturation knob (1.2 is the usual default)
b	length-normalization knob (0.75 is the usual default)

A quick feel for it, using two documents where both contain the term (corpus_size 2, avg_doc_len 2, doc_freq 2). The shorter document scores higher:

SELECT bm25_term_score(1, 1, 2.0, 2, 2, 1.2, 0.75);   -- a 1-word doc, about 0.229
SELECT bm25_term_score(1, 3, 2.0, 2, 2, 1.2, 0.75);   -- a 3-word doc, about 0.151

Rank a whole table for one term

You compute the corpus statistics with ordinary aggregates, then feed them into the score. This query ranks every matching article for the term ranking. Document length here is a simple word count; for a big collection, store it as a column (see the tips below).

WITH corpus AS (
    SELECT
        CAST(COUNT(*) AS INT) AS corpus_size,
        AVG(LENGTH(body) - LENGTH(REPLACE(body, ' ', '')) + 1) AS avg_doc_len
    FROM articles
),
term AS (
    SELECT CAST(COUNT(*) AS INT) AS doc_freq
    FROM articles
    WHERE fts_term_frequency(body, 'ranking') > 0
)
SELECT
    a.id,
    a.title,
    bm25_term_score(
        fts_term_frequency(a.body, 'ranking'),                 -- tf
        LENGTH(a.body) - LENGTH(REPLACE(a.body, ' ', '')) + 1, -- doc_len
        c.avg_doc_len,
        t.doc_freq,
        c.corpus_size,
        1.2,                                                   -- k1
        0.75                                                   -- b
    ) AS score
FROM articles a
CROSS JOIN corpus c
CROSS JOIN term t
WHERE fts_term_frequency(a.body, 'ranking') > 0
ORDER BY score DESC
LIMIT 10;

Rank for a multi-word query

A query with several words scores each word separately and adds the scores. Each term brings its own tf and its own doc_freq, while the corpus size and lengths are shared. For the query ranking length:

WITH corpus AS (
    SELECT
        CAST(COUNT(*) AS INT) AS corpus_size,
        AVG(LENGTH(body) - LENGTH(REPLACE(body, ' ', '')) + 1) AS avg_doc_len
    FROM articles
),
freqs AS (
    SELECT
        CAST(COUNT(*) FILTER (WHERE fts_term_frequency(body, 'ranking') > 0) AS INT) AS df_ranking,
        CAST(COUNT(*) FILTER (WHERE fts_term_frequency(body, 'length')  > 0) AS INT) AS df_length
    FROM articles
)
SELECT
    a.id,
    a.title,
    bm25_term_score(
        fts_term_frequency(a.body, 'ranking'),
        LENGTH(a.body) - LENGTH(REPLACE(a.body, ' ', '')) + 1,
        c.avg_doc_len, f.df_ranking, c.corpus_size, 1.2, 0.75
    )
    +
    bm25_term_score(
        fts_term_frequency(a.body, 'length'),
        LENGTH(a.body) - LENGTH(REPLACE(a.body, ' ', '')) + 1,
        c.avg_doc_len, f.df_length, c.corpus_size, 1.2, 0.75
    ) AS score
FROM articles a
CROSS JOIN corpus c
CROSS JOIN freqs f
ORDER BY score DESC
LIMIT 10;

Tips

• Store document length as a column. Computing word length on the fly is fine for a demo. For a large collection, add a doc_len column, fill it when you write each row, and read it directly in the score. That keeps ranking fast.
• Precompute corpus statistics. corpus_size and avg_doc_len change slowly. Compute them once (for example into a small stats table) and reuse them across searches instead of scanning the whole collection every query.
• Tune k1 and b if you need to. The defaults 1.2 and 0.75 work well for most text. Raise b toward 1.0 to penalize long documents more, or lower it toward 0.0 to ignore length.
• Results are always current. Because ranking reads your live table, a document you just inserted is searchable immediately, with nothing to reindex.