Search in Polecat, Part 1: Full text search on an index it owns

· 8 min read · polecat sql-server dotnet critter-stack full-text-search bm25 document-database
Search in Polecat, Part 1: Full text search on an index it owns

Series: Search in Polecat

  1. 1 Search in Polecat, Part 1: Full text search on an index it owns
  2. 2 Search in Polecat, Part 2: Vector search on a computed VECTOR column
  3. 3 Search in Polecat, Part 3: Hybrid search and reciprocal rank fusion
Next →

Polecat is the Critter Stack on SQL Server: the same session API, LINQ provider and projection model as Marten, over SQL Server 2025.

Vector search shipped in 5.28, full text and hybrid search in 5.29. This post is keyword search; Part 2 is vector search, Part 3 fuses them.

Code compiles against Polecat 5.29.0; output is from a run against SQL Server 2025 (17.0.4085.5) over six support articles.

Declare and query

The document this series searches, a support knowledge base article:

/// <summary>A support knowledge base article. Body is the member full text search reads.</summary>
public class Article
{
    public Guid Id { get; set; }
    public required string Title { get; set; }
    public required string Body { get; set; }
}

Declare a full-text index on the member you want searched:

opts.Schema.For<Article>().FullTextIndex(x => x.Body);

PlainTextSearch takes every term in any order; PhraseSearch requires them adjacent and in order.

// Every term, in any order. The corpus has "hard water", never "water hard".
var plain = await session.Query<Article>()
    .Where(x => x.Body.PlainTextSearch("water hard"))
    .ToListAsync();
// The same two terms, now required adjacent and in that order.
var phrase = await session.Query<Article>()
    .Where(x => x.Body.PhraseSearch("hard water"))
    .ToListAsync();

var reversed = await session.Query<Article>()
    .Where(x => x.Body.PhraseSearch("water hard"))
    .ToListAsync();
Where(…)matches
x.Body.PlainTextSearch(“water hard”)Descaling in hard water areas
x.Body.PhraseSearch(“hard water”)Descaling in hard water areas
x.Body.PhraseSearch(“water hard”)(none)

Both names are Marten’s, so the query ports unchanged. Empty, whitespace-only and punctuation-only searches match nothing.

It is not SQL Server’s full-text engine

SQL Server has had one for twenty years. Polecat does not use it. Asking this server to:

SERVERPROPERTY('IsFullTextInstalled') = 0

CREATE FULLTEXT CATALOG in master:
    Msg 9966: Cannot use full-text search in master, tempdb, or model database.
CREATE FULLTEXT CATALOG: SUCCEEDED
FULLTEXT INDEX on the json body:
    Msg 7670: Column 'data' cannot be used for full-text search because it is not a character-based, XML, image or varbinary(max) type column or it is encrypted.
FULLTEXT INDEX on a computed column over the json:
    Msg 7609: Full-Text Search is not installed, or a full-text component cannot be loaded.
FULLTEXT INDEX on a plain nvarchar column:
    Msg 7609: Full-Text Search is not installed, or a full-text component cannot be loaded.
  • Absent from the stock image. mssql/server:2025-latest reports IsFullTextInstalled = 0; every CREATE FULLTEXT INDEX fails with Msg 7609.
  • Refused in master with Msg 9966, where Polecat’s test suite isolates by schema.
  • Cannot index a json column. Msg 7670, the one failure here that is not about this server: the engine takes character, XML, image and varbinary(max), and a document body is json.
  • Populates asynchronously, so a document written and immediately searched may not be found. Marten’s tsvector index is synchronous, so this would be a silent divergence between two stores that otherwise behave alike.

Polecat maintains its own inverted index instead: one row per token in a side table, kept current by a trigger.

What lands on disk

CREATE TABLE dbo.pc_ft_article (
    doc_id uniqueidentifier NOT NULL,
    tenant_id varchar(250) NOT NULL,
    member varchar(200) NOT NULL,
    term varchar(255) NOT NULL,
    pos int NOT NULL
);
CREATE INDEX ix_pc_ft_article_term ON dbo.pc_ft_article (term, doc_id) INCLUDE (member, pos, tenant_id);

member is there because FullTextIndex takes several members, each searched independently, and one table serves all of them. pos is the token’s ordinal, which makes PhraseSearch possible. tenant_id scopes the index under conjoined multi-tenancy.

CREATE   TRIGGER [dbo].[tr_pc_ft_article] ON [dbo].[pc_doc_article] AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE ft FROM [dbo].[pc_ft_article] ft INNER JOIN deleted d ON ft.doc_id = d.id;
    INSERT INTO [dbo].[pc_ft_article] (doc_id, tenant_id, member, term, pos)
    SELECT i.id, '*DEFAULT*', 'Body', s.value, s.ordinal
    FROM inserted i
    CROSS APPLY STRING_SPLIT(
    LOWER(TRANSLATE(
        CAST(JSON_VALUE(i.data, '$.body') AS nvarchar(max)),
        '!"#$%&()*+,-./:;<=>?@[\]^_{|}~''',
        '                               ')), N' ', 1) s
WHERE LEN(s.value) > 0
END

STRING_SPLIT’s third argument supplies ordinal; TRANSLATE maps punctuation to spaces first. One trigger covers every declared member, since rendering one per member would have the second silently replace the first.

Declaring an index also backfills existing rows: a trigger only fires on writes made after it exists, so without the backfill you get an index matching nothing. Six articles produce 129 token rows.

Article.Title  Descaling in hard water areas
Article.Body   Limescale builds up fastest in hard water areas. Run the descaling cycle monthly to clear mineral deposits before they reach the boiler.

rows in pc_ft_article for that article, as pos:term

1:limescale  2:builds  3:up  4:fastest  5:in  6:hard  7:water  8:areas  10:run  11:the  12:descaling  13:cycle  14:monthly  15:to  16:clear  17:mineral  18:deposits  19:before  20:they  21:reach  22:the  23:boiler

Position 9 is the empty fragment between areas. and Run, dropped by LEN(s.value) > 0. pos is the split ordinal, not a dense sequence; PhraseSearch compares consecutive positions, so gaps are harmless.

Exact codes

One article in the corpus is titled Fault E-142 on the Titan-9, a machine fault code. Exact identifiers are what keyword search is best at:

var byCode = await session.Query<Article>()
    .Where(x => x.Body.PlainTextSearch("E-142"))
    .ToListAsync();
PlainTextSearchmatches
”E-142”Fault E-142 on the Titan-9
”142”Fault E-142 on the Titan-9
”e”Fault E-142 on the Titan-9
”E-143”(none)

Note what 142 and e do. The hyphen is a punctuation character, so E-142 is stored as two tokens, e and 142, and either one matches on its own. That is the price of a tokenizer this simple, and it is worth knowing before you index part numbers: search the whole code and you get the right article, search a fragment and you may get it too.

The cost: tokenization is literal

Lowercase, split on whitespace and punctuation. No stemming, no thesaurus, no language-aware word breaking:

PlainTextSearch("deposits")    matches 2 of 6 articles
PlainTextSearch("deposit")     matches 0 of 6 articles
PlainTextSearch("cleaning")    matches 1 of 6 articles
PlainTextSearch("clean")       matches 0 of 6 articles

A stemming index answers 2 and 2, then 1 and 1. Normalise the text yourself if you need linguistic matching. Marten’s regConfig overloads have no counterpart here and are absent rather than ignored: a PostgreSQL text-search configuration means nothing against an index Polecat tokenizes itself.

Ranking

Where answers which documents match. For how well, there is a ranked search scored with Okapi BM25:

var scored = await session.FullTextSearchWithScoresAsync<Article>(
    x => x.Body, "descaling water", limit: 5);

FullTextSearchWithScoresAsync(x => x.Body, "descaling water", limit: 5)

rankBM25 scorearticle
12.0398Descaling in hard water areas
21.3885Water hardness and machine lifetime
31.0810Monthly maintenance schedule

BM25 needs no extra schema: term frequency, document length, document frequency and the corpus aggregates all derive from the token rows, so ranking is one statement with four CTEs. The cost is that it reads the token table for that member on every call, so measure before trusting it on a large corpus.

k1 = 1.2 and b = 0.75, fixed at the conventional defaults.

Scores are comparable within one result set and not between two. BM25’s IDF term depends on the corpus, so a document’s score moves as others are written. Order by it or use it as a relative floor; do not store it.

What it refuses

no full-text index on the type
    InvalidOperationException: 'Note' declares no full-text index, so there is nothing to search. Declare one with Schema.For<Note>().FullTextIndex(x => x.Body).

a member that is not the declared one
    InvalidOperationException: 'Article.Title' is not a declared full-text member. Declared: Body.

Next

Keyword search finds the words you typed, and will not find the limescale article when you searched for hard water. Part 2 is vector search over the VECTOR type; Part 3 fuses the two rankings.

Reference

Series: Search in Polecat

  1. 1 Search in Polecat, Part 1: Full text search on an index it owns
  2. 2 Search in Polecat, Part 2: Vector search on a computed VECTOR column
  3. 3 Search in Polecat, Part 3: Hybrid search and reciprocal rank fusion
Next →
#polecat #sql-server #dotnet #critter-stack #full-text-search #bm25 #document-database