Why SQL/NoSQL don't work for semantic search

Capsule overview

SQL and NoSQL are excellent at what they were designed for: SQL for structured queries (WHERE age > 30), NoSQL for scalable documents. But neither was designed for geometric similarity search in high-dimensional spaces, which is what RAG needs.

This capsule explains why using SQL/NoSQL for semantic search is like using a hammer to drive a screw: technically possible (with extensions), but suboptimal. You'll understand the architectural limitations of SQL/NoSQL and why dedicated vector databases exist.

---

SQL: Designed for tables, not vectors

The fundamental problem

SQL was designed for:

SELECT name, age FROM users WHERE age > 30 AND city = 'NYC';

What you need for RAG:

SELECT doc_id FROM embeddings 
WHERE cosine_similarity(vector, query_vector) > 0.8 
ORDER BY similarity DESC LIMIT 5;

Problem: SQL has no native cosine_similarity operator. It doesn't understand "vector geometry." It only understands comparisons (>, <, =, LIKE).

---

Postgres with pgvector: A useful but limited extension

What is pgvector?

pgvector is a Postgres extension that adds vector support:

CREATE EXTENSION vector;

CREATE TABLE embeddings (
  id SERIAL PRIMARY KEY,
  doc_text TEXT,
  embedding vector(1536)  -- 'vector' type added by pgvector
);

-- Similarity search
SELECT doc_text 
FROM embeddings 
ORDER BY embedding <-> '[0.1, 0.2, ...]'::vector 
LIMIT 5;

Supported operators:

• <-> : L2 distance (euclidean)
• <#> : Negative inner product
• <=> : Cosine distance

---

pgvector limitations

1. No HNSW until a recent version (2023)

pgvector pre-0.5.0 (May 2023) only had IVFFlat (Inverted File Index):

• Latency: ~500ms for 1M vectors
• vs ChromaDB with HNSW: ~15ms
• 30x slower

2. HNSW available since v0.5.0 (May 2023)

pgvector now supports HNSW:

CREATE INDEX ON embeddings USING hnsw (embedding vector_cosine_ops);

But:

• pgvector's HNSW implementation is slower than dedicated vector DBs
• Postgres isn't optimized for vector operations (SQL overhead)
• Multiple concurrent queries degrade performance

Benchmark (1M vectors, 1536D):

pgvector + HNSW:    50-80ms   (best case)
ChromaDB + HNSW:    10-20ms
Pinecone:           5-15ms

Conclusion: pgvector is better than brute force, but worse than dedicated vector DBs.

---

When pgvector IS enough

Valid cases:

• You already use Postgres for everything (you don't want to add another DB)
• <100K vectors (pgvector is enough)
• You don't need <50ms latency (50-80ms is acceptable)
• The team has more experience with SQL than with new DBs

Example: A startup with 20K docs, existing Postgres, SQL-first junior team.

• pgvector: 20-30ms retrieval ✅ Acceptable
• ChromaDB: 5-10ms ✅ Better but requires learning a new DB
• Valid trade-off: Simplicity (pgvector) vs performance (ChromaDB)

---

NoSQL: Designed for documents, not geometry

MongoDB with vector search

MongoDB Atlas (cloud) has Vector Search since 2023:

db.collection.createIndex({
  embedding: "vectorSearch"
}, {
  vectorOptions: {
    dimensions: 1536,
    similarity: "cosine"
  }
});

db.collection.aggregate([
  {
    $vectorSearch: {
      queryVector: [0.1, 0.2, ...],
      path: "embedding",
      numCandidates: 100,
      limit: 5
    }
  }
]);

---

MongoDB vector search limitations

1. Atlas only (cloud)

• Not available in MongoDB self-hosted (Community Edition)
• Requires Atlas (managed cloud) → Additional cost

2. Recent HNSW implementation (2023)

• Less mature than ChromaDB/Pinecone (2020-2021)
• Fewer public benchmarks

3. MongoDB overhead

• MongoDB is optimized for documents, not vectors
• JSON serialization/deserialization overhead
• Lower performance vs dedicated vector DBs

Benchmark (100K vectors, 1536D - MongoDB Atlas):

MongoDB Vector Search:  80-120ms
ChromaDB:               10-20ms

Conclusion: MongoDB Vector Search is convenient if you already use MongoDB, but it's not optimal for pure RAG.

---

Elasticsearch with dense vectors

Elasticsearch supports dense vectors:

PUT /documents
{
  "mappings": {
    "properties": {
      "embedding": {
        "type": "dense_vector",
        "dims": 1536,
        "index": true,
        "similarity": "cosine"
      }
    }
  }
}

POST /documents/_search
{
  "knn": {
    "field": "embedding",
    "query_vector": [0.1, 0.2, ...],
    "k": 5,
    "num_candidates": 100
  }
}

---

Elasticsearch limitations

1. Hybrid, not dedicated

• Elasticsearch is a search engine (keyword search first, vectors second)
• Optimized for text, not pure geometry

2. HNSW since v8.0 (2022)

• Less optimized implementation than dedicated vector DBs
• Complex setup (cluster, shards, replicas)

3. Elasticsearch overhead

• Inverted index + vector index = more memory
• Lucene overhead (designed for text, adapted for vectors)

Benchmark (500K vectors, 1536D):

Elasticsearch + HNSW:  100-200ms
ChromaDB:              15-30ms

Conclusion: Elasticsearch is excellent for hybrid search (keyword + semantic), but it's not optimal if you only need semantic search.

---

Comparison: SQL vs NoSQL vs Vector DB

Comparison table (1M vectors, 1536D)

Feature	Postgres + pgvector	MongoDB Atlas	Elasticsearch	ChromaDB	Pinecone
Latency	50-80ms	80-120ms	100-200ms	10-20ms	5-15ms
Setup	Extension install	Cloud only	Cluster setup	pip install	API key
HNSW	Since v0.5 (2023)	Yes (2023)	Since v8 (2022)	Native	Native
Self-hosted	✅ Yes	❌ No	✅ Yes	✅ Yes	❌ No
Cost (self)	$0 (Postgres)	N/A	$0 (OSS)	$0 (OSS)	N/A
Cost (cloud)	$50-200/mo	$60-300/mo	$100-500/mo	$0 (self)	$70+/mo
Optimization	SQL-first	Docs-first	Text-first	Vector-first	Vector-first

---

When to use each one?

Use Postgres + pgvector IF:

• ✅ You already use Postgres (you don't want another DB)
• ✅ <100K vectors (pgvector is enough)
• ✅ SQL-first team (they don't want to learn a new API)
• ✅ 50-80ms latency is acceptable

Use MongoDB Vector Search IF:

• ✅ You already use MongoDB Atlas
• ✅ You need documents + vectors in the same place
• ✅ The Atlas cost is already justified

Use Elasticsearch IF:

• ✅ You need hybrid search (keyword + semantic)
• ✅ You already use Elasticsearch for logs/text search
• ✅ 100-200ms latency is acceptable

Use a dedicated Vector DB (ChromaDB/Pinecone) IF:

• ✅ >100K vectors (scale)
• ✅ <50ms latency is critical (performance)
• ✅ Vector search is your main use case (not hybrid)
• ✅ You want best-in-class for vectors

---

Why dedicated vector databases exist

The architecture matters

SQL/NoSQL adapted for vectors:

[SQL Engine] → [Extension: pgvector] → [HNSW Index] → [Vectors]
     ↑
   Overhead (tables, transactions, ACID)

Dedicated vector DB:

[Vector Engine] → [HNSW Index] → [Vectors]
     ↑
   Zero overhead (designed only for vectors)

Result:

• Vector DB: 3-5x faster than SQL/NoSQL with extensions
• Vector DB: Less memory (no SQL overhead)
• Vector DB: Simpler API (native vector operations)

---

Summary

What you learned:

1. ✅ SQL doesn't understand vectors: Designed for tables, not high-dimensional geometry
2. ✅ pgvector is useful but limited: 50-80ms vs ChromaDB's 10-20ms (3-4x slower)
3. ✅ NoSQL (MongoDB, Elastic) are hybrids: Good for hybrid search, suboptimal for vector-only
4. ✅ Dedicated vector DBs are 3-5x faster: Architecture optimized for vectors only
5. ✅ Valid trade-off: Simplicity (existing SQL/NoSQL) vs performance (dedicated vector DB)

Why it matters:

• If your RAG has >100K docs and you need <50ms, SQL/NoSQL aren't enough
• pgvector is a reasonable compromise for small projects (<100K vectors)
• Dedicated vector DBs are optimal for production-ready RAG (>100K docs, <50ms)

Next capsule: Now that you understand why SQL/NoSQL aren't optimal, you'll see why numpy/pandas don't scale to production either.

---

Additional resources

1. pgvector Documentation - Official Postgres extension
2. MongoDB Vector Search - Official docs
3. Elasticsearch Dense Vectors - Official docs
4. Why Vector Databases - Architectural justification
5. Postgres vs Pinecone Benchmark - Detailed comparison
6. SQL for Vector Search - Limitations explained

---

Reading time: 8-10 minutes
Next: 04-why-numpy-and-pandas-do-not-scale.md