Entity Resolution in Postgres: Trigrams, Jaro-Winkler, and Vector Embeddings Compared

The “deduplicate the data” problem nobody quotes properly

Every data project I’ve ever been involved with eventually hits the same wall, usually about six weeks in: “we need to deduplicate this.” A dataset of companies. Or agencies. Or customers. Or addresses. The names are slightly different. The cases are inconsistent. The punctuation drifts. Some have legal suffixes (“Ltd”, “LLC”, “GmbH”), some don’t. Half of them have typos. And the business has been treating each variant as a separate record for years.

The job sounds simple. It is not simple. It is an entire subdiscipline, entity resolution, and the difference between doing it well and doing it badly is the difference between a clean analytics layer and one that quietly double-counts revenue forever.

Most of the time, the right place to do entity resolution is inside the database, not in a separate pipeline. Postgres has good enough primitives to handle 90% of the real-world cases, and keeping the matching close to the data avoids the entire class of “we matched it in Python but the source of truth is in Postgres” disasters. This post compares the three techniques you’ll actually reach for in Postgres, with the honest trade-offs of each.

This is the kind of unglamorous work that lives in the AI data preparation part of what we do, it’s where most “AI projects” actually live, even though nobody puts it on the slide deck.

Technique 1: pg_trgm (trigram similarity)

The simplest weapon in the box, and almost always the right starting point. pg_trgm is a Postgres extension that breaks strings into overlapping three-character sequences and compares them. It’s been in Postgres for two decades, it’s stable, it’s indexed, and it’s almost embarrassingly effective at exactly the problem most teams actually have.

Enabling it:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

A GIN or GiST index on the column you want to match makes lookups fast:

CREATE INDEX agency_name_trgm_idx
  ON agency
  USING gin (normalised_name gin_trgm_ops);

The lookup itself is a single query:

SELECT id, normalised_name, similarity(normalised_name, $1) AS score
FROM agency
WHERE normalised_name % $1
ORDER BY score DESC
LIMIT 5;

The % operator uses a configurable threshold (default 0.3); similarity() gives you a 0, 1 score. In practice you’ll want a normalisation function, lowercasing, stripping legal suffixes, collapsing whitespace, removing punctuation:

CREATE OR REPLACE FUNCTION normalise_name(name text)
RETURNS text LANGUAGE sql IMMUTABLE AS $$
  SELECT lower(
    regexp_replace(
      regexp_replace(coalesce(name, ''), 's+(ltd|llc|inc|gmbh|sa|plc|sarl|bv).?s*$', '', 'i'),
      '[^a-z0-9 ]', '', 'gi'
    )
  )
$$;

Where pg_trgm wins: noisy strings of similar length, in the same language, where the differences are typos, casing, or minor word order. Company names, agency names, product SKUs, address fragments. Indexable, predictable, no external dependencies, no model to maintain.

Where pg_trgm quietly breaks: strings of very different lengths (the trigram count diverges and similarity scores collapse), abbreviations that share no characters (“International Business Machines” vs “IBM”), and anything semantic (“Acme Holdings” vs “Acme Group Inc”). It also can’t help across languages, because trigrams of different scripts don’t overlap.

Reach for pg_trgm first, every time. About 70% of real-world dedup problems do not need anything more sophisticated.

Technique 2: Jaro-Winkler (and friends) via fuzzystrmatch

For the cases where trigrams collapse, particularly short strings where a single character difference matters disproportionately, fuzzystrmatch adds Levenshtein, Soundex, Metaphone, and a handful of related algorithms. The most useful in practice is Jaro-Winkler:

CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

SELECT id, normalised_name,
       1.0 - (levenshtein(normalised_name, $1)::float / GREATEST(length(normalised_name), length($1))) AS score
FROM agency
ORDER BY score DESC
LIMIT 5;

Jaro-Winkler specifically weights matches at the start of the string more heavily, which is exactly what you want when the difference is a trailing suffix or a typo near the end. It’s the algorithm I’ve reached for repeatedly on short identifiers, agent names, person names, anything where one character matters.

Where it wins: short strings (under ~30 characters), name-shaped data, cases where positional weight matters.

Where it breaks: long strings (Levenshtein cost dominates), abbreviations (same problem as trigrams), and anything you want to scale to millions of rows without a candidate-blocking strategy. None of the fuzzystrmatch functions are inherently indexable in the way pg_trgm is, you’ll typically use trigrams to pre-filter candidates, then Jaro-Winkler to score them precisely. The two extensions are complementary, not alternatives.

A pragmatic pattern: use pg_trgm to get the top 20 candidates, then rescore those 20 with Jaro-Winkler. You get the index-backed performance of trigrams with the positional precision of Jaro-Winkler, at the cost of one extra CTE.

WITH candidates AS (
  SELECT id, normalised_name
  FROM agency
  WHERE normalised_name % $1
  ORDER BY similarity(normalised_name, $1) DESC
  LIMIT 20
)
SELECT id, normalised_name,
       (similarity(normalised_name, $1) * 0.5) +
       ((1.0 - levenshtein(normalised_name, $1)::float / GREATEST(length(normalised_name), length($1))) * 0.5)
       AS combined_score
FROM candidates
ORDER BY combined_score DESC
LIMIT 5;

Technique 3: vector embeddings via pgvector

The newest tool in the box, and the one that’s most over-prescribed. pgvector lets you store dense vector embeddings in Postgres and search them by cosine or L2 distance. Combined with a sentence-embedding model (any of the standard OpenAI / Cohere / open-weight models), it lets you match on meaning rather than character overlap.

CREATE EXTENSION IF NOT EXISTS vector;

ALTER TABLE agency ADD COLUMN name_embedding vector(1536);

CREATE INDEX agency_name_embedding_idx
  ON agency
  USING ivfflat (name_embedding vector_cosine_ops)
  WITH (lists = 100);

Querying:

SELECT id, normalised_name,
       1 - (name_embedding <=> $1::vector) AS score
FROM agency
ORDER BY name_embedding <=> $1::vector
LIMIT 5;

Where it wins: semantic matches that no character-based approach can see. “International Business Machines” ↔ “IBM”. “Acme Group, Inc.” ↔ “Acme Holdings”. Cross-language matches. Long strings where the semantic gist is what matters and the surface form is incidental.

Where it breaks, and where most teams get caught out:

• Embeddings are not free. Every row needs an embedding computed and stored. Every query needs an embedding computed for the lookup string. That’s an external model call per insert and per query, with cost and latency. For a dedup job over a few hundred thousand rows, that’s a real bill.
• Embeddings are non-deterministic across model versions. Upgrade your embedding model and you must re-embed everything, or your old and new vectors are no longer in the same space. This is not a one-off concern, it’s an ongoing operational cost.
• Embeddings hide the reason for a match. When Jaro-Winkler says two strings match at 0.92, you can read the strings and verify. When cosine similarity says they match at 0.89, you have a number with no explanation, which is fine for ranking and poor for the kind of human review every real entity-resolution project requires.
• They over-match on short, ambiguous strings. Two single-word company names will often have high cosine similarity simply because they’re both single-word company names, not because they’re related.

Use embeddings when you have a genuinely semantic problem, synonyms, abbreviations, cross-language, long descriptive strings. Don’t reach for them first because they’re the new shiny thing.

A practical decision tree

For most teams, the right approach is layered, not exclusive:

1. Normalise hard first. Lowercase, strip suffixes, collapse whitespace, remove punctuation. This single step resolves 30, 50% of duplicates by itself, with no algorithm needed. Cheap, deterministic, debuggable.
2. Use pg_trgm for the bulk of the work. Indexed, fast, predictable. Handles typos and minor variation. Set the similarity threshold high (0.7+) and accept that you’ll miss the hard cases.
3. Layer Jaro-Winkler on short strings. Use it to rescore trigram candidates when the strings are short and positional differences matter.
4. Reach for embeddings only for genuinely semantic matches. Abbreviations, synonyms, cross-language. Treat them as a last layer, not a first attempt, and budget for the cost of computing and re-computing them.
5. Always require human review above a threshold. No matter the technique, every entity-resolution pipeline I’ve shipped has had a “review queue”, matches above the auto-merge threshold but below the certain-match threshold, surfaced to a human. This isn’t a failure mode; it’s the design.

The mistake I see most often is teams skipping straight to embeddings because that’s where the energy is, and then discovering that they’ve solved 10% more cases at 100x the operational cost, while still having the same review queue at the end. Boring works. Boring is indexable. Boring is debuggable. Boring is also, almost always, what you actually need.

This kind of dedup work shows up everywhere, customer data platforms, finance reconciliation, the messy “before” state in every analytics initiative. We do a lot of it in our AI data preparation engagements, because clean, resolved entities are the unglamorous foundation that every “AI” project actually depends on.