When ClickHouse vector search with HNSW fits your analytics stack
If you already run ClickHouse for events, logs or telemetry and need similarity search on embeddings, keeping those vectors in the same cluster is often the simpler choice. From version 25.8, ClickHouse offers vector similarity indexes that let a query find approximate nearest neighbors without scanning every row. The index is built on HNSW (hierarchical navigable small world), a graph structure that links each vector to a set of nearby vectors across several layers, so a search can move quickly toward the closest matches and stop once it has enough candidates. The trade-off is that results are approximate, and recall depends on index parameters you choose. Keeping vectors beside analytical tables pays off when similarity search has to respect the same filters your analytics already use, such as tenant, time range, service or region. A separate vector database would need those attributes copied, synchronized and kept consistent with the event store, which adds a pipeline to operate. In ClickHouse, a single query can combine a vector distance ordering with ordinary SQL predicates and joins. This fits workloads where the embeddings are a column on existing event or document tables, and where the team already operates ClickHouse backups, replication and access control. Before you commit, confirm the exact index syntax, the supported distance functions and the tuning parameters in the ClickHouse documentation for the version you run, since this feature is new and its details can change between releases. Check the ClickHouse production FAQ for guidance on testing in a pre-production environment with representative data and queries. Plan a recall test against exact search on a sample, and measure memory use and index build time on your own hardware before you decide whether this workload belongs in ClickHouse or in a dedicated vector store.
Which HNSW settings to tune and when each one fits
This table lists the vector index and search settings in ClickHouse vector similarity indexes, what each one trades, and when a team should move away from the default. Confirm exact parameter names, accepted values and defaults in the documentation for your ClickHouse version before you set them. Most teams should change the embedding model and distance function first, then tune graph size and search breadth against measured recall.
| Setting | What it trades | When to change it from the default |
|---|---|---|
| Index type and distance function | Must match how the embedding model was trained. A mismatch lowers recall without any error. | Set the distance function the model documentation recommends, such as cosine or L2. Changing it later requires rebuilding the index. |
| Embedding dimension | Fixed when the index is created. Higher dimensions cost more memory and distance computation time. | Match the model output exactly. Reduce dimensions only through a model that supports it, then retest recall on your own queries. |
| Element type (quantization) | Lower-precision storage cuts memory and can speed scans. It can reduce recall, especially at small candidate sizes. | Consider it when the full-precision index does not fit memory budgets. Measure recall against a full-precision baseline on representative data. |
| HNSW connectivity (M) | More links per node improve recall and search quality. They increase memory use and index build time. | Raise it when recall is short at acceptable search latency. Lower it when memory is the binding constraint and recall remains sufficient. |
| Construction effort (ef_construction) | Wider candidate search while building improves graph quality. It lengthens build and rebuild time, with little effect on query memory. | Raise it when recall plateaus after tuning M. Expect longer ingestion or backfill windows, and confirm the build time on a copy of your data. |
| Search candidate breadth (hnsw_candidate_list_size_for_search) | Larger candidate lists raise recall and query latency. The setting applies per query, so it can differ by workload. | Raise it for high-stakes retrieval where missed neighbors matter. Keep it lower for interactive queries that tolerate approximate results. |
Pure similarity search or filtered search: which pattern to build
With no SQL filter, the query is an approximate nearest neighbor lookup across the whole table, which is the case the HNSW index is designed for. The index traverses a graph of stored vectors and returns the closest candidates, so recall depends on the build settings and on how much of the graph the search explores at query time.
- Build cost on large tables: Indexing every row adds time and memory during inserts and merges. Measure build time on a copy of your largest table before deciding how much data each index covers.
- Recall against exact results: Run a sample of query vectors through both the index and an exact brute-force scan, then compare the neighbors returned. This is the only reliable way to put a number on recall for your data.
- Search breadth versus latency: Exploring more candidates at query time generally improves recall and increases latency. Test the trade-off at the latency your application can tolerate.
- Re-embedding events: A new embedding model produces new vectors for every row, which means a re-embedding job and an index rebuild. Plan that window before you change models.
Check recall and latency at your expected concurrency, using query vectors sampled from real traffic rather than hand-picked examples.
When a filter such as tenant or time range reduces the table to a small share of rows, the query can no longer rely on a graph built over every row. The engine has to apply the predicate and the vector search in a way that still returns the requested number of true neighbors. If the filter is applied after candidates are chosen, a selective predicate can leave few or no rows. Confirm in the ClickHouse documentation for your version how the filter is evaluated alongside the index.
- Post-filter loss: If candidates are selected first and filtered afterward, a small tenant can receive an empty or short result even though it has matching vectors.
- Ordering key alignment: Placing tenant or time columns in the table's ordering key lets ClickHouse skip data that cannot match, which reduces the rows the distance calculation touches.
- Under-filled results: Verify that queries for small tenants return the requested number of rows, not only queries for large tenants.
- Recall per tenant: Recall often varies between a large tenant and a small one, so sample queries for each segment rather than averaging across the table.
- Measured selectivity: Count how many rows each filter value matches in your own data. The same predicate can be highly selective for one tenant and broad for another, and that difference drives the behavior you test.
Run the same vector query for your smallest and largest filter values, then compare row counts, recall and latency.
When similarity feeds an aggregation, such as the number of similar incidents per service per day or average distance by customer segment, the question shifts from which rows are nearest to what the grouped result should show. Approximate neighbors become inputs to the aggregate, so small recall errors can shift totals. Decide early whether each figure must be exact or can be approximate, and whether the vector step runs in a subquery, a common table expression or a precomputed stage.
- Bounded candidate sets: Place the top-k search in a clearly limited subquery, so the index handles the search and the aggregation runs over a fixed set of rows.
- Exactness requirements: Agree with report owners which figures need exact counts. Label approximate figures on dashboards so readers know their basis.
- Join cost: Joining the vector table to large event tables multiplies the work. Pre-filter the event side and test join order on representative volumes.
- Precomputed stages: Materialized views can separate embedding cost from query cost, but they add storage and need a rebuild path when models or schemas change.
- Shared resources: Vector queries run alongside logs and events, so they compete for memory and CPU. Set resource limits and test under the mixed workload you expect.
Compare aggregates produced through the index with an exact baseline over a sample period, and record where and by how much they diverge.
What drives memory use and build time for an HNSW index
Memory use and build time for an HNSW index grow with four inputs: vector dimension, numeric precision, row count and the graph parameters you choose. Dimension and precision set the raw size of each vector, so a column of wide, full-precision embeddings costs more to hold and to index than a narrower or lower-precision one. Row count multiplies that base cost. The graph parameters control how many neighbor links each node keeps and how widely the builder searches while it inserts. More links and a wider search usually improve recall but enlarge the graph and lengthen the build. Confirm the exact index arguments, their defaults and their valid ranges for your ClickHouse version in the vector search documentation before you size any hardware.
Data layout changes the cost as well. Each insert creates new data parts, and background merges later combine them, so the number of parts and how often merges run both affect the work the index requires. A workload that inserts many small batches will generally carry more index overhead than one that loads larger batches. Each replica that holds the table also needs CPU and memory during index construction. Because ReplicatedMergeTree keeps copies of the data on several replicas, you must confirm whether each replica rebuilds the index itself or receives it with the part. The answer determines whether build cost is paid once or multiplied across the replica set.
Benchmark on a copy of your own data, not on a generated sample. Use a representative slice that covers the full range of row counts, dimensions and insert batch sizes you expect, and measure steady-state ingestion over several merge cycles, because index build cost recurs with every insert and merge. In a pilot, ask the provider to measure peak memory per replica during a build and during a large merge, build time per batch, index size compared with the raw vector column, insert throughput with and without the index, and query latency and recall at the parameter settings you plan to use. The ClickHouse production FAQ recommends testing on a pre-production environment with representative data and queries, and the same discipline applies here: ClickHouse production FAQ.
How to add a vector similarity index to an existing table
This sequence adds an HNSW vector similarity index to a table that already holds data, and it proves the index on a pre-production replica before any production change. Each step closes with a check, so the team knows the index is ready for production replicas and that its recall and latency were measured against a baseline the team set in advance.
Confirm version and supported release line
The platform engineer or DBA runs
SELECT version()on every node and confirms the release is 25.8 or later, since vector similarity indexes arrive in that line. Then they check the version against the list of supported releases in the ClickHouse security policy, because unsupported lines receive no security updates. Read the changelog for every release between the current version and the target, since minor backward-incompatible changes can occur. Done when all replicas report the same supported version and the upgrade notes have been reviewed and filed.Create a pre-production replica with representative data
The platform team builds a pre-production cluster with the same shard and replica layout as production, including a ClickHouse Keeper ensemble of at least three nodes, which the replication guide recommends for production. They then copy a representative sample of the production table, including the embedding column, the filter columns used by queries, and the full range of event dates. Done when row counts and sample checksums match the source for each partition, and replication shows all replicas healthy.
Add the vector column and index definition
The engineer adds an array column for the embedding with the dimension and element type produced by the embedding model, then defines a vector similarity index with the HNSW method, distance function and graph parameters the team chose from its recall target and memory budget. Confirm the exact index syntax and parameter names for your version in the ClickHouse documentation before running the statement. Test the DDL on a scratch table first. Done when
SHOW CREATE TABLEdisplays the column and index as intended, and the DDL has replicated to every pre-production replica.Backfill data and confirm index build
The engineer loads existing rows into the column in batches, using an insert from the source table or a background update, and materializes the index for parts that predate it if the version requires that step. They watch
system.mutationsuntil every mutation reports done with no failure reason. Then they runEXPLAIN indexes = 1on a sample vector query to confirm the planner uses the vector index. Done when no mutations are pending or failed, every replica reports the index, and the sample query plan shows the index in use.Check recall against a brute-force baseline
The data scientist or search engineer selects sample queries from production logs and computes exact nearest neighbors with a brute-force distance scan over the same data, so the reference set is not affected by the index. They compare the top-k results from indexed queries against that baseline, including queries with SQL filters applied. The team sets the recall threshold before the test, based on the application's tolerance for missed neighbors. Done when measured recall meets the agreed threshold for each query class, and failing classes have a documented tuning decision.
Measure latency and memory under concurrent load
The performance engineer replays a realistic mix of vector queries with filters at the concurrency production sees, then reads latency percentiles from
system.query_logand memory use from the server metrics. They run the same mix without the index to compare cost and confirm the index is worth its memory. They also watch memory during the index build and during peak query load. Done when latency and memory fall within targets the team set before the test, and the run finishes without out-of-memory errors or query failures.Roll the change to production replicas
Before the change, the DBA takes a full backup of the production table with BACKUP, and confirms that a restore of it has been tested on a spare cluster, since an untested backup may not restore when needed. The change then goes to one production replica, the team checks replication and query behavior, and the same DDL is applied across the remaining replicas. Done when
system.data_skipping_indiceslists the index on every replica, smoke queries pass, and the rollback plan, which is dropping the index, has been rehearsed.
Securing embeddings and vector queries in your cluster
Vector tables hold derived data that can still reveal the source documents, customers or events they were built from. The cards below cover the access, network, logging and resource controls to set before similarity search goes near production data.
Scope vector tables with roles
Grant access to embedding tables through roles and row policies, not direct per-user grants, so the same permissions apply to every application and analyst that runs a similarity query. Row policies can restrict which vectors a user can retrieve based on tenant, region or classification columns stored beside the embedding. Before go-live, test that a restricted user cannot read the vector column or infer neighbors outside their scope. The access control documentation describes the SQL workflow.
Replace the default user
The default user starts with all permissions, so a cluster exposed with that account unchanged gives any caller full read and write access to every vector table. Create named administrative users, assign least-privilege roles, and restrict or disable the default user before the cluster accepts traffic. Verify the change by connecting with a non-admin account and attempting a write to an embedding table.
Treat embeddings as sensitive
An embedding is derived data, but it can be inverted or matched closely enough to expose the text, images or records it came from. Classify embedding columns with the same label as their source data, apply the same retention and deletion rules, and decide whether they belong in backups and non-production copies. Confirm with your data protection owner whether embeddings of personal data count as personal data under your policies.
Limit HTTP and native exposure
ClickHouse serves the HTTP and native protocols on separate ports, and both can run queries against vector indexes. Bind listeners to internal interfaces, place application traffic behind a private network path, and allow only known source ranges to reach administrative ports. Enable TLS on any listener that carries credentials or query text. Check the configured listeners on each node, not only the load balancer in front of them.
Log similarity queries
Query logs show who ran a similarity search, which table they searched and how many rows were returned, which is the evidence needed for an incident review or an access investigation. Decide the query log retention period, mask any literal query vectors or filter values that carry personal data, and send logs to a store with restricted access. Confirm that the log settings capture the vector queries you care about in your own test cluster.
Cap expensive vector queries
Approximate search with large LIMIT values, broad filters or many concurrent callers can consume memory and CPU quickly. Apply quotas and settings profiles to the roles that run similarity search so a single caller cannot exhaust the cluster, and set per-query memory and time limits. Load test with the concurrency your application will generate, then tune the quotas against the results.
Running vector indexes beside replicated analytics
A vector index does not add a new durability model. Vector similarity indexes sit on the same ReplicatedMergeTree tables that hold your analytics data, so replicas, ClickHouse Keeper and the backup path all apply to the vector column and its index as they do to any other column. Keeper coordinates replica state for the table, and a recovered replica syncs missing data from a healthy replica without manual intervention, per the replication guide. Confirm with your own tests how the HNSW index structure is rebuilt or fetched on a recovered replica in your version, and measure how long a replica takes to become query-ready with the index present, since a large index can make recovery look slower than the data copy alone suggests.
Replication is not a backup. Replicas copy a bad delete, a bad update or a corrupted part to every node, so a mistaken DROP or a faulty embedding load removes the vectors everywhere at once. Protect the table with BACKUP and RESTORE to local disk, S3-compatible storage or Azure Blob Storage, and confirm with the backup documentation and your version which index data is included in a backup and whether it is restored or must be rebuilt after RESTORE. Treat that answer as a planning input: if the index rebuild is expensive, your recovery time objective must include it. The backup documentation also warns that a backup never test-restored will probably not restore properly, so schedule restores to a spare cluster and time them, including index rebuild and a query check against known nearest-neighbor results.
Upgrades need the same discipline as the analytics tables. Stable releases ship roughly monthly, and the production FAQ asks you to read the changelog before upgrading because minor backward-incompatible changes can occur. Vector index behavior, parameters and on-disk formats are areas where changes are plausible, so check the release notes for each version you move through and check the ClickHouse security policy for which lines still receive security updates. Run the upgrade on a pre-production environment loaded with representative vectors and filtered queries, compare recall and latency before and after, and keep a tested rollback path, a backup taken immediately before the change, and a written decision on which version you will stay on.
Limits to confirm before you commit to HNSW in ClickHouse
A vector index is only as useful as the conditions it runs under. The cards below are questions to put to ClickHouse documentation, to the release notes for your version, and to a test run on your own embeddings before the design is locked. Each one changes either what the index can do or what it costs to operate.
Supported distance functions and types
Confirm which distance metrics, such as L2 or cosine, and which element types the vector similarity index accepts in the version you plan to run. A query that uses a metric or type the index does not cover will fall back to a full scan, so the index looks correct in a demo and gives no speedup in production. Ask the vendor for the exact list for your release and check that your embedding model's output type matches it.
Index use under your filter shapes
Test whether index-assisted queries apply to the filters your application actually sends, including combinations of a vector search with equality, range and tenant predicates. Filters that the planner cannot push into the index can force the engine to evaluate many more rows than the nearest-neighbor limit suggests. Capture the query plan for each real filter pattern and record whether the index was used.
Update and delete behavior
Find out how inserts, updates and deletes affect indexed rows, and whether the index is rebuilt, merged or left stale for a period. Analytics tables often receive corrections and late-arriving data, so a vector index that degrades under churn needs a planned rebuild or a different ingestion pattern. Measure recall and latency after a realistic volume of changes, not only on a freshly loaded table.
Recall at your target latency
Approximate search trades recall for speed, and the right HNSW parameters depend on your data and your latency budget. Build a labeled sample of queries with known correct neighbors, then measure recall against exact search at several parameter settings. Agree on a minimum acceptable recall with the people who will use the results before choosing parameters.
Dimension and index size ceilings
Ask for the maximum supported vector dimension and any documented limits on index size per part or per table. Estimate memory from your row count and dimension, then confirm that the index fits on the nodes you will provision, with headroom for merges and replicas. Size the cluster against the largest dataset you expect within your retention window, not against today's table.
Behavior across version upgrades
Check whether an existing vector index needs to be rebuilt, remains readable, or changes its results when you move to a newer stable or LTS release. Read the changelog for the versions between your current and target release, and rehearse the upgrade on a copy of production data. Only versions listed as supported in the security policy receive security updates, so plan the upgrade path before you rely on the index.
What vector search in ClickHouse costs and needs from your team
What drives the cost of a ClickHouse vector search project?
The largest cost drivers are the number of vectors, their dimensions, how often data changes, and how many SQL filters must run alongside the similarity search. Each of these changes memory use, index build time and the hardware you need, so they belong in the written scope before any quote. Cost splits into the consultant's design and build effort, the infrastructure you pay for in your own cloud or data center, and your internal staff time for reviews and access approvals. Ask the quote to state which of these it covers, which items are one-time, and which recur, such as index rebuilds when embeddings change.
Can our internal team build ClickHouse vector search without outside help?
Yes, if your team already runs ClickHouse in production and has someone who can tune HNSW parameters, test recall against known queries and size memory for the index. The skills most often missing are embedding pipeline ownership, evaluation of search quality, and experience with replication and backups for a table that now holds a large index. Assess those gaps honestly before starting. A partner is most useful for a fixed-scope pilot that sets the parameters and produces a runbook your team can operate afterward.
How long does a vector search pilot take, and what sets that time?
The pilot length depends on data preparation, the number of embedding models under test, how many distinct filter patterns must be validated, and how quickly your security and platform teams approve access and infrastructure. Build time for the HNSW index also grows with data volume and the parameters chosen, so tests on representative data are needed before a date is committed. Ask for a timeline in the written scope that lists each dependency on your side, such as dataset access, environment provisioning and review windows. Do not accept a duration that is not tied to those inputs.
What data and access does a consultant need to start?
Expect to provide a representative sample of the embedding and metadata data, the SQL queries your applications will run with filters, the current ClickHouse version and cluster layout, and a pre-production environment with similar hardware. Access should be limited to a named service account with a role that can create tables and indexes in the test database, not the default user, which starts with all permissions according to the access control documentation. Agree on how sensitive fields are handled, masked or excluded before any data leaves your environment.
When is a separate vector database a better choice than ClickHouse?
A separate vector database may fit better when vector search is the main workload, when it needs features ClickHouse does not offer for your use case, or when the team that owns search already runs that database well. ClickHouse is strongest when similarity search must sit next to large analytical tables, joins and time-based filters on the same data. Compare both options on your own query mix, recall targets, update patterns and operating skills, and write down the result before choosing. The Qdrant pillar covers the dedicated alternative in more depth.
What ongoing work recurs after vector search goes live?
Recurring work includes reindexing when the embedding model or dimensions change, monitoring index memory and query latency, reviewing recall as data grows, and keeping ClickHouse on a supported version. The production FAQ says stable releases ship about monthly and LTS releases twice a year, so upgrades need a test cycle on a spare cluster. Backups must include the vector tables, and a restore test on a spare cluster should be repeated on a schedule. Ask for these tasks, their owner and their frequency in the handover document.
What should a written scope for ClickHouse vector search include?
A useful scope names the datasets, vector dimensions and embedding model, the expected query patterns with SQL filters, the recall and latency targets you will test against, the ClickHouse version and deployment target, and the backup and replication requirements. It should also list deliverables such as table designs, index parameters, test results, a runbook and training, plus what is out of scope. Ask the quote to state acceptance criteria in measurable terms that your own team agrees to, since DeployOpen does not publish a price list and quotes follow from the written scope.
How DeployOpen scopes and delivers ClickHouse vector search
DeployOpen plans, builds, tests and documents ClickHouse vector search inside infrastructure you control, from the first HNSW index definition to the runbooks your operators use. Engagements are fixed-scope, with scope, deliverables and timelines agreed in writing before work starts. The work sits next to the analytics ClickHouse already serves, so the design covers both sides.
How we scope
- We confirm your ClickHouse version and whether it is on a release that includes vector similarity indexes.
- We list the embedding dimensions, vector counts, filter columns and query latency targets the written scope must cover.
- We define which SQL filters must run alongside vector search and which datasets stay out of scope.
How we build
- We build the HNSW index definitions and parameter tests against a representative sample of your vectors and queries.
- We measure index build time, memory use and recall on the test set before proposing production settings.
- We wire vector tables into your replication, ingestion and access controls rather than treating them as a separate system.
How we hand over
- You receive tested table definitions, index settings, query examples and a record of the recall and memory results.
- We document the rebuild procedure for indexes after schema changes, node loss or a version upgrade.
- Operators receive a runbook covering monitoring of memory use, slow queries and index build progress on each node.
How we support
- Ongoing support, upgrades and continuous testing are available as a retainer after handover.
- We can re-test recall and latency when your embedding model, data volume or query patterns change.
- Ask any provider to confirm in writing which ClickHouse versions their support covers and how upgrades are tested first.
