01. Why traditional SQL indexes fail here — exact rows are not meaning neighborhoods¶

~12 min read. Before fancy ANN tricks, see why normal database indexes solve a different problem.

Continues from the first-principles overview in 00-first-principles.md. The route map — the warehouse guide for the scout robot — must support "find near meaning," not only "find exact row."

1) What SQL indexes are brilliant at¶

Begin with a concrete workload: A B-tree index is excellent for ordered lookup. A hash index is excellent for exact equality. A bitmap index is excellent for some filtered analytics.

If you ask for customer_id = 42, SQL smiles. If you ask for created_at between X and Y, SQL smiles again. If you ask for price < 500, it still feels natural.

Why? Because the database knows an ordering rule. Or it knows exact bucket membership. That is enough for fast pruning.

In production, the failure is specific: Similarity search asks a different question. It asks, "Which rows are closest to this new point?" Not equal. Not smaller. Not alphabetically nearby. Geometrically nearby.

The scout robot does not want one shelf label. It wants a neighborhood on the warehouse floor. A SQL index usually does not preserve that neighborhood. That is the mismatch.

The toy picture exposes the mismatch.

B-tree world                    vector world

apple                           ● returns policy
banana                          ● refund dispute
carrot                          q query point
dates                           ● invoice exception
elderberry                      ● guitar tutorial

ordered by text                 ordered by geometric closeness

On the left, order is one-dimensional. On the right, closeness is multi-dimensional. Those are different tasks.

A B-tree can answer, "what comes before banana?" It cannot directly answer, "what sits nearest to q in 768 dimensions?" That is why ordinary indexing feels awkward here.

2) Three failed attempts before the real answer¶

Attempt one. Store the original text. Use LIKE '%refund%'.

Good for exact word overlap. Bad for semantic similarity. "refund deadline" and "money back window" may mean the same thing. Their surface words differ. So recall drops.

Attempt two — add a B-tree on every embedding dimension. Suppose each package tag has three numbers and you index x, y, and z separately. The failure is that nearest-neighbor ranking depends on all coordinates together; a row that looks decent on each individual axis can still be far away in the full space, so separate axis indexes do not preserve joint distance.

Attempt three — bucket each dimension into ranges. A predicate like x in [0.7, 0.9] and y in [0.2, 0.4] gives you a box, while nearest neighbors live in circles or spheres around the query. Big boxes admit too many wrong items; small boxes miss good items just outside the edge. The practical rule is to use a structure designed around nearest-neighbor search itself, which is where vector indexes enter.

The mismatch is easier to see visually.

query q = (5,5)

square prefilter             true nearest region

x in [4,6]                   distance <= 1.5
y in [4,6]

  4------6                     .-''''-.
  |      |                   .'   q    '.
  |  q   |                  /            \
  |      |                  \            /
  4------6                   '._      _.'
                               '-....-'

The square is convenient for SQL predicates. The circle is what distance actually means. That gap creates false positives and false negatives.

3) Worked numerical example: why row predicates miss nearest neighbors¶

Take four rows with two-dimensional tags. Keep it tiny.

Row A = (1, 9)
Row B = (4, 4)
Row C = (5, 6)
Row D = (8, 8)
Query q = (5, 5)

First compute true Euclidean distances. Use the picture before the arithmetic.

y
9 | A
8 |                D
7 |
6 |          C
5 |          q
4 |       B
3 |
2 |
1 |
0 +------------------------ x
    0 1 2 3 4 5 6 7 8 9

The distance calculation is straightforward.

Distance to A: sqrt((5-1)^2 + (5-9)^2) = sqrt(16 + 16) = sqrt(32) ≈ 5.66

Distance to B: sqrt((5-4)^2 + (5-4)^2) = sqrt(1 + 1) = sqrt(2) ≈ 1.41

Distance to C: sqrt((5-5)^2 + (5-6)^2) = sqrt(0 + 1) = 1

Distance to D: sqrt((5-8)^2 + (5-8)^2) = sqrt(9 + 9) = sqrt(18) ≈ 4.24

True ranking is C, then B, then D, then A. Correct answer found.

Try SQL-style rectangular pruning. A prefilter like x between 4 and 5 keeps B and C, which happens to work here; tightening it to x = 5 keeps only C, so B disappears even though B is the second-nearest row.

Try another filter. x between 5 and 8 AND y between 5 and 8. That keeps C and D. Now B disappears again.

The core issue is that any fixed axis-aligned box is arbitrary because the nearest-neighbor set changes with every query. The scout robot wants distance-aware pruning, while a normal row predicate only approximates shape in a crude way.

4) Why high dimensions make ordinary indexing even worse¶

In two dimensions, you can still draw boxes. In 768 dimensions, boxes become nonsense for intuition. Also, data becomes sparse. Points spread out. Distances bunch together.

A one-dimensional index becomes even less helpful in that setting. One axis tells almost nothing, even ten axes are only partial clues, and the answer comes from the full vector geometry.

The compact picture looks like this.

query uses all coordinates together

q = [q1, q2, q3, ... q768]
      │   │   │        │
      └───┴───┴────────┘
            distance

single B-tree on q17 only
      │
      └─ weak clue, not the answer

This is why vector systems talk about HNSW, IVF, and PQ. Those structures are built around approximate distance search. Not row ordering. Not equality buckets.

The important nuance is that SQL is still excellent for metadata. The aisle sticker fits naturally in SQL-style filtering, so tenant, date, status, language, and ACL rules remain good relational work.

But the core nearest-neighbor search wants different machinery. Often teams combine both worlds. Structured filtering on one side. Vector search on the other side. That hybrid design is practical.

5) What SQL still does well around a vector system¶

Begin with a concrete workload: Most real products still keep relational data nearby. Orders, users, permissions, catalogs, and audit logs remain tabular. A vector database does not replace all of that.

Instead, think split responsibility.

SQL owns exact facts and transactions.
The vector index owns fast semantic neighborhood search.
Metadata joins connect the two.

The loading dock may write embeddings into a vector engine. The application may keep canonical business rows in PostgreSQL. The scout robot finds candidate IDs. Then SQL fetches the full records.

That is why pgvector exists. That is why Weaviate and Qdrant expose payload filters. That is why Pinecone users often keep source-of-truth data outside Pinecone. Use the right tool for the right subproblem.

The system sketch looks like this.

user query
   │
   ├─► embedding model ─► vector search index ─► candidate ids
   │
   └────────────────────────────────────────────► SQL rows by id
                                                 │
                                                 ▼
                                          final ranked response

The precise statement is that traditional SQL engines fail for similarity search when used alone; they do not fail for metadata, joins, or transactions. That distinction matters in interviews and matters even more in production.

6) Why not forcing similarity search into ordinary B-tree predicates under this workload¶

The tempting alternative is forcing similarity search into ordinary B-tree predicates because it keeps the architecture small and makes the first demo look clean. That story is useful for a prototype, but it becomes dangerous once the workload has real scale, filters, freshness pressure, and evaluation data.

It fails when exact row predicates do not model semantic neighborhoods in high-dimensional space. At that point the system needs an inspectable artifact — query vector, candidate rows, and nearest-neighbor trace — because otherwise every bad answer turns into a vague argument about whether embeddings, ANN, metadata filters, lifecycle, or evaluation are guilty.

Option	Works when	Fails when	Cost moves to
forcing similarity search into ordinary B-tree predicates	corpus is small or low-risk	exact row predicates do not model semantic neighborhoods in high-dimensional space	latency, recall, or user trust
SQL index mismatch	the failure can be measured in the index path	traces or baselines are missing	memory, rebuilds, evals, operations

Mini-FAQ. "Is this always worth adding?" No. The RAG-fundamentals rule still applies: add machinery only when a measured workload pressure earns it. If exact search is cheap, if filters are simple, or if evaluation is missing, the clever index can become a more expensive way to stay confused.

7) Production signals — know whether SQL index mismatch is working¶

Healthy behavior means query vector, candidate rows, and nearest-neighbor trace explains why the returned neighbors changed. In a real incident review, you should be able to point at that artifact and explain why the candidate set changed, not merely say that the database returned something.

The first metric to watch is semantic-neighbor miss rate. Track it by query family, tenant, corpus slice, and index version, because global averages hide exactly the failures users notice first.

The misleading metric is database uptime. A vector database can be perfectly available while recall, filtering, freshness, or embedding compatibility is broken, so uptime only proves the warehouse doors opened; it does not prove the scout robot found the right shelf.

The expert graph compares exact baseline recall, p50/p99 latency, filter selectivity, index version, embedding version, and bad-query examples by slice. That graph is the difference between tuning knobs and debugging a retrieval system.

bad retrieval
   -> query vector / filter
   -> index path
   -> candidate neighbors
   -> score and metadata trace
   -> exact baseline or judged list

8) Boundary — where SQL index mismatch helps and where it does not¶

Use this mechanism when the failure happens inside vector geometry, index traversal, filtering, lifecycle, or serving operations. That is the zone where vector-database machinery can actually change the returned neighbors, the latency curve, or the operational envelope.

Do not expect it to fix cases where the source content is wrong, the embedding model is poor for the domain, or the product definition of relevance is unresolved. Those are upstream or product-definition failures, and better ANN settings will only make the wrong evidence arrive faster.

The common pathology is that teams keep tuning ANN knobs when the real issue is bad chunks, stale data, weak labels, or missing evals. In interviews, call this out explicitly: the index is not the whole retrieval system, it is one stage inside a pipeline that also depends on documents, chunks, labels, and evals.

The scale limit is blunt: every improvement spends something — RAM, disk, build time, query latency, engineering time, or vendor lock-in. The mature answer is not to pick the fanciest mechanism; it is to choose the pressure you are willing to pay for.

9) Wrong model — SQL indexes are enough if vectors are stored in a column¶

The wrong model is attractive because it compresses the system into one easy story, and easy stories feel good in design docs. The trouble is that production vector search is not one story; it is embedding quality, distance metric, ANN index, metadata filters, lifecycle, sharding, vendor operations, and monitoring all interacting under traffic.

If SQL index mismatch cannot change recall, latency, cost, freshness, or debug visibility, it is not carrying its weight; it is vocabulary without leverage.

10) Failure taxonomy for SQL index mismatch¶

Geometry failure — the embedding space does not put useful neighbors close enough.
Metric failure — the chosen similarity ruler disagrees with the model or workload.
Index failure — ANN skips relevant vectors or returns unstable candidates.
Filtering failure — metadata filters erase good candidates or violate scope.
Lifecycle failure — stale, mixed-version, or partially rebuilt indexes serve traffic.
Scale failure — fan-out, memory, or rebuild cost breaks the SLO.
Debugging failure — no trace connects query vector, index path, candidates, and final result.

11) Pattern transfer — where this returns later¶

RAG uses vector DBs as the evidence gateway before generation.
Retrieval and ranking supplies the metrics and fusion logic used here.
Data engineering supplies chunk quality, metadata, and embedding-version hygiene.
Production evals decide whether recall and relevance changes actually help users.

12) Design review checklist¶

What pressure is this mechanism relieving: latency, memory, filtering, freshness, scale, or evaluation?
What artifact would you inspect first: vector neighbors, index trace, filter plan, namespace manifest, or exact baseline?
Why is forcing similarity search into ordinary B-tree predicates weaker for this workload?
Which slice should improve first?
Which cost rises first: RAM, disk, build time, query latency, or operational complexity?
What rollback signal tells you the index change hurt retrieval?

Where this lives in the wild¶

GitHub code search teams — search infrastructure engineer. Exact symbol filters use structured indexes, while semantic retrieval needs a separate vector path.
Notion AI workspace search — retrieval engineer. Page permissions and workspace filters live like SQL predicates, but nearest-page meaning search needs vector indexing.
Instacart catalog discovery — relevance engineer. Category, availability, and region filters are structured, yet similar-item search needs embedding neighborhoods.
Stripe docs assistant — platform engineer. Product area and API version behave like metadata, while answer retrieval depends on semantic closeness.
LinkedIn talent search — ranking engineer. Location and company constraints are tabular filters, while skill-profile similarity needs vector retrieval.
Enterprise RAG — vector DBs store policy, wiki, ticket, and document chunks for semantic retrieval.
Ecommerce search — vectors help with descriptive queries while filters protect catalog scope.
Support copilots — need metadata filters for tenant, product, language, and freshness.
Code search — mixes semantic vectors with exact identifiers and repository permissions.
Recommendation systems — use nearest-neighbor retrieval before ranking models.
Image and multimodal search — embeddings represent images, captions, and cross-modal queries.
Legal discovery — recall and auditability are more important than average latency alone.
Healthcare retrieval — metadata, permissions, and freshness are safety boundaries.
Fraud and anomaly systems — vector similarity finds nearby behavior patterns.
Personalization systems — user and item embeddings need versioned lifecycle management.

Recall checkpoint¶

Why is customer_id = 42 easy for SQL, but "find similar meaning" hard?
What goes wrong if you index embedding dimensions separately?
Why do axis-aligned boxes mismatch nearest-neighbor circles?
What parts of the problem should still stay in SQL?
Which artifact would you inspect first for SQL index mismatch?
What query or corpus slice would prove the improvement is real?
What is the first operational cost this mechanism adds?

Interview Q&A¶

Q: Why use a vector index and not only a B-tree over embedding columns? A: Because nearest-neighbor ranking depends on all coordinates jointly. Separate ordered indexes do not preserve global distance neighborhoods.

Common wrong answer to avoid: "Because SQL cannot store vectors." SQL can store them. The issue is search structure, not storage type.

Q: Why does similarity search need new access paths and not just more predicates? A: Because predicates carve arbitrary boxes, while nearest-neighbor search needs distance-aware exploration around each query point.

Common wrong answer to avoid: "More predicates always approximate the circle well enough." In high dimensions, that quickly becomes brittle and expensive.

Q: Why not keep everything in a relational engine and brute-force there? A: You can for small datasets. Past modest scale, query latency and compute cost grow linearly, so dedicated ANN structures become necessary.

Common wrong answer to avoid: "Relational engines are always too slow." Small collections absolutely can work fine with exact scan.

Q: Why is SQL still present in vector-search systems? A: Because filters, joins, transactions, and source-of-truth records remain structured even when semantic lookup moves to a vector index.

Common wrong answer to avoid: "Once you add vectors, relational data modeling disappears." It usually stays central.

Q: What artifact would you inspect first when SQL index mismatch fails? A: I would inspect query vector, candidate rows, and nearest-neighbor trace, then compare it with exact baseline, filter state, index version, and embedding version.

Common wrong answer to avoid: "Just check whether the vector DB is up." — Availability does not prove recall, freshness, or relevance.

Q: How do you know the change helped? A: Track semantic-neighbor miss rate on a representative query slice and compare it with latency, memory, build time, and filtered-result behavior.

Common wrong answer to avoid: "The average similarity score increased." — Similarity scores are not product-quality metrics by themselves.

Q: When should you avoid this mechanism? A: Avoid it when the corpus is small, exact search is cheap, or the team lacks evaluation data to prove the extra complexity helps.

Common wrong answer to avoid: "Every production AI system needs the most advanced vector index." — The right index depends on workload, scale, filters, and operational constraints.

Apply now (10 min)¶

Exercise. Take five support articles from your domain. Write toy 2D coordinates for each one. Pick a query point. Compute which article is truly nearest. Then invent one rectangular SQL prefilter. Notice which good article gets excluded.

Sketch from memory. Draw the box-versus-circle picture. Label the warehouse floor, the scout robot, and the route map. Then write one line on what SQL still does well.

Reproduce from memory: explain SQL index mismatch with its pressure, artifact, metric, boundary, and failure mode.

What you should remember¶

Sql index mismatch exists because exact row predicates do not model semantic neighborhoods in high-dimensional space. The point is not to memorize a vendor feature; it is to know which workload pressure the mechanism relieves and which cost it creates.

The artifact to inspect is query vector, candidate rows, and nearest-neighbor trace. If you cannot inspect it, vector search debugging becomes guesswork.

Remember:

Vector search fails through geometry, metrics, indexes, filters, lifecycle, scale, and monitoring.
Watch semantic-neighbor miss rate by query and corpus slice before trusting global averages.
Exact baselines and judged lists are how you keep ANN tuning honest.
Every vector database choice moves cost between recall, latency, memory, rebuilds, and operations.

Bridge. Once we accept that similarity means distance on the warehouse floor, the next question becomes obvious: distance according to which metric? → 02-vector-similarity-metrics.md