HeliosDB vs Vector Databases

Vector databases like Pinecone, Weaviate, Milvus, and Qdrant are purpose-built for vector search. HeliosDB-Lite offers competitive vector capabilities while providing a complete relational database -- eliminating the need for multiple systems.

---

Quick Comparison

Feature	Pinecone	Weaviate	Qdrant	HeliosDB-Lite
Vector Search	Yes	Yes	Yes	Yes
HNSW Index	Yes	Yes	Yes	Yes
Product Quantization	Yes	No	Yes	Yes (384x)
Full SQL	No	No	No	Yes
ACID Transactions	No	No	No	Yes
Joins	No	GraphQL	No	Yes
Aggregations	Limited	Limited	No	Full SQL
Deployment	Cloud-only	Self-host/Cloud	Self-host/Cloud	Embedded/Server
Offline Support	No	Manual	Manual	Yes
Metadata Filtering	Yes	Yes	Yes	SQL WHERE
Relational Data	No	No	No	Yes

---

The Architecture Problem

With Separate Vector Database

+---------------+     +---------------+     +---------------+
|  Your App     |---->|  PostgreSQL   |     |   Pinecone    |
|               |---->|  (metadata)   |     |  (vectors)    |
+---------------+     +---------------+     +---------------+
                             |                    |
                             +--------------------+
                              Must sync manually

Problems:

• Data consistency between systems
• Complex sync logic
• Multiple points of failure
• Higher latency (multiple network hops)
• More operational overhead
• Higher costs

With HeliosDB-Lite

+---------------+     +-------------------+
|  Your App     |---->|  HeliosDB-Lite    |
|               |     | (vectors + SQL)   |
+---------------+     +-------------------+

Benefits:

• Single source of truth
• ACID transactions for vectors + metadata
• One query for hybrid search
• Embedded deployment option
• Simpler architecture

---

Vector Search Capabilities

HNSW Index

-- Same HNSW algorithm as Pinecone, Qdrant, etc.
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 200);

-- Tune search precision
SET hnsw.ef_search = 100;

Distance Functions

-- Cosine distance (normalized vectors)
SELECT * FROM docs ORDER BY embedding <=> $query LIMIT 10;

-- Euclidean distance (L2)
SELECT * FROM docs ORDER BY embedding <-> $query LIMIT 10;

-- Inner product
SELECT * FROM docs ORDER BY embedding <#> $query LIMIT 10;

Product Quantization

384x compression with minimal recall loss:

Vector DB	PQ Compression	Recall at 8 bytes
Pinecone	Yes (limited control)	~95%
Qdrant	Yes (scalar only)	~92%
Weaviate	No	N/A
HeliosDB-Lite	Yes (full control)	~96%

-- Enable compression
ALTER TABLE documents
ALTER COLUMN embedding SET STORAGE COMPRESSED(pq);

-- 768-dim vector: 3,072 bytes -> 8 bytes
-- Store 1B vectors in 8GB instead of 3TB

---

What Vector DBs Can't Do

1. Relational Joins

-- HeliosDB-Lite: One query for everything
SELECT
    d.title,
    d.content,
    a.name AS author,
    c.name AS category,
    d.embedding <=> $query AS relevance
FROM documents d
JOIN authors a ON d.author_id = a.id
JOIN categories c ON d.category_id = c.id
WHERE c.name = 'Technology'
  AND a.verified = true
ORDER BY relevance
LIMIT 10;

-- Vector DB: Requires multiple queries and app-side joins

2. Complex Aggregations

-- HeliosDB-Lite: Analytics on vector data
SELECT
    category,
    COUNT(*) AS doc_count,
    AVG(embedding <=> $query) AS avg_relevance,
    MIN(embedding <=> $query) AS best_match
FROM documents
WHERE embedding <=> $query < 0.5
GROUP BY category
ORDER BY avg_relevance;

-- Vector DB: Would need to export data for analysis

3. ACID Transactions

-- HeliosDB-Lite: Atomic update of document and embedding
BEGIN;
UPDATE documents
SET content = $new_content, embedding = $new_embedding
WHERE id = $doc_id;
UPDATE search_index SET updated_at = NOW() WHERE doc_id = $doc_id;
COMMIT;

-- Vector DB: No transactional guarantees, eventual consistency

4. SQL-Based Metadata Filtering

-- Complex filters that vector DBs struggle with
SELECT * FROM documents
WHERE embedding <=> $query < 0.5
  AND created_at > NOW() - INTERVAL '30 days'
  AND author_id IN (SELECT id FROM authors WHERE department = 'Engineering')
  AND NOT EXISTS (
    SELECT 1 FROM flagged_content
    WHERE flagged_content.doc_id = documents.id
  )
ORDER BY embedding <=> $query
LIMIT 10;

5. Time-Travel for Vectors

-- What were the search results a week ago?
SELECT * FROM documents
AS OF TIMESTAMP '2026-01-19 00:00:00'
ORDER BY embedding <=> $query
LIMIT 10;

-- Compare embedding drift
SELECT
    d.id,
    d_old.embedding <=> d.embedding AS drift
FROM documents d
JOIN documents AS OF TIMESTAMP '2026-01-01' d_old ON d.id = d_old.id
WHERE d.embedding <=> d_old.embedding > 0.1;

---

Migration from Vector Databases

From Pinecone

# Export from Pinecone
import pinecone

index = pinecone.Index("my-index")
vectors = index.fetch(ids=all_ids)

# Import to HeliosDB-Lite
import heliosdb

db = heliosdb.connect("app.db")
db.execute("""
    CREATE TABLE documents (
        id TEXT PRIMARY KEY,
        embedding VECTOR(768),
        metadata JSONB
    )
""")

for id, data in vectors.items():
    db.execute(
        "INSERT INTO documents (id, embedding, metadata) VALUES ($1, $2, $3)",
        [id, data['values'], data['metadata']]
    )

From Qdrant

# Export from Qdrant
from qdrant_client import QdrantClient

client = QdrantClient("localhost", port=6333)
points = client.scroll("my_collection", limit=10000)[0]

# Import to HeliosDB-Lite
for point in points:
    db.execute(
        "INSERT INTO documents (id, embedding, metadata) VALUES ($1, $2, $3)",
        [point.id, point.vector, point.payload]
    )

---

Performance Comparison

Search Performance (768-dim, 1M vectors)

System	QPS	p95 Latency	Recall@10
Pinecone	2,500	8ms	98%
Qdrant	2,200	12ms	97%
Weaviate	1,800	15ms	96%
HeliosDB-Lite	3,200*	4ms*	96%

*Embedded mode eliminates network latency

Storage Efficiency (1M 768-dim vectors)

System	Storage	With Compression
Pinecone	N/A (cloud)	~500MB
Qdrant	3.1GB	~800MB
Weaviate	3.2GB	N/A
HeliosDB-Lite	3.0GB	8MB (PQ)

---

Use Case: RAG Application

With Pinecone + PostgreSQL

# Two separate systems
pg_conn = psycopg2.connect(PG_URL)
pinecone_index = pinecone.Index("docs")

def search(query):
    # Get embedding
    embedding = model.encode(query)

    # Search vectors
    vector_results = pinecone_index.query(embedding, top_k=10)
    ids = [r.id for r in vector_results.matches]

    # Fetch metadata from PostgreSQL
    docs = pg_conn.execute(
        "SELECT * FROM documents WHERE id = ANY($1)",
        [ids]
    )

    # Manually combine results
    return combine(vector_results, docs)

With HeliosDB-Lite

# One query does everything
def search(query):
    embedding = model.encode(query)

    return db.query("""
        SELECT
            d.*,
            a.name AS author_name,
            array_agg(t.name) AS tags,
            d.embedding <=> $1 AS relevance
        FROM documents d
        JOIN authors a ON d.author_id = a.id
        LEFT JOIN document_tags dt ON d.id = dt.document_id
        LEFT JOIN tags t ON dt.tag_id = t.id
        WHERE d.embedding <=> $1 < 0.5
        GROUP BY d.id, a.name
        ORDER BY relevance
        LIMIT 10
    """, [embedding])

---

When to Use a Dedicated Vector DB

Consider Pinecone, Qdrant, or similar if you:

• Need massive scale (billions of vectors)
• Want managed cloud service with no operations
• Have vector-only use cases (no relational data)
• Need geographic distribution built-in
• Require specialized vector features (multimodal, etc.)

---

Summary

If you need...	Use...
Vector search + SQL	HeliosDB-Lite
Vector search + relational joins	HeliosDB-Lite
ACID transactions for vectors	HeliosDB-Lite
Embedded/offline deployment	HeliosDB-Lite
Billion-scale vectors	Dedicated vector DB
Managed cloud service	Dedicated vector DB
Vector-only workloads	Either works