Getting Started with CockroachDB HNSW Vector Search: A 5-Minute Guide

TL;DR: CockroachDB now supports vector search directly within its distributed SQL engine using HNSW (Hierarchical Navigable Small World) indexes. This guide provides a quick "Hello World" SQL setup for cosine distance indexing and explores practical enterprise use cases where distributed vector search excels.

---

Why Vector Search in CockroachDB?

Until recently, building AI-powered applications meant running a dual-database architecture: a relational database (PostgreSQL, MySQL, CockroachDB) for transactional app data, and a separate vector database (Pinecone, Qdrant, Milvus) for embeddings.

This architecture introduces synchronization lag, dual-write failure risks, complex ETL pipelines, and fragmented governance.

CockroachDB solves this by integrating pgvector-compatible vector search natively into its distributed, multi-region SQL database. You get ACID transactions, global resilience, and horizontal scaling alongside fast Approximate Nearest Neighbor (ANN) vector queries.

---

Core Concepts in 60 Seconds

1. VECTOR(d) Data Type: Stores float arrays representing high-dimensional embeddings (e.g., 1,536 dimensions from OpenAI text-embedding-3-small or 768 dimensions from Google Gemini embeddings).
2. HNSW Index: Hierarchical Navigable Small World is a graph-based indexing algorithm that organizes vectors into multi-layer graphs. It provides sub-linear query time with ultra-fast nearest-neighbor lookups.
3. Cosine Distance (vector_cosine_ops): Measures the angular distance between vectors regardless of magnitude (normalized range $0$ to $2$). In CockroachDB SQL, cosine distance uses the <=> operator (where $0$ indicates identical direction).

---

Hello World: Step-by-Step Example

Let's set up a simple product recommendation system based on 3-dimensional embeddings.

Step 1: Create the Table

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name STRING NOT NULL,
    category STRING NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    embedding VECTOR(3) NOT NULL
);

Step 2: Create the HNSW Cosine Index

Build an HNSW index specifically tuned for cosine distance queries:

CREATE INDEX idx_products_embedding_hnsw 
ON products 
USING hnsw (embedding vector_cosine_ops);

Note: CockroachDB automatically constructs the HNSW multi-layer graph for fast similarity retrieval.

Step 3: Insert Sample Embeddings

Insert products with simulated normalized 3D vectors:

INSERT INTO products (name, category, price, embedding) VALUES
    ('Wireless Noise-Canceling Headphones', 'Electronics', 299.99, '[0.91, 0.38, 0.17]'),
    ('Bluetooth Ergonomic Earbuds',       'Electronics', 129.99, '[0.88, 0.42, 0.21]'),
    ('Mechanical Gaming Keyboard',       'Electronics', 159.99, '[0.45, 0.85, 0.26]'),
    ('Ergonomic Mesh Office Chair',      'Furniture',   349.00, '[0.12, 0.31, 0.94]'),
    ('Standing Adjustable Desk',         'Furniture',   499.00, '[0.15, 0.28, 0.95]');

Step 4: Query Top-K Nearest Neighbors

To search for products similar to a query vector [0.90, 0.40, 0.18] (e.g., an audio gear search query):

SELECT 
    name, 
    category, 
    price,
    embedding <=> '[0.90, 0.40, 0.18]' AS cosine_distance
FROM products
ORDER BY embedding <=> '[0.90, 0.40, 0.18]'
LIMIT 3;

Expected Output:

name	category	price	cosine_distance
Wireless Noise-Canceling Headphones	Electronics	299.99	0.0003
Bluetooth Ergonomic Earbuds	Electronics	129.99	0.0012
Mechanical Gaming Keyboard	Electronics	159.99	0.1341

The <=> operator computes the cosine distance. Ordering by cosine distance ascending yields the most semantically relevant items first!

---

Practical Real-World Applications

1. Unified Hybrid Search (SQL Filtering + Vector Similarity)

In pure vector databases, filtering by structured attributes (e.g., price < 200 AND category = 'Electronics') is inefficient or requires complex pre/post-filtering.

With CockroachDB, you can run single-query hybrid searches backed by ACID transactions:

SELECT id, name, price, embedding <=> '[0.90, 0.40, 0.18]' AS distance
FROM products
WHERE category = 'Electronics' AND price <= 200.00
ORDER BY embedding <=> '[0.90, 0.40, 0.18]'
LIMIT 5;

2. Multi-Tenant Enterprise RAG (Retrieval-Augmented Generation)

For AI SaaS products serving thousands of enterprise clients, data isolation is crucial. CockroachDB allows you to store document chunks and embeddings alongside tenant metadata in partitioned tables:

SELECT chunk_text, document_id
FROM knowledge_base_chunks
WHERE tenant_id = 'tenant_12345'
ORDER BY chunk_embedding <=> $query_embedding
LIMIT 5;

This guarantees strict tenant data boundary isolation while leveraging CockroachDB's distributed horizontal scalability.

3. Real-Time Fraud & Anomaly Detection

Financial institutions generate embeddings for user transactions or access patterns. By comparing incoming events against known fraud vector clusters using HNSW cosine distance: - Match distance $< 0.05$ flags instant high confidence anomalies. - Transaction processing occurs in the same distributed database handling account balances, preventing split-brain inconsistencies.

4. Multimodal & Semantic Media Search

Search product catalogs, audio clips, image embeddings (CLIP), or code repos without exact keyword matches. Cosine similarity focuses on directional alignment of embedding spaces, making it ideal for normalized semantic vectors produced by modern LLMs and vision models.

---

Key Takeaways

• HNSW + Cosine Distance (<=>) delivers high-throughput, low-latency similarity search for normalized AI embeddings.
• No separate vector DB needed: Consolidate transactional state (Postgres/CockroachDB SQL) and vector embeddings into one unified storage engine.
• Distributed & Resilient: Scales across multiple regions and nodes seamlessly, with zero-downtime schema changes and multi-master fault tolerance.

Get started by trying vector queries in CockroachDB v24.2+ or CockroachDB Cloud!