Vector databases like Pinecone, Weaviate, and Milvus exist for one job: store embeddings and find the nearest ones to a query vector, fast.
ClickHouse wasn’t built for that.
It was built for analytics – scanning huge tables of structured data quickly.
But ClickHouse can also store vectors and search them, using plain SQL.
So the real question isn’t “does ClickHouse support vector search.”
It’s “is that support good enough to skip a dedicated vector database.”
What a Vector Database Actually Does
Strip away the marketing, and it comes down to three things.
- Store embeddings (arrays of floats).
- Index them so similarity search doesn’t require scanning everything.
- Return the nearest neighbors to a query vector.
Everything else – hybrid search, metadata filtering, embedding generation – is built around that core job.
Storing Vectors in ClickHouse
Vectors are just arrays.
CREATE TABLE articles (
id UInt32,
title String,
embedding Array(Float32)
) ENGINE = MergeTree ORDER BY id
Enter fullscreen mode Exit fullscreen mode
No extension needed. No plugin. This is a normal column type.
Brute Force Search
The simplest way to find similar vectors is a distance function, ordered and limited.
SELECT title, L2Distance(embedding, {target_vector}) AS score
FROM articles
ORDER BY score ASC
LIMIT 5
Enter fullscreen mode Exit fullscreen mode
This works correctly out of the box, and gives exact results.
The catch: every query scans every row’s vector. Fine at a few thousand rows, painfully slow at a few million.
This is exactly the problem specialized vector databases were built to avoid.
I Actually Benchmarked This
Instead of guessing, I ran the brute-force query above against real ClickHouse (via chdb, the embeddable version of the same engine), on synthetic normalized embeddings.
How search time scales with row count (128-dim vectors):
Rows Avg query time 50,000 0.025s 100,000 0.046s 250,000 0.155s 500,000 0.220s 1,000,000 0.413sIt’s close to linear, which is exactly what you’d expect from brute force: no shortcuts, every row gets a distance calculation.
How search time scales with vector dimension (200,000 rows):
Dimension Avg query time 128 0.086s 384 0.251s 768 0.449sDimension hurts roughly as much as row count. A 768-dim embedding (common for larger sentence-transformer models) costs about 5x what a 128-dim one does, at the same row count. If you’re using something like OpenAI’s 1536-dim embeddings, expect that cost to roughly double again – I couldn’t get a clean number at 1536 dims in my test environment (it ran out of memory before finishing), but the trend makes the direction obvious.
Does SQL-native filtering actually help? I tested WHERE category = 'engineering' against an unfiltered query, on the same 500k-row table, filtering out about 75% of rows:
Honestly, smaller improvement than I expected – about 8%, not a proportional 75% drop. The vector column still gets read before the filter meaningfully prunes work, because the table isn’t ordered by category. If you want filtering to actually pay off, you’d need to design your ORDER BY / partitioning around your common filter columns, the same way you’d tune any ClickHouse table – vectors don’t get a free pass on data modeling.
What I couldn’t test: ClickHouse’s HNSW-based vector_similarity index. The chdb build I used doesn’t ship with it compiled in, so I can’t give you a real indexed-vs-brute-force number here – only the brute-force baseline above. If you want that comparison, you’ll need to run it against a real ClickHouse server (Docker or ClickHouse Cloud) with allow_experimental_vector_similarity_index enabled. Better to tell you that than make up a “10x faster” number I didn’t actually measure.
Indexed (Approximate) Search
ClickHouse supports an HNSW-based vector similarity index – the same graph-based approach most dedicated vector databases use internally.
The idea: once an index is built on the vector column, queries should run faster than brute force, at the cost of exact accuracy – trading a bit of recall for a lot of speed. That tradeoff is the entire premise behind nearest-neighbor search in every vector database, not just ClickHouse. I just can’t hand you a verified number for it from this test run – see above.
What ClickHouse Adds That Vector Databases Usually Don’t
The interesting part isn’t the distance calculation. It’s what you can do around it in the same query.
SELECT title, category, L2Distance(embedding, {target_vector}) AS score
FROM articles
WHERE category = 'engineering'
AND published_at > now() - INTERVAL 30 DAY
ORDER BY score ASC
LIMIT 5
Enter fullscreen mode Exit fullscreen mode
Filtering by metadata, joining against other tables, aggregating – all native SQL, all in the same pass as the similarity search.
In many dedicated vector databases, metadata filtering is a secondary feature bolted onto the vector index. In ClickHouse, it’s the same engine that’s been handling filters, joins, and aggregations for years – the advantage is in query flexibility and not having a second system to keep in sync, not necessarily raw speed (my benchmark below shows filtering alone isn’t a big speed win unless your table is actually modeled around it).
Where ClickHouse Falls Short
It isn’t a drop-in replacement for every vector database use case.
- It’s not built for high-frequency, single-row upserts the way an OLTP-oriented vector store is.
- It doesn’t generate embeddings for you – you bring your own vectors, computed elsewhere.
- Its ANN indexing is newer than systems that have done nothing but similarity search for years, so tooling and edge-case handling are less mature.
If the workload is millions of small writes per second with constant re-indexing, a purpose-built vector database is still the safer choice.
The Actual Answer
ClickHouse is an analytical database that added vector search.
A vector database is a system built only around vector search.
If vectors are one part of a larger analytical dataset – filtered, joined, aggregated alongside structured columns – ClickHouse can genuinely replace a dedicated vector database and remove a piece of infrastructure.
If vectors are the whole workload, and writes are constant and high-frequency, a dedicated vector database still earns its place.
There isn’t a single right answer. There’s a right answer for a given workload.
답글 남기기