Why Relational Databases Are Not Enough

In every software engineering cycle, there is a temptation to use the tools we already know. We have PostgreSQL, MySQL, and MongoDB. They are reliable, acid-compliant, and battle-tested. Naturally, the first question developers ask when entering the world of AI is: "Why can't I just store and search my vectors in my existing relational database?"

While many relational databases (like Postgres with pgvector) are adding vector support, there are deep mathematical, architectural, and algorithmic reasons why traditional databases cannot serve as a primary vector engine for large-scale AI applications.

1. The Algorithm Problem: B-Trees vs. HNSW

Relational databases were built on the B-Tree (Balanced Tree). B-Trees are world-class at searching one-dimensional data.

How a B-Tree works (1D Search)

If you search for id = 500 in a table of 1 million rows, the B-Tree allows you to find it in ~20 steps (log2 of 1,000,000). It works by constantly splitting data: "Is it less than 500,000? Is it more than 250,000?"

Why B-Trees fail in High Dimensions

Vector data is not 1D. A typical OpenAI embedding has 1536 dimensions. In a 1D line, "closeness" is simple: 4 is close to 5. In a 2D plane, closeness is still manageable (X and Y coordinates). In 1536D, the concept of a "Tree" that splits data into "left and right" completely collapses.

graph TD
    A[Root: [0.5, 0.5, ...]] --> B[Dim 1: < 0.5]
    A --> C[Dim 1: > 0.5]
    B --> D[Dim 2: < 0.2]
    B --> E[Dim 2: > 0.2]
    C --> F[...]
    subgraph Collapse
    G[In 1536D, there are too many directions to branch efficiently]
    end

To find the nearest neighbor in a high-dimensional space without a specialized index, the database has to execute a Full Table Scan (Sequential Scan). This means calculating the distance for every single row. Even with a fast GPU, doing this for 10 million rows per query is physically impossible at sub-second latency.

2. The "Curse of Dimensionality"

The Curse of Dimensionality refers to phenomena that arise when analyzing data in high-dimensional spaces that do not occur in low-dimensional settings.

Sparsity of Data

As dimensions increase, the "volume" of the space increases so fast that the data points we have become extremely sparse. In high dimensions, almost all pairs of points are at roughly the same distance from each other.

The Math of Distance

In a 1D space, calculating distance is abs(a - b). It's one subtraction. In a 1536D space, calculating the Euclidean Distance requires:

1536 subtractions
1536 squarings
1535 additions
1 square root

Doing this for every row in a traditional database table is computationally "expensive." Vector databases use specialized CPU instructions (like AVX-512 or AMX) designed for SIMD (Single Instruction, Multiple Data) to perform these 1536 calculations in a single clock cycle. Traditional RDBMS engines are not optimized for this type of parallel math.

3. Storage Hierarchy: Row-based vs. Vector-optimized

Relational (Row-based)

Relational databases store data in Rows. If you have a table with id, name, email, and vector, the data is saved as one long string on the disk: [1, "Sudeep", "s@dev.com", [0.1, 0.2...]].

When you want to search by vector, the database has to read the entire disk block including the name and email just to get to the vector data. This results in massive I/O waste.

Vector Databases (Optimized)

Vector databases often use Columnar or Specialized Storage. They keep the vectors in high-speed, contiguous memory buffers. This allows the CPU to stream the vectors directly into the calculation engines without being blocked by "non-vector" data.

4. Approximate Nearest Neighbor (ANN) vs. Exact Search

Relational databases pride themselves on Accuracy and ACID compliance. If you ask for a sum of money, it must be 100% accurate.

Vector search is different. In the context of AI, we rarely need the mathematically perfect nearest neighbor. We need a highly similar neighbor.

Vector databases use ANN Algorithms like:

HNSW (Hierarchical Navigable Small Worlds): Creating a "navigation graph" where you can skip through layers to find the neighborhood of your target.
IVF (Inverted File Index): Clustering the space into "buckets" and only searching the most relevant buckets.
Product Quantization (PQ): Compressing the vectors (e.g., from 1536 bytes to 64 bytes) to fit more data in RAM.

Traditional databases struggle with these probabilistic indexing models because they break the core contract of "Exact Retrieval" that SQL users expect.

5. Python Performance Comparison: Postgres vs. Vector DB (Logic)

Let's look at why the "Loop over everything" approach in a standard database logic fails as scale increases.

import time
import numpy as np

# Number of documents in our "Database"
SCALES = [1000, 10000, 100000, 500000]
DIMENSIONS = 1536 # Standard for OpenAI

def simulate_search(n_docs):
    # Simulate a database table of 'n_docs' vectors
    database = np.random.rand(n_docs, DIMENSIONS).astype('float32')
    query = np.random.rand(DIMENSIONS).astype('float32')
    
    start_time = time.time()
    
    # 1. This is what a standard database without a Vector Index does:
    # It calculates the distance for EVERY row (Exhaustive Search)
    # We use numpy cross-product as a proxy for the math
    distances = np.linalg.norm(database - query, axis=1)
    
    # 2. Sort to find the top 10
    top_10 = np.argsort(distances)[:10]
    
    end_time = time.time()
    return end_time - start_time

print(f"Algorithm: Exhaustive Search (Relational-style Table Scan)\n")
for scale in SCALES:
    latency = simulate_search(scale)
    print(f"Scale: {scale:7,} rows | Latency: {latency:.4f} seconds")

# Logic Check:
# At 500k rows, latency is likely already hitting >0.5s.
# At 10M rows, it becomes completely unusable for interactive AI.

In contrast, a vector database with an HNSW index would maintain a latency of under 10ms even as the scale grows to millions of rows, because it only visits a tiny fraction of the vector space.

6. Where Vector Extensions (like pgvector) fit in

It is important to note the rise of Vector Extensions.

pgvector for PostgreSQL
Redis VSS
MongoDB Atlas Vector Search

When should you use them? If your dataset is small (under 100,000 vectors) and your application already uses Postgres, pgvector is an excellent choice. It simplifies your architecture because you don't need to manage a separate database.

When are they "not enough"?

Extreme Scale: If you have 100 million vectors, the RAM requirements for HNSW in Postgres will likely cripple your main database performance.
Advanced Filtering: Pure vector databases like Pinecone can filter by metadata during the vector search process (Pre-filtering), whereas relational databases often do it after (Post-filtering), which is much slower.
Advanced RAG Features: Dedicated vector DBs offer built-in features for chunking, re-ranking, and hybrid scoring that require manual plumbing in SQL.

Summary and Key Takeaways

The fundamental reason relational databases are not enough for the AI era is that they were designed for exact matches in low-dimensional space.

Indexing Difference: B-Trees (SQL) are 1D; HNSW/IVF (Vector) are Multi-D.
Math Intensity: High-dimensional distance calculations require hardware acceleration (SIMD).
Accuracy vs. Speed: Vector apps prefer Approximate results for speed; SQL apps require Exact results.
Architectural Isolation: Storing heavy vector data in your main transactional DB can "poison" your cache and slow down standard SQL queries.

In the next lesson, we will look at the Modern AI Stack and see exactly where the Vector Database lives in a production environment alongside LLMs, API gateways, and your existing databases.

Exercise: Architecture Decision

You are designing a backend for a new startup. You have 50,000 product descriptions and you want to implement semantic search.

List the pros/cons of using your existing PostgreSQL database with pgvector.
List the pros/cons of adopting a dedicated vector database like Pinecone.
At what point in your user growth (number of products or number of queries) would you decide to migrate from Postgres to a dedicated Vector DB?

There is no single "right" answer, but understanding the transition point is the hallmark of a senior AI Engineer.

Why Relational Databases are Not Enough for Vector Data