“

Series: S13 AI Engineering · Article 4 | Level: Advanced | Reading Time: 18 min

Doris HNSW Vector Search: Vector Retrieval in an Analytical Database

#TL;DR

Apache Doris 2.1+ natively supports HNSW vector indexes, enabling enterprises to perform vector retrieval and traditional OLAP analytics in the same database without maintaining a separate vector database. This article details the HNSW algorithm, vector table design in Doris, index parameter tuning, hybrid query optimization (vector + scalar filtering), and performance comparisons with standalone vector databases.

#1. Why Vector Search in Doris

#1.1 The Value of Unified Storage

Traditional RAG architectures require two storage systems -- a vector database (Milvus, Pinecone) for semantic retrieval and an OLAP database (Doris) for structured analytics. This creates triple problems: data consistency, operational complexity, and cost.

Doris's vector search capability lets enterprises support with a single system:

Semantic retrieval: Document search based on vector similarity
Structured filtering: Precise filtering based on metadata
Aggregation analytics: Statistical analysis of retrieval results
Real-time updates: Consistency between real-time writes and retrieval

#1.2 When to Choose Doris vs Dedicated Vector DB

Scenario	Vector DB	Doris Vector Search	Recommendation
Pure vector retrieval, 100M+ scale	Optimal	Viable	Vector DB
Vector + complex scalar filtering	Limited	Optimal	Doris
Vector retrieval + OLAP analytics	Needs two systems	Native	Doris
Sub-10M scale, simplified arch	Overkill	Optimal	Doris

#2. HNSW Algorithm Deep Dive

#2.1 Hierarchical Navigable Small World Graph

HNSW (Hierarchical Navigable Small World) is the most popular approximate nearest neighbor (ANN) search algorithm. Its core idea is building a multi-layer skip-list-like graph index:

Code

Layer 3:  [A] ---------------------- [F]
Layer 2:  [A] ---- [C] ------------ [F] ---- [H]
Layer 1:  [A] - [B] - [C] - [D] - [F] - [G] - [H]
Layer 0:  [A] [B] [C] [D] [E] [F] [G] [H] [I] [J]

Search starts from the highest layer, performs greedy search at each layer, descends layer by layer, and finally finds nearest neighbors at the bottom layer.

#2.2 Core Parameters

Parameter	Description	Recommended Range
`M`	Max connections per node	12-48 (default 16)
`ef_construction`	Build-time search width	100-500 (must >= 2*M)
`ef_search`	Query-time search width	64-512 (must >= top_k)

Higher M: better recall, slower build, more memory
Higher ef_construction: better index quality, slower build
Higher ef_search: better recall, slower query (tunable at query time)

#2.3 Performance Characteristics

Dataset Size	Build Time	Memory	Recall@10	QPS
1M (128d)	~5 min	~1.5 GB	98.5%	~2000
10M (128d)	~60 min	~15 GB	97.8%	~1500
100M (128d)	~12 hrs	~150 GB	96.5%	~800

#3. Vector Table Design in Doris

#3.1 Creating a Vector Table

SQL

CREATE TABLE knowledge_base (
    doc_id VARCHAR(64) NOT NULL,
    chunk_id INT NOT NULL,
    content TEXT,
    embedding ARRAY<FLOAT> NOT NULL,
    source VARCHAR(256),
    category VARCHAR(64),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_embedding (embedding) USING INVERTED
        PROPERTIES(
            "index_type" = "hnsw",
            "dim" = "768",
            "metric_type" = "cosine",
            "M" = "16",
            "ef_construction" = "200"
        )
) ENGINE=OLAP
DUPLICATE KEY(doc_id, chunk_id)
DISTRIBUTED BY HASH(doc_id) BUCKETS 16;

#3.2 Inserting Vector Data

SQL

INSERT INTO knowledge_base (doc_id, chunk_id, content, embedding, source, category)
VALUES (
    'doc-001', 1,
    'Apache Doris is a modern MPP analytical database...',
    [0.023, -0.156, 0.089, ...],  -- 768-dimensional embedding
    'technical-docs',
    'database'
);

#3.3 Vector Search Queries

SQL

-- Basic vector search
SELECT doc_id, chunk_id, content,
       cosine_distance(embedding, [0.031, -0.142, ...]) AS distance
FROM knowledge_base
ORDER BY distance ASC
LIMIT 10;

-- Hybrid search: vector + scalar filtering
SELECT doc_id, content,
       cosine_distance(embedding, [0.031, -0.142, ...]) AS distance
FROM knowledge_base
WHERE category = 'database'
  AND created_at > '2025-01-01'
ORDER BY distance ASC
LIMIT 10;

#4. Hybrid Query Optimization

#4.1 Pre-Filter vs Post-Filter

SQL

-- Pre-filter (recommended): scalar filter first, then vector search
-- More efficient when scalar filter is highly selective
SELECT doc_id, content, cosine_distance(embedding, ?) AS dist
FROM knowledge_base
WHERE category = 'database'  -- Scalar filter applied first
ORDER BY dist ASC
LIMIT 10;

-- Post-filter: vector search first, then scalar filter
-- Better when scalar filter is not very selective
SELECT * FROM (
    SELECT doc_id, content, category,
           cosine_distance(embedding, ?) AS dist
    FROM knowledge_base
    ORDER BY dist ASC
    LIMIT 100  -- Retrieve more candidates
) t
WHERE category = 'database'
LIMIT 10;

#4.2 Performance Tuning for Hybrid Queries

SQL

-- Adjust ef_search at query time for recall/speed tradeoff
SET session.hnsw_ef_search = 256;  -- Higher for better recall

-- Use BITMAP index on scalar columns for faster filtering
ALTER TABLE knowledge_base
ADD INDEX idx_category (category) USING BITMAP;

#5. Integration with coomia-dip RAG Pipeline

Python

from ontology_sdk import OntoPlatform

platform = OntoPlatform(base_url="http://localhost:8080", token="admin-token")

# Register vector-enabled object type
platform.ontology.create_object_type(
    name="KnowledgeChunk",
    properties={
        "chunkId": {"type": "STRING", "primary_key": True},
        "content": {"type": "STRING"},
        "embedding": {"type": "VECTOR", "dimension": 768},
        "source": {"type": "STRING"},
        "category": {"type": "STRING"},
    },
    vector_config={
        "embedding_field": "embedding",
        "index_type": "HNSW",
        "metric_type": "COSINE",
        "M": 16,
        "ef_construction": 200,
    },
)

# Semantic search via OQL
results = platform.oql.execute("""
    SELECT chunkId, content, VECTOR_DISTANCE(embedding, $1) AS score
    FROM KnowledgeChunk
    WHERE category = 'technical'
    ORDER BY score ASC
    LIMIT 5
""", params=[query_embedding])

#6. Benchmarks: Doris vs Standalone Vector DB

Metric	Milvus 2.3	Doris 2.1 HNSW	Notes
Pure vector QPS (1M)	~3000	~2000	Milvus optimized for pure vector
Hybrid query QPS	~500	~1500	Doris excels at hybrid queries
Insert throughput	~10K/s	~50K/s	Doris batch write is faster
Storage efficiency	1x	0.7x	Doris columnar compression
Ops complexity	High (separate system)	Low (unified)	Doris is already there

#7. Production Best Practices

Dimension selection: 768d (BERT) or 1536d (OpenAI) most common; avoid > 2048d
Normalize vectors: Use cosine similarity with L2-normalized vectors
Batch ingestion: Use Stream Load for bulk vector data import
Monitor index build: HNSW index build is CPU-intensive; schedule during off-peak
Tune ef_search per query: Lower for speed-critical, higher for recall-critical
Partition by time: If data has temporal dimension, partition for efficient pruning

#Summary

This article covered vector search capabilities in Apache Doris: HNSW algorithm fundamentals, vector table design, hybrid query optimization, integration with coomia-dip RAG pipelines, and performance benchmarks. For enterprises already using Doris as their OLAP database, adding vector search eliminates the need for a separate vector database, simplifying architecture while maintaining competitive performance.

Next: [S13-05] Incremental Indexing & Real-Time Vector Updates Previous: [S13-03] Embedding Pipeline Architecture