You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Full-text and hybrid retrieval: measure what ArcadeDB has, then close the gaps (SQL, Cypher, PostgreSQL wire) #344
ArcadeDB has full-text search with BM25 ranking, and agent memory needs good keyword search and hybrid keyword plus vector retrieval. We have read the code but not measured it, and a few gaps are known from reading. The aim: measure what the release has (26.11.1, engine a3a5d12457), then close the gaps that matter, and make the same search work from SQL, Cypher and the PostgreSQL wire.
What exists today (read from the code, not run)
FULL_TEXT index with BM25 by default (k1 1.2, b 0.75), Lucene analyzers over the engine's own LSM posting store.
SQL: SEARCH_INDEX('idx','query'), SEARCH_FIELDS, CONTAINSTEXT, $score for ordering; vector.fuse (RRF, DBSF, LINEAR) for hybrid; HTTP, gRPC and MCP hybrid_search with vector, full-text and graph-expansion legs.
Cypher: CALL db.index.fulltext.queryNodes (Neo4j style, ranked by BM25). The Cypher 25 SEARCH clause exists only for vector indexes.
Known gaps (from reading; to be confirmed by the measurements)
No term positions: a phrase query is a bag of terms, so phrase order is not checked.
The full-text scorer has no top-k pruning (it reads every posting); the learned-sparse index already has block-max MaxScore.
No stemming or language setting by default.
hybrid_search has no recency leg (matters for memory and for issues and events).
Cypher 25 SEARCH has no full-text form.
Plan
Measure (step 0): relevance (nDCG@10 on BEIR) and latency (p50, p99 at 10k, 50k and the full corpus) against Lucene, SQLite FTS5 and Tantivy; freshness after commit, churn and memory; hybrid on a joint corpus; and a dialect check: does the same text query give the same answers through SQL, Cypher and the PostgreSQL wire? A smoke on a 300-document corpus passes; the real run is queued.
Post the numbers here (generated by script from the result files). Nothing is raised against the engine repository before they exist; afterwards we decide with the maintainers which confirmed gaps become engine issues, each with a Java repro and one question.
Fix with the maintainers' direction; extend the PostgreSQL-flavored language work (tsvector, @@, ts_rank onto the same index) once that language exists.
The control plane (tae898/control-plane) will use this as its own text filter and as a live corpus (issues, queue comments, decisions, inbox text).
Related: #306 (agent-memory workloads in the bindings), #315.
This is the step 0 result for this plan: what release 26.11.1 (engine a3a5d12457) does today, measured. I have not filed an engine issue from these results yet. A script printed every figure from the result files.
What was measured
I ran it on my laptop (Intel Core Ultra X9 388H, cores 8-11, which are efficiency cores, turbo not disabled). Latency is untimed and descriptive: one run per cell, 2 warm-up and 5 measured passes, a single thread, k 10, JDK 25 only, so the milliseconds hold for this machine only. Freshness, churn, and hybrid ran on JDK 21 and JDK 25. I wrote rules R1 to R12 before any run, and every rule with a verdict passes. The corpora are the BEIR test splits of SciFact (5,183 documents, 300 queries), NFCorpus (3,633 documents, 323 queries), and FiQA (57,638 documents, 648 queries). The baselines are Lucene 10.5.2, SQLite FTS5 3.50.4, and Tantivy 0.27.0.
Amendment: my first scoring of stage a failed R1 on NFCorpus, because my Python scorer skipped queries with an empty answer (308 of 323 queries evaluated for en, 298 of 323 for std and stop). I fixed score.py only and re-scored the same run files. R1 now passes on 18 Java-written runs, and all figures here use the fixed scoring.
Relevance
ArcadeDB's SEARCH_INDEX ordered by $score stays within 0.0044 nDCG@10 of Lucene BM25 with the same analyzer in all 9 cells (bound 0.02), and the forms with and without ORDER BY $score DESC return the same top-100 order on all 3,813 query runs. I set the analyzer with CREATE INDEX ... FULL_TEXT METADATA {"analyzer": ...}: std is StandardAnalyzer, stop is the engine's StandardEnglishStopWordsAnalyzer, and en is Lucene's EnglishAnalyzer. FTS5 and Tantivy tokenize differently and drop no stop words, so I read no verdict from their columns.
nDCG@10, full corpus:
corpus
analyzer
ArcadeDB
Lucene
FTS5
Tantivy
SciFact
std
0.6622
0.6606
0.6683
0.6611
SciFact
stop
0.6664
0.6660
n/a
n/a
SciFact
en
0.6870
0.6826
0.6824
0.6868
NFCorpus
std
0.3066
0.3064
0.3069
0.3078
NFCorpus
stop
0.3074
0.3068
n/a
n/a
NFCorpus
en
0.3218
0.3209
0.3209
0.3222
FiQA
std
0.2377
0.2389
0.2342
0.2373
FiQA
stop
0.2394
0.2389
n/a
n/a
FiQA
en
0.2509
0.2536
0.2481
0.2512
In ArcadeDB, en adds 0.0248 (SciFact), 0.0152 (NFCorpus), and 0.0132 (FiQA) over std, and stop moves it by at most 0.0042.
Latency
FiQA only (the other corpora are too small for the 10,000 and 50,000 subsamples). p50 in ms at three sizes, p99 at the full corpus:
engine
analyzer
p50 10k
p50 50k
p50 57,638
p99 57,638
ArcadeDB
std
9.59
60.5
85.9
217.0
ArcadeDB
stop
2.82
15.3
19.0
59.5
ArcadeDB
en
3.34
20.2
24.7
70.8
Lucene
std
0.197
0.318
0.339
0.948
Lucene
en
0.165
0.282
0.304
0.828
FTS5
std
7.70
38.2
43.9
145.8
Tantivy
std
0.173
0.685
0.781
1.32
From 10,000 to 57,638 documents (5.8 times) the ArcadeDB std p50 grows 9.0 times and Lucene's grows 1.7 times. At the full corpus it is 253.1 times Lucene's on FiQA (29.9 on SciFact, 3.2 on NFCorpus), and stop lowers it by a factor of 4.5. The ArcadeDB figure is a SQL statement through db.query and the baselines use their own APIs, so the ratios are not a pure index comparison. With and without ORDER BY the p50 differs by at most 2.3 percent on FiQA. I did not count postings read, so these rows show how latency scales, not why.
Freshness, churn, memory
Freshness: 5 modes (same-thread insert, other-thread insert, uncommitted in a transaction, update, delete), 200 repetitions each, both JDKs: 0 misses and 0 isolation or staleness violations. A committed insert was found after a p50 of 331 µs (JDK 21) and 318 µs (JDK 25) on the same thread.
Churn (random inserts, updates, deletes, rollbacks, a failed transaction, reopen, and compaction; 5 seeds; both JDKs): 0 mismatches against a brute-force token oracle for the string index, the two-property index, and the LIST BY ITEM index, so the LIST BY ITEM update loss I reported in ArcadeData/arcadedb#10209 did not show up on this release (this is random churn, I did not run the minimal case of that issue here). After churn, scores equal those of a freshly built index (maximum relative difference 0).
Memory on FiQA with std: loading with the index already created took 5,579 ms (10,331 documents/s), and loading first (418 ms) and then building the index took 6,051 ms. The index is 34.12 MiB on disk (84.62 MiB with it, 50.50 MiB without). Lucene and Tantivy do not store the text and take 16.05 and 18.28 MiB.
Hybrid
This is a synthetic corpus (20,000 documents, 50 topics, 200 queries; text and vector each point to a wrong topic with probability 0.35), 3 seeds on each JDK, same figures on both. F1 is P@10 against the topic, F2 against the topic and the newest 20 percent:
system
F1
F2
text only
0.653
0.097
vector only
0.654
0.134
RRF
0.909
0.162
DBSF
0.862
0.166
LINEAR
0.696
0.131
RRF + recency leg, weight 0.3
0.907
0.165
RRF + recency leg, weight 1.0
0.843
0.181
DBSF + recency leg
0.855
0.191
LINEAR + recency leg
0.695
0.133
All 9 systems ran without error, with 10 rows and no duplicate id in all 54 (run, system) rows (R9). The fused ones are vector.fuse statements, RRF with k 60. RRF on F1 (0.909) beats the better single leg (0.654), as R10 expected. A third recency leg raises F2 from 0.162 to 0.181 (RRF, weight 1.0) and from 0.166 to 0.191 (DBSF), with non-overlapping per-run ranges, and costs F1 (0.909 to 0.843, 0.862 to 0.855). LINEAR does not move (0.131 to 0.133).
Dialects
On 50 queries per corpus (47 on NFCorpus, where 3 return no row on any surface), ArcadeDB SQL, Cypher db.index.fulltext.queryNodes, and the same SQL over the PostgreSQL wire returned the same top-10 ids in the same order with scores equal within 1e-6 (R11). Cypher over the wire matched too, except for 4 NFCorpus queries where Cypher orders equal-score ids differently from SQL.
The PostgreSQL text-search forms I sent are rejected. to_tsvector(...) @@ plainto_tsquery(...), websearch_to_tsquery, and contents @@ 'alpha' give a syntax error, and ts_rank gives Unknown function name 'ts_rank', on all 3 corpora, while LIKE and CONTAINSTEXT controls work. The Cypher procedure refuses the analyzer and eventually_consistent options by name, and the Neo4j-style index name 'Doc' is not found. I did not run Neo4j.
Known gaps
No term positions: not measured, I ran no phrase query.
No top-k pruning: latency grows with corpus size (above), but nothing I measured counts postings read, so the rows neither confirm nor refute it.
No stemming or language setting by default: 3 analyzers can be set at index creation, and en (which stems) adds 0.0132 to 0.0248 nDCG@10. I did not measure an index without an explicit analyzer, or mixed-language text.
No recency leg in hybrid_search: I did not call hybrid_search. The SQL vector.fuse accepts a third leg, with the effect above.
Cypher 25 SEARCH has no full-text form: SEARCH n IN (FULLTEXT INDEX ...) fails with a CommandParsingException at FULLTEXT (column 27) on all 3 corpora. I ran no vector SEARCH as a control.
Not measured: real embeddings, recency on real data, mixed-language and Korean text, writes during queries, compaction-time visibility, multi-thread and cold-cache latency, the HTTP hybrid_search route, and phrase queries.
Should I open the latency growth as the first engine issue, with a Java repro on the engine API that counts postings read per query for std and stop on FiQA?
Activity
tae898 commentedon Nov 11, 2026
This is the step 0 result for this plan: what release
26.11.1(enginea3a5d12457) does today, measured. I have not filed an engine issue from these results yet. A script printed every figure from the result files.What was measured
I ran it on my laptop (Intel Core Ultra X9 388H, cores 8-11, which are efficiency cores, turbo not disabled). Latency is untimed and descriptive: one run per cell, 2 warm-up and 5 measured passes, a single thread,
k10, JDK 25 only, so the milliseconds hold for this machine only. Freshness, churn, and hybrid ran on JDK 21 and JDK 25. I wrote rulesR1toR12before any run, and every rule with a verdict passes. The corpora are the BEIR test splits of SciFact (5,183 documents, 300 queries), NFCorpus (3,633 documents, 323 queries), and FiQA (57,638 documents, 648 queries). The baselines are Lucene 10.5.2, SQLite FTS5 3.50.4, and Tantivy 0.27.0.Amendment: my first scoring of stage a failed
R1on NFCorpus, because my Python scorer skipped queries with an empty answer (308 of 323 queries evaluated foren, 298 of 323 forstdandstop). I fixedscore.pyonly and re-scored the same run files.R1now passes on 18 Java-written runs, and all figures here use the fixed scoring.Relevance
ArcadeDB's
SEARCH_INDEXordered by$scorestays within 0.0044 nDCG@10 of Lucene BM25 with the same analyzer in all 9 cells (bound 0.02), and the forms with and withoutORDER BY $score DESCreturn the same top-100 order on all 3,813 query runs. I set the analyzer withCREATE INDEX ... FULL_TEXT METADATA {"analyzer": ...}:stdisStandardAnalyzer,stopis the engine'sStandardEnglishStopWordsAnalyzer, andenis Lucene'sEnglishAnalyzer. FTS5 and Tantivy tokenize differently and drop no stop words, so I read no verdict from their columns.nDCG@10, full corpus:
stdstopenstdstopenstdstopenIn ArcadeDB,
enadds 0.0248 (SciFact), 0.0152 (NFCorpus), and 0.0132 (FiQA) overstd, andstopmoves it by at most 0.0042.Latency
FiQA only (the other corpora are too small for the 10,000 and 50,000 subsamples). p50 in ms at three sizes, p99 at the full corpus:
stdstopenstdenstdstdFrom 10,000 to 57,638 documents (5.8 times) the ArcadeDB
stdp50 grows 9.0 times and Lucene's grows 1.7 times. At the full corpus it is 253.1 times Lucene's on FiQA (29.9 on SciFact, 3.2 on NFCorpus), andstoplowers it by a factor of 4.5. The ArcadeDB figure is a SQL statement throughdb.queryand the baselines use their own APIs, so the ratios are not a pure index comparison. With and withoutORDER BYthe p50 differs by at most 2.3 percent on FiQA. I did not count postings read, so these rows show how latency scales, not why.Freshness, churn, memory
Freshness: 5 modes (same-thread insert, other-thread insert, uncommitted in a transaction, update, delete), 200 repetitions each, both JDKs: 0 misses and 0 isolation or staleness violations. A committed insert was found after a p50 of 331 µs (JDK 21) and 318 µs (JDK 25) on the same thread.
Churn (random inserts, updates, deletes, rollbacks, a failed transaction, reopen, and compaction; 5 seeds; both JDKs): 0 mismatches against a brute-force token oracle for the string index, the two-property index, and the
LIST BY ITEMindex, so theLIST BY ITEMupdate loss I reported in ArcadeData/arcadedb#10209 did not show up on this release (this is random churn, I did not run the minimal case of that issue here). After churn, scores equal those of a freshly built index (maximum relative difference 0).Memory on FiQA with
std: loading with the index already created took 5,579 ms (10,331 documents/s), and loading first (418 ms) and then building the index took 6,051 ms. The index is 34.12 MiB on disk (84.62 MiB with it, 50.50 MiB without). Lucene and Tantivy do not store the text and take 16.05 and 18.28 MiB.Hybrid
This is a synthetic corpus (20,000 documents, 50 topics, 200 queries; text and vector each point to a wrong topic with probability 0.35), 3 seeds on each JDK, same figures on both. F1 is P@10 against the topic, F2 against the topic and the newest 20 percent:
All 9 systems ran without error, with 10 rows and no duplicate id in all 54 (run, system) rows (
R9). The fused ones arevector.fusestatements, RRF withk60. RRF on F1 (0.909) beats the better single leg (0.654), asR10expected. A third recency leg raises F2 from 0.162 to 0.181 (RRF, weight 1.0) and from 0.166 to 0.191 (DBSF), with non-overlapping per-run ranges, and costs F1 (0.909 to 0.843, 0.862 to 0.855). LINEAR does not move (0.131 to 0.133).Dialects
On 50 queries per corpus (47 on NFCorpus, where 3 return no row on any surface), ArcadeDB SQL, Cypher
db.index.fulltext.queryNodes, and the same SQL over the PostgreSQL wire returned the same top-10 ids in the same order with scores equal within 1e-6 (R11). Cypher over the wire matched too, except for 4 NFCorpus queries where Cypher orders equal-score ids differently from SQL.The PostgreSQL text-search forms I sent are rejected.
to_tsvector(...) @@ plainto_tsquery(...),websearch_to_tsquery, andcontents @@ 'alpha'give a syntax error, andts_rankgivesUnknown function name 'ts_rank', on all 3 corpora, whileLIKEandCONTAINSTEXTcontrols work. The Cypher procedure refuses theanalyzerandeventually_consistentoptions by name, and the Neo4j-style index name'Doc'is not found. I did not run Neo4j.Known gaps
en(which stems) adds 0.0132 to 0.0248 nDCG@10. I did not measure an index without an explicitanalyzer, or mixed-language text.hybrid_search: I did not callhybrid_search. The SQLvector.fuseaccepts a third leg, with the effect above.SEARCHhas no full-text form:SEARCH n IN (FULLTEXT INDEX ...)fails with aCommandParsingExceptionatFULLTEXT(column 27) on all 3 corpora. I ran no vectorSEARCHas a control.Not measured: real embeddings, recency on real data, mixed-language and Korean text, writes during queries, compaction-time visibility, multi-thread and cold-cache latency, the HTTP
hybrid_searchroute, and phrase queries.Should I open the latency growth as the first engine issue, with a Java repro on the engine API that counts postings read per query for
stdandstopon FiQA?