Skip to content

Full-text and hybrid retrieval: measure what ArcadeDB has, then close the gaps (SQL, Cypher, PostgreSQL wire) #344

Open

Description

@tae898

Problem

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

  1. 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.
  2. 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.
  3. 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.

Activity

  1. added
    enhancementNew feature or request
    pythonPull requests that update python code
    performanceSpeed or memory of the bindings, with a measurement
    on Nov 10, 2026
  2. added this to the 26.12.1 milestone on Nov 10, 2026
  3. tae898 commented on Nov 11, 2026

    @tae898
    CollaboratorAuthor

    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?

  4. modified the milestones: 26.12.1, 27.1.1 on Nov 21, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

enhancementNew feature or requestperformanceSpeed or memory of the bindings, with a measurementpythonPull requests that update python codevectorVector indexes and find_nearest

Type

No type

Projects

No projects

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions