On 25 June 2026 I spent the morning of RoachFest London 2026 in a hands-on session titled âVector Search, Transactional & RAG on CockroachDBâ, part of Cockroach Labsâ âAI Workshopsâ series. My main RoachFest recap covers the day and the keynotes, and mentions this workshop in one section. This post goes deeper into the part I found most useful as a platform engineer: how CockroachDBâs own vector index, C-SPANN, compares with the HNSW and IVFFlat indexes you get from pgvector.
One framing note before the details. Everything below comes from the workshopâs slides and lab screens, which I photographed from the room, plus the public documentation I checked afterwards. The latency numbers shown in the workshop UI come from an interactive simulation, not from a benchmark I ran.
The Problem: Dual-Database Architectures
The workshop opened with a problem statement on its slides: dual-database architectures create consistency gaps for AI applications. The usual pattern is a relational database for the application data plus a separate vector store for embeddings, glued together by an embedding pipeline. The slide pointed at three symptoms: similarity search without relational context, vector-only workloads, and managed embedding pipelines. The question to ask of your own stack was written on the slide too: do your vectors reference transactional data?
If they do, you are keeping two systems in agreement. The workshop returned to that later with a crash demo (see below). It is the same trade-off I covered in vector databases on Kubernetes: a dedicated vector store can be fast, but you take on a consistency boundary.
Why C-SPANN Instead of HNSW or IVFFlat
The âAlgorithmâ section of the lab, titled âWhy CockroachDB built C-SPANN instead of using HNSW or IVFFlatâ, put the three approaches side by side.
The workshopâs algorithm comparison: HNSW as a multi-layer navigable graph, IVFFlat as k-means clusters, and C-SPANN as k-means partitions aligned with CockroachDB ranges.
- HNSW: a multi-layer navigable graph, described on the slide as having excellent recall on a single node.
- IVFFlat: k-means clusters with brute-force search inside each cluster, described as simple with lower memory use.
- C-SPANN: k-means partitions aligned with CockroachDB ranges, âbuilt for distributedâ.
HNSW and IVFFlat are the two index types in pgvector, the open-source Postgres extension. Its README describes HNSW as the one with better query performance but slower builds and higher memory use, and IVFFlat as the one with faster builds and lower memory use, at the cost of query performance.
The comparison table further down the same screen is where the distributed argument showed up. As I read it:
| HNSW | IVFFlat | C-SPANN | |
|---|---|---|---|
| Single-node recall | 95%+ | 85-90% | 90-95% |
| Distributed recall | degrades | degrades | maintained |
| Memory | high (in-memory graph) | low (centroids only) | low (centroids only) |
| Cross-node hops | many (graph traversal) | some (split cells) | zero (range-aligned) |
Take those figures as the workshop slideâs claims, read off a photo, rather than as an independent benchmark. Cockroach Labsâ own write-up describes C-SPANN as adapting Microsoftâs SPANN and SPFresh algorithms to CockroachDBâs distributed architecture, with a hierarchical k-means tree whose partitions are stored in the key-value layer (Cockroach Labs blog: Real-Time Indexing for Billions of Vectors, Introducing Distributed Vector Indexing). It arrived with CockroachDB 25.2.
The lab step that introduces C-SPANN and the index-tuning exercise.
The Simulated Latency Demo
The same lab screen includes a small simulation of a multi-region search. In it, an HNSW trace made three cross-region hops and took 294.6 ms, while the C-SPANN trace stayed local with zero cross-region hops and took 3.7 ms. The screen then showed âC-SPANN is 291ms faster (99% improvement)â.
An illustration, not a benchmark: the labâs multi-region trace compares an HNSW graph walk across regions with C-SPANN searching partitions locally.
My take: the point of the demo is the shape of the argument, not the millisecond values. A graph index that wanders across regions pays for every hop, while a partitioned index that lives next to its data does not. That is a fair argument for a distributed database. It says little about a single Postgres node running pgvector, where HNSWâs graph sits in one machineâs memory and the cross-region problem does not exist.
What the Docs Say
I checked the public docs after the session. CockroachDBâs vector index documentation describes CREATE VECTOR INDEX and supports three operators: L2 distance (<->), cosine distance (<=>), which suits semantic similarity and RAG, and negative inner product (<#>). It also describes prefix columns, which pre-filter the search space and are used only when every prefix column is constrained to a specific value in the query. That is the feature the multi-tenant lab relied on. The page also lists limitations, including that large batch inserts can degrade performance and that IMPORT INTO is unsupported on indexed tables. That page describes the index as organising vectors with k-means clustering into hierarchical partitions and does not use the name C-SPANN, which comes from the workshop and Cockroach Labsâ blog.
On the pgvector side, the README notes that with approximate indexes âfiltering is applied after the index is scannedâ, so a filtered query can return fewer rows than expected unless iterative index scans are enabled. pgvector keeps what you expect from Postgres: ACID compliance, point-in-time recovery and joins.
Orphaned Vectors: The Dual-Database Rebuttal
The most memorable lab was the transactional ingestion exercise. Each document is split into chunks and embedded, and a successful run should leave exactly three chunks per document. The lab then ran a crash demo comparing two architectures.
The âTransaction Crash Comparisonâ screen: dual database on the left, CockroachDB on the right.
According to the lab instructions on screen: in the dual-database case, the vector database accepted two chunks before the crash while the relational database had no document record, leaving two orphaned vectors and queries that may return stale or meaningless results. In the CockroachDB case, the ROLLBACK removed everything atomically, with zero orphaned vectors. Verifying in the SQL shell returned zero rows for the failed document.
My take: this is a rebuttal to the dual-database pattern (relational database plus separate vector store), not to pgvector. A single Postgres instance with pgvector gets the same atomicity for free, because the vectors live in the same transactional store. The fair comparison for pgvector is scale and operations: how far one Postgres primary takes you, and what you do when you need multi-region writes and survivability. That is the gap CockroachDB is aiming at, and it is why the migration lab below mattered to me.
Migrating Off Postgres Plus Pinecone
A later lab, âMigration to CockroachDBâ, walked through moving from PostgreSQL plus Pinecone to a single converged system.
The migration lab: a dual-write phase where new inserts go to both systems.
The step on screen was a dual-write phase: new inserts go to both PostgreSQL plus Pinecone and CockroachDB, a changefeed on CockroachDB watches for any drift, and old data still lives only in the legacy system. It is a standard expand-and-contract migration, and the same approach applies to any AI-assisted database migration. Run the dual-write phase long enough to trust the drift check before cutting over.
Multi-Tenant Retrieval in One SQL Query
The last lab covered a multi-tenant RAG pipeline. The instructions on screen describe it as combining vector similarity search with relational filtering in a single SQL query, with no need for separate vector indexes per tenant. The steps were to search as tenant âacmeâ, run the same query for a different tenant, verify that tenants cannot see each otherâs results, filter by source, and visualise tenant isolation. The terminal showed embeddings generated locally with Ollama (nomic-embed-text, 768 dimensions) and the closest chunks returned with their distances.
Searching as tenant âacmeâ: ranked chunks with their distances, filtered by tenant.
This is where prefix columns earn their place. The tenant becomes a prefix column on the vector index, so a query constrained to one tenant searches only that tenantâs slice of the index. Compare that with pgvector, where a filtered query on an approximate index filters after the scan unless you turn on iterative scans, and where per-tenant isolation usually means partitioning, separate indexes or row-level security.
My Takeaways
- Pick the index for the topology. On one Postgres node, pgvector with HNSW is simple and strong. The C-SPANN argument starts when your data is spread across nodes or regions.
- The consistency argument is about two systems, not about pgvector. If your vectors and rows live in separate stores, you need a story for orphaned vectors.
- Filters are part of the index design. Whether tenant filters are prefix columns or post-scan predicates decides how your multi-tenant RAG behaves.
- Treat vendor numbers as illustrations. The 291 ms figure was a simulation. Benchmark with your own embeddings and recall target before choosing.
