Substring search over parts tool bodies - ngram on a materialized Utf8 column

[tenequm]

Why

Agents querying pond_sql_query need to find arbitrary substrings (error messages, identifiers like toolu_01X..., shell commands, file paths) inside tool-call/tool-result bodies, which live in parts.variant_data (binary JSONB). No search surface covers them: pond_search and fts() index only conversational messages.search_text. The fallback is a JSON-stringify + LIKE '%...%' full scan against the query timeout (measured: repeated >30s timeouts).

Status

• Lance v8 bump: done (v0.12.2, 2026-07-07) - made scalar substring indexes (FM-Index, ngram) available.
• Structured slice: done (v0.13.0 / feat(parts)!: materialize tool columns + in-place schema migration; per-query SQL timeout #100) - tool_name/call_id/is_failure materialized as indexed columns; "which tool / call-id / failed" queries hit narrow indexes (remote tool GROUP BY: >30s -> ~9s). Query timeout is now tunable (timeout_seconds on pond_sql_query, --timeout on pond sql, max 600s).
• Substring search: still open - this issue.

Measured decision (full data in the comment below)

Benchmarked FM-Index vs ngram vs FTS on the full real corpus (Lance 8.0.0, benches/fmindex_probe.rs). The original "FM-Index directly on variant_data" plan is refuted; ngram on a narrow materialized Utf8 column is the fit.

• FM directly on variant_data is not queryable as planned: contains(variant_data, 'x') won't plan (variant_data is LargeBinary; DataFusion contains needs String), and ngram won't build on a binary column - a Utf8 column is mandatory (the earlier "no extracted column, no schema change" assumption was wrong).
• FM is too heavy: 2.66 GB on-disk (~0.94x of raw), ~3 GB resident + ~9 s per query (whole-index heap prewarm on every load), vs ngram 480 MiB / ~0.6 GB / 0.2-0.4 s for identical exact results.
• FTS doesn't fit: token/word search, not substring (127k false hits for a phrase; 0 for a mid-token needle), and not smaller than ngram on tool-body text.

Plan

1. Materialize a narrow searchable Utf8 column from the tool-body text worth searching (command text + tool-result payload, not the whole blob) - same pattern as the feat(parts)!: materialize tool columns + in-place schema migration; per-query SQL timeout #100 tool columns.
2. Build an ngram scalar index on it; expose contains(<col>, 'needle') in pond_sql_query (index-accelerated, exact after verify; case-insensitive / ASCII-folded).
3. Document it in the tool description + schema://pond-sql; repoint the src/sql.rs timeout / parts-scan TODOs (which still reference this issue) at the new operator.

Interim

Until this lands, src/sql.rs carries TODOs referencing this issue at the timeout hint and the parts-scan guidance, so the gap and the plan stay discoverable.

[tenequm]

Partially overtaken - narrowing to the FM-Index half

The "bump Lance to v8" half is done: v0.12.2 upgraded lance 7 -> 8 (2026-07-07).

Separately, v0.13.0 / #100 materialized tool_name, call_id, and is_failure as real nullable columns on parts, with a BTree scalar index on tool_name. That answers the structured slice of this issue's motivation - "which tool, what call-id, did it fail" queries now hit narrow indexed columns instead of scanning variant_data (measured: remote tool GROUP BY moved from a hard >30s timeout to ~9s).

It does not answer the core ask here: arbitrary exact-substring search inside variant_data (error strings, toolu_... ids, shell commands, file paths). That still has no index and still falls back to a JSON-stringify + LIKE '%...%' full scan against the query timeout (now tunable - timeout_seconds on pond_sql_query, --timeout on pond sql, max 600s, added in #100).

Remaining work for this issue:

• Build an FM-Index directly on variant_data.
• Expose contains(variant_data, 'needle') in pond_sql_query (case-sensitive by design).
• Repoint the timeout / parts-scan hints in src/sql.rs (which still carry TODOs referencing this issue) at the new operator.

Retitling to drop the v8-bump prerequisite, which is now satisfied.

[tenequm]

Measured on the real corpus - the direct-on-variant_data plan doesn't hold; ngram is the fit

Built and benchmarked all three candidate indexes on a full local copy of the production parts table (Lance 8.0.0, harness benches/fmindex_probe.rs). Corpus: variant_data = 1,530,769 non-null rows, 2,793.8 MiB of raw text (decoded to a 1.2 GB Utf8 column on disk). This changes the plan.

Two blockers kill "FM directly on variant_data, no extracted column"

1. contains(variant_data, 'x') does not plan. variant_data is LargeBinary (JSONB) and DataFusion's contains requires String, refuses to coerce: contains(LargeBinary, Utf8) ... add explicit type casts. A CAST(... AS VARCHAR) makes it plan but defeats index pushdown (full scan).
2. ngram refuses to build on LargeBinary ("can only be created on a Utf8 or LargeUtf8 field"). FM builds on the binary column but, per (1), can't be queried through contains.

So a Utf8 column is mandatory for any of these to be queryable. The issue body's original "works on variant_data directly - no extracted column, no interim index, no schema change" premise is refuted. Whatever we build needs a materialized searchable Utf8 column.

Three-way comparison (same column, identical needles)

Index	On-disk	Build	Query latency (cold)	Query RSS	Model	"rustup override set" hits
ngram	480 MiB	25 s	0.2-0.4 s	~0.6-0.7 GB	substring (case-fold, verify)	3 (exact)
FTS (simple+stem)	464 MiB	105 s	0.3 s	~0.6-0.8 GB	token/word (BM25)	127,448
FM-Index	2,662 MiB	148 s	8.7-9.0 s	~3.0 GB	exact raw-byte substring	3 (exact)

ngram and FM return identical exact results; ngram is 5.5x smaller, ~25x faster, ~5x lighter in RAM.

FM-Index is far heavier than assumed

• On-disk 2.66 GB (~0.94x of the raw text) - roughly doubles the parts footprint; it does not compress away.
• ~3.0 GB resident and ~9 s per cold query. Source (lance-index/src/scalar/fmindex.rs): search() calls prewarm() -> wavelet.load_all(), loading the entire index into heap (not mmap, not shared across processes) on every index load, across all partitions - so num_segments lowers build RAM but not query RAM. On pond mcp (long-lived) that's ~3 GB held resident after the first contains, plus a ~2.6 GB cold download on a remote store.

FTS (simple/other tokenizer) does not fit substring search

• Not smaller here - 464 MiB ~= ngram's 480 MiB. The "~4x lighter than ngram" that holds for natural-language search_text evaporates on tool bodies (commands/JSON/paths/hashes = huge token vocabulary -> bloated dictionary).
• Wrong query model. Token/bag-of-words, not substring: "rustup override set" -> 127,448 hits (any doc with set/override/rustup) vs the exact 3; a mid-token needle "verride" -> 0 hits (FTS can't match inside a token). A phrase query (with_position) tightens the first case but adds index weight and still can't do mid-token substrings, symbols (--flag), or exact case. This is exactly why pond already keeps "substring/symbol lookup on the SQL LIKE/contains_tokens path, not FTS" (src/substrate.rs InvertedFtsWord).

Revised plan

1. Drop the whole-variant_data FM-Index approach. Too heavy (2.6 GB disk + 3 GB resident + 9 s/query) for the payoff.
2. Materialize a narrow searchable Utf8 column from just the tool-body text worth searching (command text + tool-result payload, not the entire blob) - the same "materialize a narrow column" pattern as tool_name/call_id in feat(parts)!: materialize tool columns + in-place schema migration; per-query SQL timeout #100. This shrinks n and unblocks the query path.
3. Put an ngram index on it, expose contains(<col>, 'needle') in pond_sql_query (index-accelerated, exact after verify). Note: ngram is case-insensitive / ASCII-folded (behavior change from the old case-sensitive FM plan - generally desirable for command/log search).
4. Repoint the src/sql.rs timeout / parts-scan TODOs at the new operator.

Numbers reproducible via benches/fmindex_probe.rs (prep -> build fm|ngram|fts -> query|ftsq).