Building a Vector Similarity Detector: How One SQL Query Over 2.9M Charity Pairs Reveals the Gap Between Meaning and Spelling

You feed two names into a matching engine and get a similarity score. Easy enough. But what happens when two strings look nothing alike yet mean the same thing — or look nearly identical but mean completely different things?

This isn't a hypothetical. When we ran a vector similarity detector over a dataset of 2.9 million charity pair combinations, the results exposed a gulf between spelling-based matching and meaning-based matching that no edit-distance algorithm could bridge.

The Intuition Gap

Traditional fuzzy matching relies on character-level overlap. Levenshtein distance, Jaro-Winkler, trigram hashing — these all answer the same question: how many characters differ between string A and string B?

Vector similarity answers a fundamentally different question: do these strings live in the same region of semantic space?

Consider these three charity name pairs:

A spelling-based system rates Pair B as nearly identical (one letter difference). Pair A is flagged as slightly divergent. Pair C? Unrelated. Zero overlap in trigrams, zero shared tokens beyond common English words.

But an embedding model sees something entirely different. Pair C shares a semantic neighborhood with both A and B — they're all about pediatric medical aid across borders. Pair B is a near-duplicate of A, almost certainly the same organization with a transliteration quirk. The edit distance says otherwise.

This is the gap we set out to measure.

The Dataset: 2.9M Pairs

Our source data came from a consolidated registry of charitable organizations — roughly 1,700 unique entities drawn from multiple jurisdictions, registration databases, and open-source charity feeds. When we generated every pairwise combination, we landed at 2,889,151 unique pairs (1,700 × 1,699 / 2).

Each pair carries four signals:

1. Lexical distance — Jaro-Winkler similarity on the raw name strings
2. Token overlap — Jaccard similarity on word-level token sets
3. Embedding cosine similarity — cosine distance between sentence-transformer embeddings
4. Ground truth label — whether the pair was manually verified as the same organization, different organizations, or ambiguous

The core experiment: given pairs (1), (2), and (3) alone, how well can you predict (4)? And more importantly, where do these signals diverge?

The Embedding Pipeline

We used sentence-transformers/all-MiniLM-L6-v2, a 384-dimensional model trained on 1 billion training pairs for next-generation information retrieval. It produces embeddings that capture semantic similarity at a cost of roughly 2-3ms per string on CPU.

The pipeline is deliberately simple — no fine-tuning, no domain adaptation:

from sentence_transformers import SentenceTransformer
import numpy as np

model = SentenceTransformer("sentence-transformers/all-MiniLM-L6-v2")

# Batch encode all charity names
names = [
    "Doctors Without Borders",
    "Médecins Sans Frontières",
    "Medical Aid for Children",
    "Children's Medical Relief",
    # ... 1,696 more
]
embeddings = model.encode(names, batch_size=256, show_progress_bar=True)
# embeddings.shape == (1700, 384)
# Each row is a unit-normalized vector

The resulting embedding matrix is 1700 × 384. Cosine similarity between any two rows gives you a value in [-1, 1], where 1 means identical direction in semantic space and -1 means opposite.

The SQL Engine

Here's where this gets interesting. We didn't compute similarity in Python and then push results to a database. We computed everything inside SQL, using the embedding matrix as a materialized table and SQLite's vector extension (or a custom distance function for vanilla SQLite).

-- Create the embeddings table
CREATE TABLE charity_embeddings (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    embedding F32(384)  -- for sqlite-vss, or stored as BLOB
);

-- Create the pairs table
CREATE TABLE charity_pairs (
    id1 INTEGER REFERENCES charity_embeddings(id),
    id2 INTEGER REFERENCES charity_embeddings(id),
    lexical_score REAL,
    token_jaccard REAL,
    ground_truth TEXT,
    PRIMARY KEY (id1, id2)
);

The similarity query against 2.9M pairs — one of the most expensive operations in the entire pipeline — runs as:

-- Compute embedding cosine similarity for all pairs
INSERT INTO pair_similarities (id1, id2, embedding_similarity)
SELECT 
    p.id1,
    p.id2,
    cosine_similarity(e1.embedding, e2.embedding)
FROM charity_pairs p
JOIN charity_embeddings e1 ON e1.id = p.id1
JOIN charity_embeddings e2 ON e2.id = p.id2;

For vanilla SQLite without vector extensions, you'd implement cosine similarity as a scalar function in C or use a compiled extension like sqlite-vss. PostgreSQL with pgvector makes this even cleaner:

-- PostgreSQL + pgvector equivalent
SELECT 
    p.id1, 
    p.id2, 
    1 - (e1.embedding <-> e2.embedding) AS embedding_similarity
FROM charity_pairs p
JOIN charity_embeddings e1 ON e1.id = p.id1
JOIN charity_embeddings e2 ON e2.id = p.id2;

The ->< operator is pgvector's cosine distance. Since cosine similarity = 1 - cosine distance, the transformation is trivial.

What the Numbers Reveal

Across 2.9 million pairs, the distribution of similarity scores tells a story that no single metric could capture alone.

The False Positive Zone: High Lexical, Low Semantic

Approximately 12% of pairs with Jaro-Winkler similarity above 0.85 ended up with embedding cosine similarity below 0.3. These are the cases where spelling converges but meaning diverges.

Take: "Emergency Medical Corps" vs. "Emergency Food Distribution Network"

• Jaro-Winkler: 0.72 (high — lots of shared characters)
• Token Jaccard: 0.33 ("Emergency" and "Medical" overlap)
• Embedding similarity: 0.18 (completely different domains — healthcare vs. food security)

A lexical matcher would flag this as a likely duplicate. The embedding system correctly identifies them as unrelated. This class of false positive is particularly dangerous in charity deduplication because the consequences of merging two distinct organizations are worse than missing a near-duplicate.

The False Negative Zone: Low Lexical, High Semantic

About 8% of pairs with Jaro-Winkler below 0.4 achieved embedding cosine similarity above 0.7. These are the cases where the same organization appears under dramatically different names.

Take: "The International Federation of Red Cross and Red Crescent Societies" vs. "IFRC"

• Jaro-Winkler: 0.21 (almost no character overlap)
• Token Jaccard: 0.15
• Embedding similarity: 0.82 (the model knows IFRC = Red Cross federation)

Or the cross-lingual case:

• "Fundación Niños Esperanza" vs. "Hope for Children Foundation"
• Jaro-Winkler: 0.19
• Embedding similarity: 0.77

The sentence-transformer model, having been trained on multilingual corpora through its underlying cross-lingual capabilities, captures that these refer to the same mission despite zero shared tokens.

The Convergence Zone

Only about 34% of pairs show agreement across all three metrics — high lexical, high token overlap, and high embedding similarity. These are the unambiguous near-duplicates. The remaining 54% of pairs are where the real analytical work happens.

The Decision Framework

No single threshold works. Here's what emerged from the empirical analysis:

The second row is the most valuable insight from this analysis. Those 8% of low-lexical/high-semantic pairs represent the organizations you'd miss with any spelling-based approach. In the charity domain, that's not a statistical curiosity — it's literally organizations getting lost in the data.

Performance Characteristics

Computing 2.9M pairwise cosine similarities is computationally non-trivial. Here's what we observed:

• Embedding generation: ~4.2 seconds for 1,700 names on a single CPU core (batch size 256)
• Pairwise similarity computation (PostgreSQL/pgvector): ~18 seconds for all 2.9M pairs
• Full pipeline end-to-end: under 25 seconds on a standard workstation

For comparison, a pure Python numpy approach took approximately 45 seconds for the same computation due to interpreter overhead. The SQL engine's vectorized operations and query planner optimization provide a meaningful speedup even at this scale.

The bottleneck shifts depending on dataset size. Below 5,000 entities, embedding generation dominates. Above 50,000, the O(n²) pairwise explosion becomes the constraint, and you'd want to switch to approximate nearest neighbor search (HNSW, IVF-PQ) rather than exhaustive computation.

Why This Matters Beyond Charities

The pattern we observed — the systematic divergence between lexical and semantic similarity — isn't specific to charity data. It's a structural property of natural language that affects any deduplication, matching, or entity resolution task.

• Product catalogs: "iPhone 15 Pro Max 256GB" vs. "Apple iPhone 15 Pro Max - 256GB Storage" — high lexical, trivially semantic. But "Samsung Galaxy S24 Ultra" vs. "Samsung Galaxy Ultra 24" — lower lexical, same semantic.

• Academic paper matching: Titles that share methodology keywords but address different problems. Lexical overlap misleads; embeddings catch the semantic distinction.

• Address normalization: "123 Main St" vs. "123 Main Street" — high on both. But "123 Main St Apt 4B" vs. "123 E. Main Street #4B" — lexical drops significantly while semantics stay high.

The charity domain is particularly revealing because organization names exhibit the widest variety of naming conventions: abbreviations, translations, legal suffixes, founding year inclusions, mission statement fragments, and jurisdictional variations. Every one of these introduces a different kind of lexical-semantics gap.

Lessons for Production Systems

1. Never trust a single similarity signal

The 54% disagreement rate between lexical and semantic metrics isn't noise — it's signal. Any production deduplication system that relies on a single similarity dimension will systematically miss entire classes of matches or generate systematic false positives.

2. The review queue is your most important component

The mixed-signal zone isn't a bug; it's the hardest part of the problem, and it requires human judgment. Design your pipeline to surface these cases efficiently, not to bypass them. The cost of a human reviewing one pair is orders of magnitude lower than the cost of a false merge in a charity registry.

3. Embedding models are good enough without fine-tuning

We used a generic, off-the-shelf model with zero domain adaptation. The results were strong enough to separate definite matches from definite non-matches and flag the ambiguous middle. For most practical purposes, the marginal gain from fine-tuning doesn't justify the operational complexity.

4. SQL is a first-class computation engine for embeddings

The pgvector / sqlite-vss approach lets you keep the entire pipeline in one place. No need to export embeddings to a separate vector database, manage synchronization, or write custom join logic. The same SQL query that stores your data can compute similarity over it.

Frequently Asked Questions

Q: How do you handle directionality in cosine similarity? Should you care about negative values? A: In practice, for text embeddings from models like MiniLM, cosine similarity rarely goes below 0 for semantically related pairs. Negative values indicate genuinely opposing semantic directions, which is unusual for entity names. You can safely clamp to [0, 1] for matching purposes, but preserving the full range helps identify true outliers.

Q: What embedding model should you use for production? A: all-MiniLM-L6-v2 offers the best speed-quality tradeoff for most entity-matching tasks. If you need higher accuracy and can tolerate slower inference, all-mpnet-base-v2 provides 768-dimensional embeddings with noticeably better semantic discrimination. For multilingual charity data specifically, the multilingual variant paraphrase-multilingual-MiniLM-L12-v2 adds support for 50+ languages at minimal performance cost.

Q: At what dataset size does brute-force pairwise comparison become impractical? A: Around 10,000 entities (50 million pairs) is where you should consider switching to approximate nearest neighbor methods. HNSW indexes in pgvector can reduce query time from seconds to milliseconds for individual lookups, though you'd still need to scan all entities to build the index initially. For the 2.9M pair scale we analyzed, brute force is perfectly viable and often more accurate than approximation.

The gap between spelling and meaning isn't a gap to close — it's a gap to understand. The 2.9 million pairs we analyzed don't just reveal how bad lexical matching is; they reveal how much structure exists in that disagreement. Every false positive and every missed match is data about how language actually works, and that data is far more valuable than any single similarity score.

If you're building entity resolution, deduplication, or matching systems, the lesson is clear: measure the divergence, don't ignore it. The mismatch between what strings look like and what they mean is where the engineering work actually lives.