Vector Similarity Search Benchmark for Tinybird/ClickHouse

Benchmarks ClickHouse vector search performance on synthetic TikTok post embeddings, comparing brute-force cosine distance against the vector_similarity HNSW index.

What it tests

• Brute-force search over 100k+ posts with 768-dimensional embeddings using both cosineDistance and L2Distance
• HNSW-indexed search (added manually via ALTER TABLE) over the same data
• Accuracy: Precision@K, MRR against ground-truth topic assignments
• Performance: Server-side query latency (avg, median, p95, max)
• Distance function comparison: cosineDistance vs L2Distance (equivalent for normalized data, but L2 is recommended)

Prerequisites

• Python 3.13
• Set cheriff > ClickHouse Limits > admin_max_execution_time to 295s.
• OpenAI API key (optional, for regenerating topics_and_queries.json)

1. Setup

# Create and activate a virtual environment
python3.13 -m venv .venv
source .venv/bin/activate

# Install Python dependencies
pip install --upgrade pip
pip install -r scripts/requirements.txt

# [Optional] Set OPENAI_API_KEY environment variable for 
# generating topic and query embeddings
export OPENAI_API_KEY="your-openai-api-key"

2. Deploy to Tinybird

Make sure you are using the correct tb CLI.

which tb
  -> path-to-your-project/.venv/bin/tb
tb --version

Authenticate with the correct region (e.g. gcp/europe-west3) and workspace (e.g. vector_similarity_tests)

tb login

Validate the project locally, then deploy to Cloud:

tb build
tb --cloud deploy --check
[Alt] tb --cloud deployment create --check
tb --cloud deploy

Verify deployment:

tb datasource ls
  -> tiktok_posts_embeddings
tb pipe ls
  -> vector_search_cosine
  -> vector_search_l2

Open UI:

tb --cloud open

3. Generate data

[Optional] Step 1: Regenerate topic and query embeddings (one-time)

This calls OpenAI API 40 times to produce embeddings for 20 topics and 20 search queries. Make sure that the model text-embedding-3-small is enabled in your OpenAI account.

python scripts/generate_topic_embeddings.py

Output: data/topics_and_queries.json

Step 2: Generate synthetic posts

Creates 2M posts with deterministic embeddings (no OpenAI calls needed). Each post gets an embedding that is a noisy variation of its assigned topic centroid.

# Full 2M posts (~80 batch files, ~412MB each)
python scripts/generate_posts.py --num-posts 2000000

# Smaller test run (~4 batches, ~412MB each)
python scripts/generate_posts.py --num-posts 100000

Output:

• data/embeddings/batch_NNN.ndjson -- NDJSON batch files ready to ingest into Tinybird

4. Ingest data

The ingest script uses tb datasource append and respects the standard rate limits:

• Max 5 requests/minute for the datasource API
• Max 500 MB per NDJSON file

# Preview what will be ingested (no actual upload)
python scripts/ingest_data.py --dry-run

# Ingest all batch files
python scripts/ingest_data.py

# Resume from a specific batch (e.g. 2) if interrupted
python scripts/ingest_data.py --start-batch 2

# Upload one single batch file at a time
tb --cloud datasource append tiktok_posts_embeddings --file data/embeddings/batch_001.ndjson

For 2M posts (~80 batch files of ~410 MB each), ingestion could take 1 hour.

Verify row count after ingestion:

tb --cloud sql "SELECT count() FROM tiktok_posts_embeddings"

5. Verify deployment

Test the endpoint with a rock_music query:

# Get base URL and token (the full URL includes default params we don't want)
FULL_URL=$(tb --cloud --show-tokens endpoint url vector_search_cosine | tail -1)
BASE_URL=$(echo "$FULL_URL" | cut -d'?' -f1)
TOKEN=$(echo "$FULL_URL" | grep -o 'token=[^&]*' | cut -d'=' -f2)

# Extract the rock_music query embedding as a comma-separated string
EMBEDDING=$(python -c "
import json
with open('data/topics_and_queries.json') as f:
    d = json.load(f)
q = next(q for q in d['queries'] if q['topic'] == 'rock_music')
print(','.join(f'{v:.8f}' for v in q['embedding']))
")

# Call the pipe endpoint
curl -s "${BASE_URL}?token=${TOKEN}&query_embedding=${EMBEDDING}&top_k=5" | jq .

Expected response: 5 posts with similarity scores, all from the rock_music topic.

6. Run tests

Accuracy test

Calls the vector search pipe for all 20 queries and compares results against ground truth. Both pipes return the same ranking (data is normalized), but cosine returns a similarity score (1 = identical) while L2 returns a distance (0 = identical). The test scripts only use the topic field, so the metric column doesn't affect accuracy results.

# cosineDistance baseline (no index) — uses vector_search_cosine pipe
python scripts/test_accuracy.py --output results/accuracy_cosine_no_index.json

# L2Distance baseline (no index) — uses vector_search_l2 pipe
python scripts/test_accuracy.py --pipe vector_search_l2 --output results/accuracy_l2_no_index.json

Metrics reported:

• Precision@K (K=10, 50, 100): fraction of the top-K results that belong to the correct topic; 1.0 is perfect, 0.5 means half the results were from the wrong topic.
• MRR (Mean Reciprocal Rank): measures how high the first correct result appears in the list. If it's at position 1 the score is 1.0, position 2 → 0.5, position 5 → 0.2. Averaged across all 20 queries; 1.0 means every query returned the correct topic as the very first result.

Performance test

Benchmarks query latency with warm-up and multiple measured runs. L2Distance should be slightly faster than cosineDistance for normalized data since it skips an internal normalization step.

# cosineDistance baseline (no index) — uses vector_search_cosine pipe
python scripts/test_performance.py --output results/performance_cosine_no_index.json

# L2Distance baseline (no index) — uses vector_search_l2 pipe
python scripts/test_performance.py --pipe vector_search_l2 --output results/performance_l2_no_index.json

Metrics reported: avg, median, p95, max for both server-side and wall-clock time.

7. Add vector_similarity HNSW index

Overview of vector_similarity HNSW index

Ref: https://clickhouse.com/docs/engines/table-engines/mergetree-family/annindexes Ref: GRANULARITY.md

Index syntax:

  INDEX index_name vectors TYPE vector_similarity('hnsw', <distance_function>, <dimensions>[, <quantization>, <hnsw_max_connections_per_layer>, <hnsw_candidate_list_size_for_construction>]) [GRANULARITY N]

Function Choices: L2Distance, CosineDistance, DotProduct

• For normalized data, L2Distance is usually the best choice, otherwise cosineDistance is recommended to compensate for scale.
• For L2Distance and CosineDistance, a smaller value means more similar.

Approximate vector search methods:

• ClickHouse only supports the hnsw (Hierarchical Navigable Small World Graph) index type for approximate vector search.

Other options:

• Granularity is the size of the granules, defaults to 100 million.
• Quantization is the number of bits used for the vectors: f64, f32, f16, bf16, i8, or b1. The default is bf16.
• hnsw_max_connections_per_layer: number of neighbors per graph node. Defaults to 32.
• hnsw_candidate_list_size_for_construction: size of the dynamic candidate list for the graph construction. Defaults to 128.

Test the vector_similarity HNSW index with cosineDistance

After running baseline tests (without index), SSH into one of the ClickHouse réplicas on the dedicated cluster and run:

-- Check your cluster name
SELECT DISTINCT cluster
FROM system.clusters
ORDER BY cluster;
-> gcp_europe_west3_vector_similarity

-- Check the name of the database
SELECT DISTINCT database
FROM clusterAllReplicas('gcp_europe_west3_vector_similarity', system.tables)
ORDER BY database;
-> d_f6bdcf

-- Check the name of the table
SELECT DISTINCT name
FROM clusterAllReplicas('gcp_europe_west3_vector_similarity', system.tables)
WHERE database = 'd_f6bdcf'
ORDER BY name;
-> t_71cfa19f46bb495ab89cb34bf8102f75_v1

-- Add HNSW index for cosine distance (used by vector_search_cosine pipe)
ALTER TABLE d_f6bdcf.t_71cfa19f46bb495ab89cb34bf8102f75_v1 ON CLUSTER 'gcp_europe_west3_vector_similarity'
ADD INDEX embedding_cosine embedding
TYPE vector_similarity('hnsw', 'cosineDistance', 768);

-- Materialize the index (takes like 10 minutes)
ALTER TABLE d_f6bdcf.t_71cfa19f46bb495ab89cb34bf8102f75_v1 ON CLUSTER 'gcp_europe_west3_vector_similarity'
MATERIALIZE INDEX embedding_cosine
SETTINGS mutations_sync = 2, , distributed_ddl_task_timeout = 1000;

-- Check if the materialization process is done
SELECT
    command,
    is_done,
    latest_fail_reason,
    parts_to_do,
    create_time,
    formatReadableTimeDelta(dateDiff('second', create_time, now())) AS elapsed
FROM system.mutations
WHERE database = 'd_f6bdcf'
  AND table = 't_71cfa19f46bb495ab89cb34bf8102f75_v1'
ORDER BY create_time DESC
LIMIT 10;

-- Verify the indexes exist
SELECT *
FROM system.data_skipping_indices
WHERE database = 'd_f6bdcf'
  AND table = 't_71cfa19f46bb495ab89cb34bf8102f75_v1';

-- Check index size on disk (3.38 GiB uncompressed, 2.52 GiB compressed)
SELECT
    table,
    formatReadableSize(sum(secondary_indices_uncompressed_bytes)) AS index_size_uncompressed,
    formatReadableSize(sum(secondary_indices_compressed_bytes)) AS index_size_compressed
FROM system.parts
WHERE database = 'd_f6bdcf'
  AND table = 't_71cfa19f46bb495ab89cb34bf8102f75_v1'
  AND active
GROUP BY table;

Then, run the tests again with the index:

# cosineDistance (with HNSW index) — uses vector_search_cosine pipe + embedding_cosine index
python scripts/test_accuracy.py --output results/accuracy_cosine_with_index.json
python scripts/test_performance.py --output results/performance_cosine_with_index.json

Cleanup:

-- Drop the index (fast)
ALTER TABLE d_f6bdcf.t_71cfa19f46bb495ab89cb34bf8102f75_v1 ON CLUSTER 'gcp_europe_west3_vector_similarity'
DROP INDEX embedding_cosine;

Test the vector_similarity HNSW index with L2Distance

Do the same for L2Distance:

-- Add HNSW index for L2 distance (used by vector_search_l2 pipe)
ALTER TABLE d_f6bdcf.t_71cfa19f46bb495ab89cb34bf8102f75_v1 ON CLUSTER 'gcp_europe_west3_vector_similarity'
ADD INDEX embedding_l2 embedding
TYPE vector_similarity('hnsw', 'L2Distance', 768);

-- Materialize the index (takes like 10 minutes)
ALTER TABLE d_f6bdcf.t_71cfa19f46bb495ab89cb34bf8102f75_v1 ON CLUSTER 'gcp_europe_west3_vector_similarity'
MATERIALIZE INDEX embedding_l2
SETTINGS mutations_sync = 2, distributed_ddl_task_timeout = 1000;

Then, run the tests again with the L2Distance index:

# L2Distance (with HNSW index) — uses vector_search_l2 pipe + embedding_l2 index
python scripts/test_accuracy.py --pipe vector_search_l2 --output results/accuracy_l2_with_index.json
python scripts/test_performance.py --pipe vector_search_l2 --output results/performance_l2_with_index.json

Cleanup:

-- Drop the index
ALTER TABLE d_f6bdcf.t_71cfa19f46bb495ab89cb34bf8102f75_v1 ON CLUSTER 'gcp_europe_west3_vector_similarity'
DROP INDEX embedding_l2;

Compare results

Compare the JSON output files to see:

• Whether the HNSW index changes accuracy (approximate search may sacrifice some precision)
• How much the HNSW index improves query latency
• Whether L2Distance is faster than cosineDistance (expected for normalized data, since L2 skips internal normalization)

8. Cleanup

The datasource has a TTL of 2 days from ingestion (created_at + INTERVAL 2 DAY). Data will auto-expire after 2 days.

Truncate data only:

tb --cloud datasource truncate tiktok_posts_embeddings --yes

Remove all deployed resources (pipes and datasource) from the cloud:

mv tinybird ../
tb --cloud deployment create --check --allow-destructive-operations
tb --cloud deploy --allow-destructive-operations 

Project structure

tinybird/
  tinybird.config.json              # Project config
  datasources/
    tiktok_posts_embeddings.datasource  # 768-dim embeddings + metadata, MergeTree, TTL 2d
  endpoints/
    vector_search_cosine.pipe       # Cosine similarity search — returns `similarity` (1 = identical, 0 = unrelated)
    vector_search_l2.pipe           # L2 distance search — returns `distance` (0 = identical, larger = farther)

scripts/
  requirements.txt                  # Python dependencies
  generate_topic_embeddings.py      # Generate 20 topic + 20 query embeddings via OpenAI
  generate_posts.py                 # Generate 2M synthetic posts with deterministic embeddings
  ingest_data.py                    # Batch append to Tinybird with rate limiting
  test_accuracy.py                  # Precision, recall, MRR tests
  test_performance.py               # Latency benchmark
  tb_api.py                         # Shared Tinybird API helper

data/                               # Generated data (gitignored except topics_and_queries.json)
  topics_and_queries.json           # Pre-computed topic/query embeddings
  embeddings/
    batch_NNN.ndjson                # NDJSON files ready for ingestion