Full-text search

Native full-text search over skaidb tables: BM25-ranked retrieval with MATCH() / SEARCH() SQL predicates, backed by an embedded Tantivy index per search index. This documents the shipped state; the SQL grammar is in QUERY_SYNTAX.md, and the phase history in git.

Status: phases 0–8 complete — phases 1–5 (single-node core: DDL, index maintenance on put/delete, score(), top-k pushdown, crash recovery, rebuild; analysis & mappings: analyzer registry, per-column configuration, typed fast fields, .keyword twins, copy_to; query DSL: the full predicate family MATCH/MATCH_PHRASE/MATCH_PREFIX/FUZZY/ WILDCARD/REGEXP/SEARCH/MATCH_CROSS, AND/OR/NOT composition plus BOOSTED() optional scoring, dis-max multi-field scoring, HIGHLIGHT() snippets, per-hit BM25 explain over the ES subset, multi-word synonyms; cluster: scatter-gather top-k, per-replica indexes, topology continuity; performance: bulk ingest path, writer heap under memory_target — benchmarked vs Elasticsearch, see BENCHMARKS.md) plus aggregations: GROUP BY and aggregate functions over search queries, with an exact fast-field facet pushdown.

Using it

CREATE SEARCH INDEX articles_fts ON articles (title, body, year, published)
  WITH (analyzer = 'english', refresh_ms = 1000,
        title.boost = 2.0, title.keyword = true,
        title.copy_to = 'everything', body.copy_to = 'everything',
        year.type = 'long', published.type = 'bool');

SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet FROM articles
WHERE MATCH(body, 'quick brown fox') AND published = true
ORDER BY score() DESC LIMIT 10;

SELECT id FROM articles
WHERE SEARCH('title:"rust database" +body:performance year:[2020 TO 2024]')
ORDER BY score() DESC LIMIT 20;

-- Search predicates compose with AND/OR/NOT:
SELECT id FROM articles
WHERE (MATCH(body, 'rust') OR MATCH(title, 'rust')) AND NOT MATCH(body, 'draft');

REBUILD SEARCH INDEX articles_fts;   -- re-index from the table
DROP SEARCH INDEX articles_fts;

An index covers one or more document paths (dotted paths into nested documents work: meta.title). Arrays index every element (multi-valued fields). Rows are schema-less — the declaration is the mapping: a value that doesn't fit its column's declared type is simply not indexed for that column.
refresh_ms (default 1000) controls how quickly writes become searchable — near-real-time. A write only queues its index work; the server's refresher thread feeds the Tantivy writer and commits (the fsync) every refresh_ms with no engine lock held, so a slow disk's index commit never stalls other statements. A search after a write on the same node commits the serving index first — also outside the lock — so you read your own writes immediately; the refresher ticks every 200 ms, so even a table receiving no further traffic becomes searchable on the shared/read-only path within refresh_ms + one tick. With refresh_ms = 0 each write's index work is committed before the write's statement returns, right after it releases the engine lock, on every node that applied the write.
An update that changes no field the index covers (a tag, a counter) does not re-index the row: the index already holds that text. On a cluster, the full-text work of replicated writes runs outside the storage engine's lock, and a late, older version of a row never overwrites a newer one in the index.
Measured against Elasticsearch on identical hardware: ~1.5× ES's bulk ingest and single-digit-fraction query latencies (see BENCHMARKS.md).

From an application

Search is ordinary SQL, so every driver runs it through its normal query call — there is no separate search API to learn. The query text binds as a parameter, so user input never gets concatenated into SQL. score() comes back in a column named score even without an alias; HIGHLIGHT needs one. Placeholders differ per driver (? everywhere except Node.js and Ruby, which use $1) — see the matrix in HOWDOI.md.

SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet
FROM articles
WHERE MATCH(body, ?) AND published = true
ORDER BY score() DESC LIMIT 10;

cur = conn.cursor()
cur.execute("SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet "
            "FROM articles WHERE MATCH(body, ?) AND published = true "
            "ORDER BY score() DESC LIMIT 10", ("quick brown fox",))
for id_, title, score, snippet in cur.fetchall():
    print(id_, score, snippet)

const res = await client.query(
  `SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet
   FROM articles WHERE MATCH(body, $1) AND published = true
   ORDER BY score() DESC LIMIT 10`, ['quick brown fox']);
for (const row of res.rows) console.log(row.id, row.score, row.snippet);

rows, err := db.Query(`SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet
    FROM articles WHERE MATCH(body, ?) AND published = true
    ORDER BY score() DESC LIMIT 10`, "quick brown fox")
defer rows.Close()
for rows.Next() {
    var id int
    var title, snippet string
    var score float64
    rows.Scan(&id, &title, &score, &snippet)
}

Skaidb.ResultSet rs = conn.prepare(
        "SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet "
      + "FROM articles WHERE MATCH(body, ?) AND published = true "
      + "ORDER BY score() DESC LIMIT 10")
    .setString(1, "quick brown fox")
    .executeQuery();
while (rs.next()) System.out.println(rs.getDouble("score") + " " + rs.getString("snippet"));

res = conn.exec_params(<<~SQL, ["quick brown fox"])
  SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet
  FROM articles WHERE MATCH(body, $1) AND published = true
  ORDER BY score() DESC LIMIT 10
SQL
res.each { |row| puts "#{row['score']} #{row['snippet']}" }

$stmt = $db->prepare('SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet
    FROM articles WHERE MATCH(body, ?) AND published = true
    ORDER BY score() DESC LIMIT 10');
$stmt->execute(['quick brown fox']);
foreach ($stmt->fetchAll() as $row) { echo $row['score'], ' ', $row['snippet'], PHP_EOL; }

using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet " +
                  "FROM articles WHERE MATCH(body, ?) AND published = true " +
                  "ORDER BY score() DESC LIMIT 10";
cmd.Parameters.Add("quick brown fox");
using var reader = cmd.ExecuteReader();
while (reader.Read()) Console.WriteLine($"{reader.GetDouble(2)} {reader.GetString(3)}");

use skaidb_proto::Response;
use skaidb_types::Value;
let mut q = client.prepare(
    "SELECT id, title, score(), HIGHLIGHT(body, 120) AS snippet \
     FROM articles WHERE MATCH(body, ?) AND published = true \
     ORDER BY score() DESC LIMIT 10")?;
if let Response::Rows { rows, .. } =
    client.execute_prepared(&mut q, &[Value::String("quick brown fox".into())])?
{
    for row in rows { println!("{} {}", row[2], row[3]); }
}

SEARCH('…') takes a bound string the same way, so a query-string UI passes the user's text straight through as a parameter.

A search index on a range-partitioned table (WITH (partition_by = 'range(col, 1d)'), see QUERY_SYNTAX.md) fans to one physical index per partition; a MATCH() ranks across them with merged BM25 statistics, so a term's IDF is corpus-wide rather than per partition.

Analyzers

Set the index default with analyzer = '...', or per column with <column>.analyzer = '...':

standard — Unicode word split (UAX §29: dog's stays one token) + lowercase (the default). Measured at 98.5% strict top-10 result-set overlap with Elasticsearch on a 280 k article corpus (see BENCHMARKS.md).
folding — standard + ASCII folding (café → cafe) for accent-insensitive matching without stemming.
Languages (standard + stopwords where a list exists + Snowball stemmer): arabic, danish, dutch, english, finnish, french, german, greek, hungarian, italian, norwegian, portuguese, romanian, russian, spanish, swedish, tamil, turkish.
whitespace — split only, case kept.
keyword — the whole value as one term.
ngram(min,max) — lowercased character ngrams (substring matching).
edge_ngram(min,max) — lowercased prefix ngrams (search-as-you-type); pair with search_analyzer = 'standard' so queries aren't ngrammed too.
Custom pipelines — compose your own chain (ES custom analyzers): '<tokenizer> | <filter> | …', e.g. 'unicode | lowercase | stopwords(the,a,an) | stem(english)' or 'whitespace | lowercase | ascii_folding'. Tokenizers: unicode (UAX §29, the standard base), whitespace, keyword, ngram(min,max), edge_ngram(min,max), regex(<pattern>) (each pattern match is a token; a | inside the parens is pattern payload). Filters, applied in order: lowercase, ascii_folding, alphanum_only, remove_long(<max chars>), stop(<language>), stopwords(w1, w2, …), stem(<language>). Note pipeline tokenizers are bare — add lowercase/remove_long yourself (the built-in standard/ngram names include them). Char filters (ES char_filter) go before the tokenizer: html_strip (tags and comments become a space, &-style and numeric entities decode), pattern_replace(<regex>, <replacement>) (every match replaced, $1 group references expand, the last comma separates the two, no comma = delete the matches) and nfkc (Unicode NFKC normalization, the ES icu_normalizer — ﬁ → fi, full-width forms fold) — 'html_strip | pattern_replace(\d+, N) | nfkc | unicode | lowercase'. They rewrite the text the tokenizer sees but every token keeps its offsets in the original text, so highlighting marks the source as written (a match inside stripped markup highlights the word, not the tag).

Query text is analyzed with the field's query-time analyzer: <column>.search_analyzer if set, else the index-time analyzer.

Synonyms (synonyms = 'quick,fast,speedy; new york,nyc,big apple') expand at query time in MATCH — each group entry is analyzed with the field's own pipeline so stemming lines up. Multi-word entries work in both directions: an entry that occurs in the query as a consecutive token sequence expands to its peers, and multi-word peers expand as phrase alternatives (a query for nyc matches "new york" only where the words are adjacent). MATCH_PHRASE and the query-string language do not expand. Because expansion is query-time, synonyms hot-reload:

ALTER SEARCH INDEX articles_fts SET (synonyms = 'quick,fast; car,auto');

ALTER SEARCH INDEX … SET changes query-time-safe options in place (synonyms, refresh_ms, <col>.search_analyzer, <col>.boost) with no reindex; index-time options (analyzers, types, twins, copy_to) error — those change the stored postings and need DROP + CREATE.

Per-column options

WITH (...) takes global options (analyzer, refresh_ms) and <column>.<option> per-column options:

option	meaning
`<col>.type`	`text` (default), `keyword`, `long`, `double`, `bool`, `date`
`<col>.analyzer`	index-time analyzer for this text column
`<col>.search_analyzer`	query-time analyzer override
`<col>.boost`	score multiplier in multi-field queries (positive number)
`<col>.keyword`	`true` adds a `<col>.keyword` exact-match twin
`<col>.copy_to`	also index this text into a named composite field

Typed columns (long, double, date, bool) become fast fields, addressable from the SEARCH() query-string language (year:1999, price:[30 TO *], published:true). double accepts integer values; date accepts timestamp and millisecond-integer values. MATCH() on a non-text column is an error.
Residual pushdown: ordinary WHERE predicates on declared columns ride into the index alongside the search predicate instead of being checked row by row — col = literal and col IN (...) on keyword, long, double and bool columns (and on a text column through its .keyword twin), </<=/>/>=/BETWEEN on long/double columns, and AND/OR trees of those (a range only at the top level of the WHERE's AND chain). A pushed predicate matches exactly what SQL matches: the index holds a value only when the row's value fits the declared type, an array contributes each element (so = is SQL's array containment), and a range over a row that turns out multi-valued makes the index decline the aggregate to the row side. Everything else — other operators, NOT, IS NULL, a literal of another type, an undeclared column — stays row-side. This is what makes a filtered count (WHERE account = ? AND MATCH_PHRASE(to_addresses, ?)) an index-only answer and keeps a filtered sorted page from walking hits the filter would drop. EXPLAIN lists the split as search_residual_pushed and search_residual_row_side.
Key-list restriction: a WHERE that pins every primary-key column to = / IN literals (account = ? AND id IN (…), up to 4096 keys in the cross product) restricts the search to those rows: the key set rides into the index as a non-scoring term-set filter on the document key, so only listed documents are hits — no other hit is read as a row. It scores nothing and the corpus statistics are the index's own, so score(), ORDER BY score(), sorted pages and counts are exactly the unrestricted search's answer restricted to the list, for every search predicate (MATCH_BEST, WILDCARD, NOT MATCH, …). The key leaves are answered by the index (a count(*) over them still pushes down) unless a key column is pinned twice, where the set is a superset and the pins also stay row-side. The set holds every stored key the row-side = admits, not only the literal's own type: a number also matches keys stored as another numeric type or a Timestamp of that many ms (id = 1.0 finds Int id 1), and an ISO-8601 string also matches a Timestamp key at that instant. A list the set cannot hold exactly — a Timestamp literal (any ISO string spelling of it would match), a number of magnitude 2^53 or more, an array — leaves the search unrestricted and the list filters row-side. On a cluster mid-roll, a member too old to decode the key set is re-asked for its unrestricted hits (as keys and scores, no rows) and the coordinator keeps the listed ones: the same answer, at that member's pre-restriction search cost. EXPLAIN shows search_key_set (N keys counts the listed key values).
.keyword twins index the raw string alongside the analyzed text: MATCH(title, 'rust handbook') matches analyzed terms while MATCH(title.keyword, 'Rust Handbook') matches only the exact original string.
copy_to aggregates several columns into one searchable composite field (analyzed with the index default) — the ES copy_to pattern for "search everything" fields. Several columns may share one target.
Options are validated at CREATE time; unknown options, unknown analyzers, or analyzer/keyword/copy_to options on non-text columns error.
Changing columns, types, or index-time analyzers requires a rebuild (the engine rebuilds automatically on open if the definition changed); search_analyzer is query-time-only and needs none.

Predicates

Analyzed predicates (query text goes through the field's query-time analyzer):

MATCH(col, 'text') — any analyzed term matches (ES match).
MATCH_PHRASE(col, 'text' [, slop]) — terms in order within slop transpositions (ES match_phrase).
FUZZY(col, 'text' [, distance]) — Levenshtein ≤ 2 per term (ES fuzzy).
SEARCH('query-string') — the mini-language: bare terms over text columns, "phrase", col:term, +must, -must_not, AND/OR, and ranges over typed columns (year:[2020 TO 2024], published:true). A +/- in front of AND, OR or NOT means the word itself (+OR requires the term "or"); bare, they are operators. * on its own matches every indexed row, col:* the rows that have the column, and * is an open range bound (year:[2020 TO *]); a field-less * glued to punctuation (*{, *:, *") is refused as an invalid query.
MATCH_CROSS(col, col, …, 'text') — term-centric multi-field match (ES multi_match cross_fields): the fields behave like one big field — each term scores by its best field and the terms OR together, so a query whose terms are spread across columns ('bob smith' against first_name/last_name) still matches and ranks sensibly. Per-field MATCH composed with OR is field-centric instead (each field scores the whole query).
MATCH_BEST(col, col, …, 'text') — field-centric dis-max over an explicit column subset (ES multi_match best_fields): a row matches if any listed column matches and scores as its best single field. The same match set as OR-ing per-field MATCHes, spelled in one predicate.

Term-level pattern predicates (not analyzed — they run against the indexed terms, so with a lowercasing analyzer write patterns lowercase):

MATCH_PREFIX(col, 'qu') — term prefix (ES prefix).
WILDCARD(col, 'qu*ck') — * any run, ? any one char (ES wildcard).
REGEXP(col, 'qu.[ck]+') — regular expression (ES regexp).

Composition: search predicates combine freely with AND/OR/NOT among themselves (ES bool must/should/must_not); ordinary SQL conditions join at the top level with AND and filter the hits afterward. BOOSTED(required, optional…) adds an optional-scoring shape: the required predicate decides which rows match, and each optional predicate only raises the score of rows that already match (tantivy Must + Should — ES bool must + should under the default minimum_should_match: 0). Every argument must itself be a search predicate. AT_LEAST(n, p1, p2, …) matches rows where at least n of the predicates match, every matching one adding its score (ES should with minimum_should_match: n); BOOST(predicate, factor) scales a predicate's score by factor without changing what matches (ES per-field ^boost) — MATCH(title, 'x') OR BOOST(MATCH(body, 'x'), 0.5). Mixing a search predicate with an ordinary condition under OR/NOT is rejected — the index cannot serve it. A NOT search returns only rows the index knows: a row with none of the indexed columns present is never returned.

Multi-field scoring is dis-max (a row scores as its best field, ES best_fields), with per-column boosts applied.

Similarity & suggestions: MORE_LIKE_THIS(col, 'like text') finds textually similar rows (the like-text's most distinctive terms by in-index IDF, OR-ed — permissive defaults so short like-texts work). SUGGEST '<text>' ON <index> returns per-token "did you mean" terms from the index dictionary (Levenshtein ≤ 2, doc-frequency ranked); completion /search-as-you-type is the edge_ngram + MATCH_PREFIX pattern.

Highlighting: HIGHLIGHT(col [, max_chars [, pre_tag, post_tag [, no_match_size [, fragments]]]]) in the projection returns the best-scoring snippet of the column's text (default fragment size 150 chars) with matching terms wrapped in tags (… by default), other text HTML-escaped. Stemming is respected — a query for jumping highlights jumps. Only valid together with a search predicate; highlight multiple columns by calling HIGHLIGHT() once per column. The snippet re-reads the row's live text (not stored offsets), so it always reflects the current document.

Custom tags (ES pre_tags/post_tags): pass a string pair after the fragment size, e.g. HIGHLIGHT(body, 40, '', '') → slow roasted vegetables.
no_match_size (ES): a trailing integer returns that many leading characters (HTML-escaped) when the column had no match, instead of an empty string — HIGHLIGHT(body, 40, '', '', 80).
Multiple fragments (ES number_of_fragments): a final fragment count (2–10) switches the highlight value to an array of up to that many fragments, in text order, best match-density windows first — HIGHLIGHT(body, 40, '', '', 0, 3). The default (1, or the count omitted) keeps the classic single-string best passage.
Whole field (ES number_of_fragments: 0): a fragment count of 0 returns the entire text as one string with every match marked — HIGHLIGHT(body, 150, '', '', 0, 0).
Boundary scanner (ES boundary_scanner): a trailing 'word' widens each cut to the nearest whitespace so no word is split, 'sentence' to the enclosing sentence (., !, ?, newline); the default 'chars' cuts at the character budget — HIGHLIGHT(body, 40, 'sentence'), HIGHLIGHT(body, 40, '', '', 0, 3, 'word').
Highlight query (ES highlight_query): a MATCH() / SEARCH() predicate among the arguments marks that query's terms instead of the statement's — HIGHLIGHT(body, MATCH(body, 'lazy dogs')), in any position beside the positional options.

The ES type option (plain, unified, fvh) is accepted and irrelevant: snippets always re-read the live row text and re-analyze it, so no highlighter needs stored term vectors. Every option travels with the sharded sorted-scan scatter too (shards highlight with the full request, not just the fragment size).

Aggregations

Search queries combine with GROUP BY and aggregates like any other SQL, and GROUP BY g TOP k BY score() returns each group's k best-scoring rows instead of aggregates — the SQL spelling of ES top_hits (per-group top documents), with HIGHLIGHT() available in the projection:

SELECT region, title, score() FROM products
WHERE MATCH(title, 'widget') GROUP BY region TOP 3 BY score();

SELECT region, COUNT(*), SUM(units), AVG(price) FROM sales
WHERE MATCH(product, 'widget') GROUP BY region;

SELECT COUNT(*), MAX(price) FROM sales
WHERE SEARCH('+widget -clearance');   -- one global row

Two serving paths produce identical results:

Fast-field pushdown (no row materialization): global aggregates (COUNT(*)/COUNT(col)/COUNT(DISTINCT col)/SUM/AVG/MIN/MAX over declared columns, no GROUP BY), grouped counts over a time_bucket(step, col) date histogram, and keyword-grouped metric aggregations — GROUP BY a declared keyword column or a text column with a .keyword twin (the twin's raw string is exactly the value the row path groups by), with any mix of COUNT/COUNT(col)/SUM/AVG/ MIN/MAX over declared numeric columns. Grouped metrics run as a manual fold over fast-field columns (matching doc set → per-segment ord-indexed accumulators), not tantivy's aggregation module — whose 0.26.1 sub-aggregation data-loss bug (small buckets lose metric input on periodic flushes while doc counts stay exact) keeps date-histogram metrics on the row fallback until fixed upstream. A keyword-grouped COUNT(DISTINCT) on a single index (a sole member, or a full-copy cluster) also takes the row fallback; on a sharded cluster it is served by the same manual fold carrying one exact term set per group across the shard boundary (below). APPROX_PERCENTILE(col, p) pushes down as a t-digest sketch (global aggregates only; the exact PERCENTILE never pushes down). COUNT(DISTINCT) is exact — a terms-bucket count, never an HLL (the opt-in APPROX_COUNT_DISTINCT() is the HLL: it pushes down as a cardinality sketch and never bails on wide term sets — on a single index; the sharded scatter serves both distinct forms from exact term sets instead, see below).
Hit-set count (no row materialization either): a plain COUNT(*) whose row-side residual is a conjunction the table's secondary indexes consume in full — col = literal and ranges on CREATE INDEXed columns, the shapes an index-only filtered count takes — is answered as |search hits ∩ residual index keys|: the search index's hit set is one more key-set source for the index-only intersection count, trusted like any index. So WHERE MATCH(body, ?) AND folder = ? counts without a row read when folder is on a secondary index, even though it is not on the search index. Trust follows the index-only counts: pending index writes commit first, a pushed range over a multi-valued row declines the hit set, and a TTL table, an open transaction or an AS OF read decline (each key set is ≤ 1M keys). EXPLAIN shows search count: hit set of '<search index>' ∩ index keys of '<index>'. The residual that plans the index keys may include the predicates already pushed into the search (they hold either way), so WHERE account = ? AND labels = ? AND MATCH(...) uses an (account, labels[]) index even though only labels stayed row-side. A negated text predicate — … AND NOT MATCH(body, 'amazon') — counts as the residual's index keys minus those that match the term, never materializing the near-whole-table hit set of "not amazon"; every counted row carries the pushed values, so it is one the index can speak for. On a cluster this is the coordinator's wire-free local path for a table it holds in full (RF ≥ members, or a pin); a sharded table keeps the row gather.
Row fallback: everything else — date-histogram metrics, grouped distincts, residual predicates the index cannot take (see Residual pushdown under per-column options; a residual on a declared typed column is pushed into the index and keeps the aggregate on the fast-field path, and a residual on a secondary-indexed column keeps a plain count on the hit-set count above), HAVING, ORDER BY — gathers the matching rows (deduped by key at the coordinator, so correct at any replication factor) and runs the ordinary grouped executor. The gather is bounded by the scan budget (past it, the error names the fix: declare the group column a keyword fast field), and a GROUP BY on a column not on the index at all fails fast instead of gathering — it can never be answered index-side, and on a large match set the silent gather tied a production coordinator up for the full statement timeout. Group without a search predicate to aggregate off-index columns row-side.

SQL semantics hold on both paths: rows missing the group column form the NULL group, SUM over no values is NULL (not 0), and metric types follow the column declarations (SUM of a long is an integer). The pushdown is exact or declined — on a truncated bucket list or any count mismatch (e.g. a date histogram that would lose rows missing the date column) it silently falls back rather than approximate. Cluster mode pushes down when one index holds every row (single node, or RF ≥ member count). Sharded corpora (RF < members) scatter partials: every document carries its placement hash in a _ring fast field, each member aggregates only the hash arcs it primarily owns (the arcs tile the key-space, so every key counts exactly once regardless of replication), and the coordinator merges. Exact-or-decline throughout: the scatter runs only for losslessly mergeable metrics (COUNT(*)/COUNT(col)/SUM/ MIN/MAX, globally and per keyword bucket — grouped partials merge by bucket key), requires a stable membership epoch across the whole gather and every member answering — a silent peer, an epoch change, or a membership change in flight (dual-ring placement) falls back to the deduped row gather. AVG scatters as SUM+COUNT pairs and the coordinator divides after the merge. Ungrouped distinct counts scatter too: per-shard final answers cannot merge (a value present on several shards would double-count), so each member ships its mergeable intermediate term-set state and the coordinator merges those — COUNT(DISTINCT) stays exact across shards, and APPROX_COUNT_DISTINCT rides the same term sets (rewritten for the scatter like AVG), so on a sharded corpus it is exact within the same 65,536-distinct-terms bound and falls back to the row gather beyond it. Keyword-grouped distinct counts scatter the same way: each member runs the manual fast-field fold over its primary-owned key-space and ships one exact term set per group (with the group's count/sum/min/max alongside — never tantivy's bucketed sub-aggregations, which the upstream bug above corrupts); the coordinator unions the sets by bucket key, so a value that lives on several shards under one group counts once and no group's set reaches another's. The same 65,536 bound applies per group and to the group count; a grouped APPROX_PERCENTILE, a date-histogram group, or a distinct over a plain text column declines to the row gather. An ORDER BY on a grouped search select keeps the row path. Sorted top-k (ORDER BY <fast column> LIMIT k) scatters the same way — each member resolves its primary-owned top k (highlights included) and the coordinator k-way merges; a residual SQL filter declines the scatter (filters don't travel). Per-hit explain routes to a replica of the key (ring order), so both "explain": true and the SQL spelling — EXPLAIN SCORE SELECT ... WHERE MATCH(...) FOR <pk> — work at any RF. One typing nuance: time_bucket pushdown keys are timestamps (a date column's semantics), while the fallback preserves each row's stored type — store timestamp values (not bare integers) in date columns for consistent typing.

Hybrid search (`RANK BY RRF`)

Fuse a full-text leg and a vector leg in one query by Reciprocal Rank Fusion — the SQL analogue of Elasticsearch's rrf retriever. The NEAREST clause is the vector leg, the WHERE search predicate is the text leg, and RANK BY RRF fuses them:

SELECT id, rrf_score() FROM docs
NEAREST (embedding, [1.0, 0.0, 0.0], 100)   -- vector leg (100 candidates)
WHERE MATCH(body, 'quick brown fox')         -- text leg
RANK BY RRF                                  -- default constant 60
LIMIT 10;

Each leg fetches the NEAREST k candidates; a hit at 1-based rank r in a leg contributes 1 / (c + r) to its fused score (rrf_score()), so a doc that ranks well in both legs beats one strong in only a single leg. Fusion is rank-based, so BM25 scores and vector distances need no normalization. RANK BY RRF (c) overrides the constant. The residual (non-search) part of WHERE filters both legs. Cluster-wide: each leg scatter-gathers to a coordinator-merged ranked list, then the coordinator fuses. Requires at least two legs: a NEAREST clause plus a search predicate, or several NEAREST clauses — each over its own vector column (dense, MULTI or SPARSE), a table's named vectors — with the text leg then optional. RERANK still needs the text leg for its query. No JOIN/UNION/DISTINCT/GROUP BY/ORDER BY (ordering is rrf_score() desc).

Reranking (`RERANK`)

Second-stage relevance — the SQL analogue of Elasticsearch's text_similarity_reranker retriever. The top candidates of a search (or hybrid) retrieval are re-scored by an external cross-encoder model and the page is served in the reranker's order:

SELECT id, title, score() FROM docs
WHERE MATCH(body, 'how do I tune compaction')
RERANK TOP 100      -- send the top 100 BM25 hits to the rerank endpoint
LIMIT 10;

Needs [inference] rerank_url or an active MODEL PROVIDER … TYPE 'rerank' (a Cohere/Jina/TEI-compatible endpoint — wire contract and config in VECTOR.md). Full clause syntax, defaults, and constraints: QUERY_SYNTAX.md. Key properties:

Opt-in per query — a down rerank endpoint fails only RERANK queries, never writes or ordinary searches.
Coordinator-side, cluster-wide — the reorder happens over the already scatter-gathered candidate rows; one rerank HTTP call per query, bounded by TOP (default 100, cap 1000) and 4000 chars per document.
score() reads the rerank score; on a hybrid query rrf_score() still reads the fusion score.
Composes with RANK BY RRF (reranks the fused list) and HIGHLIGHT.

Deep pagination (`AFTER` / `search_after`)

Stable keyset pagination beyond OFFSET — SQL AFTER (<last sort value>, <last pk>) on a search query ordered by score() DESC or a single column (full syntax and rules: QUERY_SYNTAX.md). Every sorted search page tie-breaks by primary key ascending, so pages and cursors always agree, ties included. ES clients use the standard workflow: sort by [<key>, {"_id": "asc"}], then echo the previous page's last hit sort array as search_after — each sorted hit carries its sort values, with JSON float types round-tripping exactly. On a plain (non-search) query search_after becomes a keyset predicate instead — pk > last, or (key, pk) > (last_key, last_pk) for a sort key plus the _id tie-break — so filter-only queries page the same way.

Point in time and scroll. POST /{index}/_pit returns an id that pins the index and the current instant; POST /_search with {"pit": {"id": …}, "search_after": […]} then reads every page AS OF that instant (SQL: QUERY_SYNTAX.md): rows written after the PIT was opened never appear, and rows updated or deleted afterwards drop out (their old version is not resurrected). Nothing is held server-side — the id is the state, so it works from any coordinator, keep_alive is accepted and irrelevant, and DELETE /_pit always succeeds. POST /{index}/_search?scroll=1m starts a cursor the same way and returns _scroll_id; POST /_search/scroll with {"scroll_id": …} returns the next page and a new id, an empty page once the cursor is exhausted; DELETE /_search/scroll frees it. A scroll pages in primary-key order unless the request sorts explicitly (the ES _doc analogue — the only order that stays stable while the index changes; an explicit _score sort is honoured, but BM25 scores move with every index write, so its page boundaries can skip or repeat rows under concurrent writes). Aggregations ride the first scroll page only. Search AFTER depth stays capped at 65,536 ranked hits; primary-key-ordered pages are unbounded.

Not offered, deliberately: learned sparse retrieval (ELSER-style sparse_vector inference) — skaidb runs no embedded ML; use an external embedder with a vector index — and binary / BBQ vector quantization tiers, a storage-format project of its own (the vector index is float32 HNSW).

ES-compatible REST subset

The REST endpoint speaks enough Elasticsearch for existing ES client libraries and log shippers (not Kibana). An ES "index" is a skaidb table; its SEARCH INDEX is the mapping; _id maps to the table's single-column primary key (stored as a string, auto-generated when a bulk action omits it). Pre-create the table + search index for full control — or let _bulk auto-create an unknown index ES-style: primary key id plus a dynamic mapping from the first document (strings → text, integers → long, floats → double, bools → bool; null/array/object fields are stored but not indexed).

POST /{index}/_bulk      index / create / delete NDJSON actions
                         (auto-creates an unknown index, see above)
POST /{index}/_pit       open a point in time → {"id"}; DELETE /_pit closes
POST /_search            {"pit": {"id"}, …} — the index and read instant
                         come from the id; hits page with search_after
POST /{index}/_search?scroll=1m   start a scroll (→ _scroll_id);
POST /_search/scroll     {"scroll_id"} next page; DELETE /_search/scroll
POST /{index}/_search    query DSL: match, match_phrase, prefix, wildcard,
                         regexp, fuzzy, term, terms, range, exists, bool
                         (must/filter/must_not/should — should beside
                         must/filter boosts scores via BOOSTED(), is
                         required with minimum_should_match: 1, and
                         minimum_should_match: n ≥ 2 runs as
                         AT_LEAST(n, …)), query_string, more_like_this,
                         multi_match (best_fields / most_fields /
                         cross_fields; per-field `^boost` weights on
                         best_fields / most_fields wrap each field's match
                         in BOOST() and OR the fields, so scores add —
                         cross_fields takes no per-field boosts),
                         geo_distance / geo_bounding_box / geo_polygon /
                         geo_shape (polygon, envelope, circle, point on
                         a point field; relation intersects | within |
                         disjoint) → the SQL geo predicates (GEO.md; a
                         geo index prunes transparently) — distances take ES unit
                         suffixes ("5km", "1mi", …; bare number =
                         metres), points are {lat, lon} objects,
                         [lon, lat] GeoJSON arrays, "lat,lon" strings,
                         or WKT POINT (geohashes unsupported), boxes
                         take corner pairs or flat top/left/bottom/
                         right edges;
                         "explain": true per-hit BM25 breakdowns; from/size,
                         multi-key sort (incl. _score; sorted hits carry
                         their `sort` values), search_after deep paging
                         (sort [<key>, {"_id": "asc"}] + the previous
                         page's last hit `sort` array; not with from > 0
                         or knn/retriever; a plain query pages by keyset
                         predicate), pit / scroll (see above),
                         _source with
                         include/exclude lists (trailing-* globs),
                         highlight (incl. number_of_fragments > 1 →
                         fragment arrays), exact totals; aggs: terms,
                         date_histogram (+ sum/avg/min/max/value_count/
                         cardinality/percentiles sub-aggs and top_hits —
                         top_hits runs one query per retained bucket,
                         relevance-ordered or by its own `sort`), bare
                         metrics incl.
                         percentiles (exact, linear-interpolated;
                         percents default to ES's; with a `tdigest`
                         option the APPROX_PERCENTILE sketch pushdown),
                         composite
                         (multi-source terms/date_histogram buckets,
                         ascending keys, after/after_key pagination;
                         metric sub-aggs and top_hits), significant_terms
                         (foreground = the query, background = the whole
                         index, JLH score, size / min_doc_count), parent
                         pipelines under any multi-bucket agg
                         (cumulative_sum, derivative, bucket_script,
                         bucket_selector — scripts are the arithmetic /
                         comparison subset over params — bucket_sort
                         with from/size) and sibling pipelines
                         (avg_bucket, sum_bucket, min_bucket,
                         max_bucket, stats_bucket over `agg>metric` or
                         `agg>_count`, gap_policy skip / insert_zeros);
                         geo aggs: geo_distance (origin + ranges, unit,
                         metric sub-aggs), geo_bounds, geo_centroid,
                         geohash_grid (precision, size);
                         vector retrieval: a top-level knn block
                         {field, query_vector | query_vector_builder,
                         k, filter} → NEAREST (a query_vector_builder
                         text searches a managed EMBED index, auto-
                         embedded); a retriever {rrf {retrievers:
                         [standard, knn]}} block → NEAREST + WHERE-search
                         RANK BY RRF (rank_constant → the RRF constant);
                         a retriever {text_similarity_reranker
                         {retriever: standard | knn | rrf, field,
                         inference_id, inference_text,
                         rank_window_size}} block → RERANK (field → ON,
                         inference_id → WITH, inference_text → QUERY,
                         rank_window_size → TOP, default 10, up to
                         10,000 in batches of 1,000; timeout_ms → TIMEOUT;
                         hit _score is the rerank score);
                         {"query": {"percolate": {"field", "document" |
                         "documents"}}} — the index is a table of stored
                         queries (query DSL in `field`, string or object),
                         the hits are the stored queries the document
                         matches (full-text parts through the table's
                         search-index analyzers on a one-document memory
                         index, the rest on the document; several
                         documents → `_percolator_document_slot`)
                         a top-level suggest block: term suggesters
                         ({name: {text, term: {field, size}}}) run
                         SUGGEST against the index's search index and
                         answer per input token with options (text,
                         score = 1/(1+edit distance), freq); phrase and
                         completion suggesters are not offered
POST /{index}/_count     exact match count
GET  /{index}/_doc/{id}  fetch one document by _id
GET  /{index}/_mget      {"ids": [...]} — or POST /_mget {"docs": [{"_index",
                         "_id"}]} — each answered like _doc, in order
PUT  /_index_template/{name}   {"index_patterns": ["logs_*"], "priority",
                         "template": {"mappings": {"properties": …}}} —
                         stored in the `es_templates` table; when `_bulk`
                         auto-creates an index whose name matches, the
                         highest-priority template's properties declare
                         the search index (text [+ fields.keyword, +
                         analyzer], keyword, integer/long, float/double,
                         boolean, date; other types stored, not indexed)
                         and the document's remaining fields map
                         dynamically; GET /_index_template[/{name}],
                         DELETE /_index_template/{name}
GET  /{index}/_mapping   the search-index declaration as ES properties,
                         vector indexes as dense_vector {dims, similarity}

Everything translates to the same SQL statements documented above and runs through the ordinary session path — HTTP Basic auth, RBAC, cluster routing, and all pushdowns apply unchanged. Limits: bool.should beside a must/filter with no search clause cannot be scored (set minimum_should_match: 1 to make the shoulds required); a similarity in mappings or settings is not honored — scoring is BM25 with tantivy's fixed k1 = 1.2, b = 0.75 (per-field .boost and query boosts are the tuning knobs); clients that hard-check the X-elastic-product header need that check disabled. cardinality is skaidb's exact COUNT(DISTINCT), not an HLL approximation. For knn/retriever queries num_candidates is ignored (HNSW breadth is an index property — tune it with ALTER VECTOR INDEX … SET (ef = n)), the hit _score is the fused rrf_score() for a retriever or a 1/(1+distance) similarity for a plain knn, and the total is the number of ranked hits (≤ k), not a full match count.

Architecture

Why Tantivy (decision record): Lucene is a JVM library — embedding a JVM contradicts the single static Rust binary, and out-of-process Lucene is just running Elasticsearch. Tantivy is Lucene's architecture re-done in Rust (MIT, a plain dependency, forbid(unsafe) on our crates unaffected): immutable segments, skip-list postings, positional indexes, FST term dictionaries, columnar fast fields, BM25 — and public benchmarks have it matching or beating Lucene per core. A native engine stayed on the table (skaidb built its own TSDB and HNSW), but FTS parity is 10–20× the surface of either; hence the thin crate boundary below, which keeps a native replacement possible without touching engine/SQL.
skaidb-fts crate wraps Tantivy behind an engine-agnostic API (skaidb Documents in, (key, score) hits out); no Tantivy types cross the crate boundary, so the engine and SQL layers stay independent of the search core.
Derived data over the LSM table, like the vector indexes: the index registers in the catalog (schema-version prefix s:<name>, replicated like other DDL), and every put/delete maintains it alongside secondary and vector indexes. The table remains the source of truth — a lost, stale, or mis-configured index rebuilds from the table.
Durability — the row WAL is the translog. The write path queues index operations on the index (it never touches the Tantivy writer, so neither a commit in progress nor a busy indexing thread can extend the engine lock); the refresher and the cluster's maintenance applier apply them in order and commit lazily (on the refresh_ms cadence). Each commit atomically persists the max row HLC it contains (the watermark) as the Tantivy commit payload. On open, the engine replays table rows (and tombstones) newer than the watermark into the index — so a crash, or a clean shutdown with uncommitted index writes, loses nothing. There is deliberately no commit-on-shutdown: the replay path runs on every open, keeping recovery constantly exercised.
Storage layout: Tantivy segments live under <data_dir>/fts/<index>/, mmap'd for search (evictable pages — reads cost no memory budget). The writer heap is bounded per index: 64 MB by default, or memory_target/8 clamped to [16 MB, 64 MB] when a budget is set (peak RSS during a bulk build ≈ 1.5× the heap).
Bulk ingest: a multi-row statement (and a replicated batch, including the async replication frames) feeds every search index in one pass with a single NRT refresh check at the end — an index commit never fires mid-batch. Dev-box reference (100 k-row synthetic corpus, skaidb-engine/examples/fts_bench): ≈ 126 k rows/s ingest with the index live (batched) vs ≈ 81 k rows/s per-row; ranked top-10 ≈ 1.2 ms p50.
Query pushdown: ORDER BY score() DESC LIMIT k retrieves top-k directly from the index (early-terminated, no scan). Residual WHERE conditions are applied after the authoritative row re-read, with over-fetch to keep k results (the vector-search discipline). A MATCH used as a plain predicate (no ranking) retrieves the matching key set from the index — and that key set is also a planner input: a count intersects it with secondary-index key ranges, and an ordered page uses it to gate a sorted secondary-index walk (see Aggregations and Limits).
Cluster (the vector-search pattern): DDL broadcasts, and every member indexes its shard locally from replicated writes — the replicated apply paths (apply_put/apply_delete, batched) maintain search indexes, so replication, rebalance, drain, hinted replay, and anti-entropy repair all keep the index in step with the table for free. A query scatters to all members; each answers with its local (key, score) top-k after committing pending index writes (writes replicate synchronously at the write consistency, so every acked write is searchable cluster-wide, not just NRT). The coordinator merges by score (keeping a replicated row's best per-shard score), re-reads survivors at read consistency, applies the residual filter, and generates highlight snippets from its own index. A member that answers "busy" (its engine write-locked at that instant) is retried. Up to RF−1 members that still cannot answer are skipped, since their rows surface through reachable replicas. Past that (any missing member at RF=1, or a missing pin of a pinned table) the query fails with a retryable search incomplete error rather than returning a short answer. Scoring uses per-shard BM25 statistics by default (the standard distributed-index default: each shard's IDF and length normalization come from its own documents, so ranks can drift from a single index's when a term's frequency is skewed across shards). cluster.global_bm25 = true (live-mutable, set on the coordinators) switches to global statistics: the coordinator first gathers every member's document count, field token totals and per-term document frequencies for the query and merges them, then every shard scores with the merged figures — ranks and scores then equal a single index over the whole corpus (Elasticsearch's dfs_query_then_fetch), for one extra scatter round per search (skaidb_cluster_search_global_stats_rounds). Post-resharding Reclaim leaves stale postings for moved-away keys — harmless (the authoritative re-read resolves them) and reclaimed by REBUILD SEARCH INDEX.

Observability

SHOW INDEXES lists search indexes with their analyzer and columns.
SHOW STATUS rows: search_indexes, search_docs, search_rebuild_ms, and per-index search.<name>.{docs,disk_bytes,uncommitted}.
/metrics gauges: skaidb_search_indexes, skaidb_search_docs_total, skaidb_search_disk_bytes, skaidb_search_rebuild_seconds.

Limits

Search predicates compose with AND/OR/NOT among themselves; mixing them with ordinary conditions under OR/NOT is rejected (top-level AND with ordinary conditions works — they filter the hits).
ORDER BY score() orders descending only (and requires LIMIT). Column orderings work: declared fast-field columns with LIMIT retrieve index-ordered top-k. An ordinary AND condition the index cannot evaluate is applied after the sorted window, and the window widens until LIMIT rows pass it or the index runs dry (past 65,536 sorted hits the pushdown declines to the exact gather-and-sort) — so MATCH(…) AND internal_date <= <last month> ORDER BY internal_date DESC LIMIT 5 answers the newest five inside the range, never an empty page because the newest hits fell outside it. The pushdown also declines when matching rows lack the sort column (SQL NULL placement differs from the index's). A sort column that is not a fast field but carries a secondary index goes through the ordinary index planner with the search hit set as the membership gate: an index entry outside the hit set is skipped before its row is read, every row read re-checks the full WHERE, and a sorted walk stops at LIMIT (plus the rows tied with the boundary, so ties break by key exactly as the gather-and-sort does) — EXPLAIN shows hit-set walk of the sorted index '<name>'. When the planner's probe finds a residual's bounded index range more selective than the walk (its guard against a residual matching nothing), only the hits inside that range are read and sorted — EXPLAIN shows hit-set scan of the bounded index range on '<name>'. On a cluster both serve a table the coordinator holds in full; a sharded table gathers and sorts. Anything else gathers and sorts through the ordinary executor.
No JOIN, UNION, DISTINCT in the same query. A search predicate combines with NEAREST (vector) only through hybrid RANK BY RRF (see below), not as a boolean sibling.
Per-shard BM25 statistics by default (the standard distributed-index default); cluster.global_bm25 = true scores with merged cluster-wide statistics instead, for one extra scatter round per search (see Architecture, Cluster). Scoring targets result-set stability, not identical score floats — BM25 constants and length normalization differ subtly between engines.