Hybrid Search Benchmark for Tinybird/ClickHouse

Evaluates hybrid retrieval (lexical + semantic) over synthetic procurement tenders on Tinybird/ClickHouse, and measures how much a proper BM25 scorer adds on top of a simple boolean lexical scorer.

Five strategies are compared, each returning a top-10:

Strategy	What it is
lexical	Boolean field-weighted scorer over the text index (section 3)
lexical-bm25	BM25F with precomputed IDF + term frequency (section 3b)
semantic	Vector search with cosineDistance (section 4)
hybrid	Reciprocal Rank Fusion of lexical + semantic
hybrid-bm25	Reciprocal Rank Fusion of lexical-bm25 + semantic

Candidate pools default to 100 per branch; fusion is RRF (k=60); the final answer is the top 10. Evaluation is deterministic (no AI): relevance comes from the synthetic corpus's CPV codes, and Python computes Precision@5, Precision@10, MRR and nDCG@10.

Prerequisites

• Python 3.11–3.13 (the tinybird CLI package does not yet support 3.14)
• Tinybird Cloud workspace (developer plan or above)
• OPENAI_API_KEY — embeddings use OpenAI text-embedding-3-small at 1536 dims
• Cluster operator (SSH) access to ClickHouse for the ALTER TABLE index operations. On managed/Cloud clusters these are support-only; request them from Tinybird support (the exact statements are in Add the indexes).

ClickHouse version. The lexical pipes use hasAllTokens / hasAnyTokens / tokens(..., 'splitByNonAlpha') and the text index, which are GA in ClickHouse 26.2+. The vector text/vector_similarity indexes here target 26.3. tb build against a local image older than 26.2 will report hasAllTokens does not exist — that is expected; deploy to Cloud with tb --cloud deploy.

1. Setup

# Use a Python the tinybird CLI supports (3.11–3.13). 3.14 is not yet supported.
python3.13 -m venv .venv
source .venv/bin/activate
pip install --upgrade pip
pip install -r scripts/requirements.txt

export OPENAI_API_KEY=sk-...

2. Deploy to Tinybird

which tb            # -> path-to-your-project/.venv/bin/tb
tb --version
tb login            # pick the correct region + workspace (hybrid_search)

tb --cloud deploy --check
tb --cloud deploy

Verify:

tb --cloud datasource ls
  -> tenders_hybrid_eval
  -> corpus_token_idf
  -> corpus_field_stats
tb --cloud endpoint ls
  -> lexical_search
  -> lexical_bm25_search
  -> semantic_search

The datasources:

• tenders_hybrid_eval — one row per tender (text fields, embedding Array(Float32), nullable submission_end_at). Source of truth is tinybird/datasources/tenders_hybrid_eval.datasource. Secondary indexes are not in the datasource file — they are added via ALTER TABLE (section 4).
• corpus_token_idf — IDF per token, for BM25.
• corpus_field_stats — per-field average length (avgdl), for BM25 length normalization.

3. Generate data

scripts/generate_tenders.py produces the tender records, embeds each tender's structured document with OpenAI (validating that every vector is exactly 1536-d), and in the same pass emits the two BM25 auxiliary tables using the same tokenizer as the text index (lowercase then split on non-alphanumeric).

# ~100k tenders (recommended starting point). Embedding ~100k short docs is a
# handful of batched OpenAI requests and costs well under $1.
python scripts/generate_tenders.py --num-tenders 100000

Outputs (all git-ignored):

data/tenders/batch_NNNN.ndjson       # tender rows + embeddings (<=500 MB each)
data/corpus/corpus_token_idf.ndjson  # token, df, idf
data/corpus/corpus_field_stats.ndjson

4. Ingest data

scripts/ingest_data.py uploads via tb --cloud datasource append, respecting the datasource API limits (max 5 requests/minute → one append every ~13s; max 500 MB/file).

python scripts/ingest_data.py --dry-run        # preview
python scripts/ingest_data.py                  # tenders + both BM25 tables
python scripts/ingest_data.py --start-batch 10 # resume tenders from batch 10
python scripts/ingest_data.py --only corpus    # only the BM25 aux tables
python scripts/ingest_data.py --only tenders   # only the main table

5. Add the indexes (support-only)

Tinybird datasource files cannot declare secondary indexes, so the text and vector_similarity indexes are added directly on the ClickHouse cluster. On managed/Cloud clusters this is a support-only action — send the block below to Tinybird support.

Discover cluster, database, and table names

Support SSHes into a ClickHouse replica and runs clickhouse-client:

-- Cluster name
SELECT DISTINCT cluster FROM system.clusters ORDER BY cluster;
-- e.g. -> gcp_europe_west3_xxx

-- Database (use the cluster name from above)
SELECT DISTINCT database
FROM clusterAllReplicas('{cluster}', system.tables)
ORDER BY database;
-- e.g. -> d_1bff36

-- Table (physical name of tenders_hybrid_eval)
SELECT DISTINCT name
FROM clusterAllReplicas('{cluster}', system.tables)
WHERE database = 'd_1bff36'
ORDER BY name;
-- e.g. -> t_8227c2bd85cc42cd97b1c1f7d4ae4d58_v1

Add and materialize the indexes (ClickHouse 26.3 syntax)

Add and materialize the indexes.

-- Text (inverted) index on the concatenated searchable text.
ALTER TABLE d_1bff36.t_8227c2bd85cc42cd97b1c1f7d4ae4d58_v1 ON CLUSTER '{cluster}'
ADD INDEX idx_search_text search_text
TYPE text(tokenizer = splitByNonAlpha, preprocessor = lowerUTF8(search_text));

-- Vector similarity (HNSW) index on the embedding.
-- The dimension (1536) MUST match text-embedding-3-small's output.
ALTER TABLE d_1bff36.t_8227c2bd85cc42cd97b1c1f7d4ae4d58_v1 ON CLUSTER '{cluster}'
ADD INDEX idx_embedding embedding
TYPE vector_similarity('hnsw', 'cosineDistance', 1536);

-- Materialize both onto existing parts.
ALTER TABLE d_1bff36.t_8227c2bd85cc42cd97b1c1f7d4ae4d58_v1 ON CLUSTER '{cluster}'
MATERIALIZE INDEX idx_search_text
SETTINGS mutations_sync = 2, distributed_ddl_task_timeout = 3600;

ALTER TABLE d_1bff36.t_8227c2bd85cc42cd97b1c1f7d4ae4d58_v1 ON CLUSTER '{cluster}'
MATERIALIZE INDEX idx_embedding
SETTINGS mutations_sync = 2, distributed_ddl_task_timeout = 3600;

Confirm both materializations are done and their size.

SELECT command, is_done, latest_fail_reason, parts_to_do, create_time
FROM system.mutations
WHERE database = 'd_1bff36' AND table = 't_8227c2bd85cc42cd97b1c1f7d4ae4d58_v1'
ORDER BY create_time DESC LIMIT 10;

   ┌─command─────────────────────────────┬─is_done─┬─latest_fail_reason─┬─parts_to_do─┐
1. │ (MATERIALIZE INDEX idx_embedding)   │       1 │                    │           0 │
2. │ (MATERIALIZE INDEX idx_search_text) │       1 │                    │           0 │
   └─────────────────────────────────────┴─────────┴────────────────────┴─────────────┘

-- uncompressed is the size loaded into RAM (what vector_similarity_index_cache_size must hold)
SELECT name, type,
       formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
       formatReadableSize(sum(data_compressed_bytes))   AS compressed
FROM system.data_skipping_indices
WHERE database = 'd_1bff36' AND table = 't_8227c2bd85cc42cd97b1c1f7d4ae4d58_v1'
GROUP BY name, type;

   ┌─name────────────┬─uncompressed─┬─compressed─┐
1. │ idx_embedding   │ 319.26 MiB   │ 241.33 MiB │
2. │ idx_search_text │ 2.88 MiB     │ 2.87 MiB   │
   └─────────────────┴──────────────┴────────────┘

With both indexes materialized, you're ready to run and compare the strategies (section 6). No further index changes are needed.

Size the vector index cache

The HNSW index must be fully resident in RAM to serve a query, held in the vector_similarity_index_cache_size cache. Size it to the index for this corpus.

Per-row index cost (bf16 quantization, dimension 1536, M=32):

per row = (1536 × 2 bytes)  +  (32 × 4 bytes × 2)  = 3072 + 256 = 3328 bytes

For 100,000 tenders: 100,000 × 3328 = 332,800,000 bytes ≈ 317 MiB. That fits comfortably in the default vector_similarity_index_cache_size of 5 GiB, so no change is needed for this corpus.

If you scale up (recompute bytes = rows × 3328, rounded up with headroom) and the index exceeds 5 GiB, raise the cache. This is a server config change requiring cluster operator access — ask Tinybird support to apply the drop-in on each replica:

sudo tee /etc/clickhouse-server/config.d/vector_cache.xml << 'EOF'
<clickhouse>
    <vector_similarity_index_cache_size>42949672960</vector_similarity_index_cache_size>
</clickhouse>
EOF

# No restart needed -- this setting is applied at runtime.
clickhouse-client -q "SYSTEM RELOAD CONFIG"

Verify the index size in RAM:

SELECT formatReadableSize(sum(secondary_indices_uncompressed_bytes)) AS index_in_ram
FROM system.parts
WHERE database = 'd_1bff36' AND table = 't_8227c2bd85cc42cd97b1c1f7d4ae4d58_v1' AND active;

6. Run the search strategies

scripts/search.py embeds each query with OpenAI, runs the three pipes, fuses with RRF in Python, and writes one JSON file per (query, strategy). Metadata filters are built once per query and applied identically to every branch.

# All queries, all five strategies -- this is the full comparison run.
python scripts/search.py

# [Optional] Just one query (same five strategies)
python scripts/search.py --query solar-panels-for-schools

Output layout:

results/
  solar-panels-for-schools/
    lexical.json  lexical-bm25.json  semantic.json  hybrid.json  hybrid-bm25.json
  ...

Each file carries query_id, query, strategy, candidate_limit, result_limit, filters, latency_ms, and the ranked results.

(Optional) Metadata filtering

Not needed for the strategy comparison — skip this unless you specifically want to test filtered retrieval. These flags apply extra hard filters identically to all five strategies, so the comparison stays fair. They produce different, narrower result sets and overwrite the same results/<query_id>/ files, so run them separately from the baseline (or evaluate them separately).

python scripts/search.py --open --nuts ES51
python scripts/search.py --category "IT security services (CPV 72500000)"
python scripts/search.py --submission-from "2026-01-01 00:00:00" --submission-to "2026-12-31 23:59:59"

• --open — only tenders still open. NULL policy: a tender with no published deadline (submission_end_at IS NULL) is included as still open.
• --nuts ES51 — location_nuts_code prefix match (e.g. Catalonia).
• --contracting-party, --category — exact match.
• --submission-from / --submission-to — deadline range. Range filters compare against submission_end_at, so tenders with a NULL deadline are excluded from a range (NULL comparisons are not true).

Filters can also be set per-query in data/queries.json under "filters".

7. Evaluate

Evaluation is fully deterministic — no AI in the loop. Every tender's category carries a unique CPV code; each query declares its target relevant_cpv plus adjacent_cpv (same-group themes). A result is graded 3 (exact, category contains relevant_cpv), 1 (adjacent, contains an adjacent_cpv), or 0. Python computes the metrics from those grades.

python scripts/evaluate.py
python scripts/evaluate.py > results/metrics.txt   # dump to a file

Reads the result files under results/<query_id>/, computes the metrics below per strategy, prints one row per strategy, and writes results/evaluation_summary.json.

The table has one row per strategy (lexical, lexical-bm25, semantic, hybrid, hybrid-bm25) and these columns, each averaged over all queries:

Column	What it measures
P@5	Precision@5 — fraction of the top 5 that are exact hits (grade 3). "How clean is the very top of the list."
P@10	Precision@10 — same, over the top 10. "How clean is the first page."
MRR	Mean Reciprocal Rank — 1/(rank of the first exact hit), averaged over queries. Rewards putting a relevant hit as high as possible (1.0 = always rank 1, 0.5 = rank 2, …).
nDCG@10	Normalized Discounted Cumulative Gain @10 — graded quality of the top-10 ordering (exact hits worth 3, adjacent-theme hits worth 1, discounted by rank), normalized so 1.0 = the best possible ordering. The main "overall ranking quality" number.

Higher is better for all four. Below the table it also prints the BM25 contribution — the nDCG@10 delta of lexical-bm25 vs lexical and hybrid-bm25 vs hybrid — so you can see whether IDF + term frequency actually helped.

Expected results

On the 100k-tender / 38-query corpus, python scripts/evaluate.py produces roughly:

strategy           P@5    P@10     MRR  nDCG@10
-----------------------------------------------
lexical          0.537   0.539   0.559    0.581
lexical-bm25     0.974   0.974   0.974    0.974
semantic         0.974   0.974   0.974    0.974
hybrid           0.732   0.771   0.789    0.773
hybrid-bm25      0.974   0.974   0.987    0.974

BM25 contribution (nDCG@10 delta):
  lexical-bm25 vs lexical: +0.392
  hybrid-bm25 vs hybrid: +0.201

What this shows:

• Boolean lexical is weak (~0.54–0.58). Every matched token counts equally — no IDF, no term frequency, no length normalization — so it can't tell a strong match from an incidental one.
• BM25 is a large lift (+0.39 nDCG). IDF + TF + length normalization push lexical-bm25 to ~0.97. This is the headline finding: proper lexical scoring matters far more than fusion on this data.
• semantic matches BM25 (~0.97). Real text-embedding-3-small vectors classify the theme essentially perfectly.
• hybrid (boolean + vector) < either strong branch. RRF fusing the weak boolean lexical branch with semantic drags the result down to ~0.77 — fusion only helps when both branches are strong.
• hybrid-bm25 is best on MRR (0.987) and ties the top on the other metrics: fusing two strong branches puts a relevant hit at rank 1 slightly more often than either alone.

Why the strong strategies saturate at ~0.97 (and why that's expected). Relevance here is theme/CPV-level, and with 100k rows across 24 themes there are ~4,000 relevant tenders per query. The top-10 is therefore trivially filled with easy exact matches by any competent ranker, so Precision@10 can't fall unless a non-relevant doc outranks them — which doesn't happen on clean, redundant synthetic data. Treat the ordering of strategies as the signal, not the absolute values.

Making the benchmark discriminate (future work)

To spread the strong strategies apart instead of saturating:

• (A) Stronger lexical traps. The generator injects a distractor phrase into ~20% of descriptions, but the trap only lands in the low-weight description field, so true tenders (query terms in title/category) always outrank it. Inject the trap phrase into the title of a wrong-category tender so BM25 ranks it high: that pries apart lexical/lexical-bm25 (fooled) from semantic (resists, since the document's dominant topic is unchanged).
• (B) Scarce, fine-grained relevance. The real ceiling is coarse, abundant relevance. Define relevance narrower than theme — e.g. a query relevant only to tenders with a specific sub-attribute (theme + location + feature), so only a handful match and where each strategy ranks them actually matters. This needs per-query relevance labels beyond the CPV code.
• (C) Harder query mix. More pure paraphrases (no shared tokens → favor semantic) and rare acronyms/codes (favor lexical), so no single strategy wins everywhere and hybrid's advantage becomes visible.

8. The query set

38 queries in data/queries.json span exact terminology, semantic intent, named products (Oracle), CPV codes, geographic intent (Catalonia, Madrid), mixed intent, acronyms (SOC/pentest), paraphrases where the literal words don't appear, and a filtered query. Each query carries relevant_cpv + adjacent_cpv for grading.

The corpus is built to discriminate the strategies rather than saturate: 24 themes across 7 groups (so adjacent themes compete), and ~20% of tenders carry a distractor — a secondary mention of another theme's keywords, keeping their own category. Those are lexical traps a pure token scorer falls for and a semantic scorer should resist. Edit or extend the file — it is version-controlled and read directly by search.py.

9. Cleanup

The datasources carry a 2-day TTL (created_at + INTERVAL 2 DAY), so data auto-expires. To clear immediately:

tb --cloud datasource truncate tenders_hybrid_eval --yes
tb --cloud datasource truncate corpus_token_idf --yes
tb --cloud datasource truncate corpus_field_stats --yes

Truncate removes rows but leaves the secondary indexes on the table. To drop them:

ALTER TABLE d_1bff36.t_8227c2bd85cc42cd97b1c1f7d4ae4d58_v1 ON CLUSTER '{cluster}'
DROP INDEX idx_search_text;

ALTER TABLE d_1bff36.t_8227c2bd85cc42cd97b1c1f7d4ae4d58_v1 ON CLUSTER '{cluster}'
DROP INDEX idx_embedding;

Confirm they are gone:

SELECT name, type
FROM system.data_skipping_indices
WHERE database = 'd_1bff36' AND table = 't_8227c2bd85cc42cd97b1c1f7d4ae4d58_v1';

Project structure

tinybird/
  datasources/  tenders_hybrid_eval, corpus_token_idf, corpus_field_stats
  endpoints/    lexical_search, lexical_bm25_search, semantic_search
scripts/
  generate_tenders.py   # tenders + OpenAI embeddings + BM25 corpus stats
  ingest_data.py        # rate-limited upload of all three datasources
  search.py             # 5 strategies, RRF fusion, metadata filters
  evaluate.py           # deterministic metrics from CPV ground truth
  tb_api.py             # endpoint host/token discovery + calls
data/queries.json       # evaluation query set (each query tagged with relevant_cpv)