Fuzzy Matching in PostgreSQL with pg_similarity: A Practical Dedup Guide

By /

pg_similarity is a PostgreSQL extension that adds a library of string similarity and distance functions like Levenshtein,Jaro, Jaro-Winkler, token/set-based like Jaccard, Dice,Cosine and phonetic like Soundex for fuzzy matching directly in SQL.

If you have done fuzzy matching in Postgres before, you might have probably used pg_trgm for typo-tolerant searches, and now you might be wondering why we should use pg_similarity It is a complementary extension for pg_trgm, and not a replacement. It packages several similarity metrics instead of one, which matters a lot for deduplication. That is because different kinds of duplications (typos, word reordering, phonetic near misses ) respond to different kinds of similarity.

In this blog, let’s walk through a real dedup exercise on a sanitized set of customer/vendor records: install pg_similarity test the functions before trusting them, run each comparison function, build a composite match score, and cover a migration pitfall worth knowing about.

These are a few parts covered in this blog post:

  • Install pg_similarity
  • Sanity-check the functions before using them
  • Run the comparison functions
  • Build a composite match score
  • Migration pitfall: Oracle Jaro-Winkler scale mismatch

Install pg_similarity

To install the pg_similarity extension, please refer to the below mentioned steps

#Install the package (Debian/Ubuntu via PGDG, or bundled on CYBERTEC PGEE):
sudo apt install postgresql-18-similarity
#Create the extension in your target database:
CREATE EXTENSION pg_similarity;

Verify the installation 

\dx+ pg_similarity

The following image shows some of the actual functions which are present in the pg_similarity extension:

Sanity-check the functions

Before using the functions, confirm identical strings return the maximum score

SELECT jarowinkler('acme corp', 'acme corp');    -- expect 1
SELECT dice('acme corp', 'acme corp');           -- expect 1

#if this returns 100 instead of 1, you're not looking at the correct function, stop and check \df before writing more SQL against it

Spot-check a known match/non-match pair against intended threshold

SELECT jarowinkler('Acme Corp', 'ACME Corporation'); 
SELECT jarowinkler('Acme Corp', 'Globex LLC');

Confirm regexp_replace strips ALL punctuation, not just the first hit:

SELECT regexp_replace('A&B CO., LTD.', '[^A-Z0-9]', ' ', 'g');

#the 'g' flag is required in PostgreSQL as Oracle's REGEXP_REPLACE replaces all matches but PostgreSQL's regexp_replace only replaces the first match without it

Expected output: score of 1 for identical strings, a sensible mid-to-high score for the known similar pair, and every punctuation character replaced in the regexp-replace test not just the first one

As we can see that functions are working as expected from the snapshot below

Run the comparison functions

The following dataset (which is a sanitized slice of a customer/vendor table) has been used in the examples within this post:

id   company_name        contact_name     email

101  Acme Corp           John Smith       jsmith@acme.com
102  ACME Corporation    John A. Smith    john.smith@acme.com
103  Acme Corp.          J. Smith         jsmith@acme.com
104  Globex LLC          Karen Lee        klee@globex.com
105  Globex L.L.C.       Karen A Lee      k.lee@globex.com

Rows 101–103 are the same account; 104–105 are the same account. A plain GROUP BY clause on the company_name column would count these as four and two distinct customers.

Levenshtein

Levenshtein is good for typos, weak for reordering

SELECT a.id, b.id,
       levenshtein(a.company_name, b.company_name) AS edit_distance
FROM customers a
JOIN customers b ON a.id < b.id
WHERE levenshtein(a.company_name, b.company_name) <= 4;

Expected output: “Acme Corp” vs “Acme Corp.” comes back at distance 1 (close). “Acme Corp” vs “ACME Corporation” scores worse than you’d like, purely due to length difference. Treat this as a first-pass filter, not the final word.

Jaro-Winkler

Jaro-Winkler is better for names and short identifiers

SELECT a.id, b.id,
       jaro_winkler(a.contact_name, b.contact_name) AS score
FROM customers a
JOIN customers b ON a.id < b.id
ORDER BY score DESC;

Expected output: “John Smith” vs “John A. Smith” scores noticeably higher here than under Levenshtein, since Jaro-Winkler rewards matching prefixes. Good fit for person names and short fields like SKUs or ticket IDs.

Jaccard and Dice

Jaccard and Dice are good when word order doesn’t matter

SELECT a.id, b.id,
       jaccard(a.company_name, b.company_name) AS jaccard_score,
       dice(a.company_name, b.company_name)    AS dice_score
FROM customers a
JOIN customers b ON a.id < b.id
ORDER BY jaccard_score DESC;

Expected output: “Globex LLC” vs “Globex L.L.C.” scores well here even though character-level edit distance is high, because both functions treat strings as sets of tokens/q-grams rather than ordered sequences. Indifferent to punctuation noise and reordering.

Cosine

Cosine is robust for longer free-text fields

SELECT a.id, b.id,
       cosine(a.company_name, b.company_name) AS cosine_score
FROM customers a
JOIN customers b ON a.id < b.id
ORDER BY cosine_score DESC;

Expected output: the most forgiving score of the set. Scales reasonably to longer strings like full addresses. Use as a complement to Jaccard/Dice, not a replacement — they tend to agree on easy cases and diverge slightly on ambiguous ones, which is useful signal on its own.

Soundex

Soundex is catching phonetic near-misses

SELECT a.id, b.id
FROM customers a
JOIN customers b ON a.id < b.id
WHERE soundex(a.contact_name) = soundex(b.contact_name)
  AND a.contact_name <> b.contact_name;

Expected output: catches pairs like “Catherine” vs “Kathryn” that every function above will miss, since the strings barely overlap. Noisy on its own – use as a tiebreaker alongside a token-based score, not a standalone filter.

Build a composite match score

Blending multiple functions into one score instead of relying on a single metric:

SELECT
    a.id, b.id,
    a.company_name, b.company_name,
    ROUND(
        (dice(a.company_name, b.company_name)
       + jaro_winkler(a.contact_name, b.contact_name)
       + CASE WHEN a.email = b.email THEN 1 ELSE 0 END
        ) / 3.0, 3
    ) AS match_score
FROM customers a
JOIN customers b ON a.id < b.id
WHERE dice(a.company_name, b.company_name) > 0.4
   OR jaro_winkler(a.contact_name, b.contact_name) > 0.85
ORDER BY match_score DESC;

Set thresholds: auto-merge above a high score, queue for manual review in a middle band, leave low scores alone.

Results of the above composite match score:

Here is the demo video for your reference:

Checks to Perform Before Running this at Scale:

  • Check for index support: none of these functions have native index support the way pg_trgm does, via GIN/GiST trigram indexes. Hence a naive self-join will be slow beyond a few tens of thousands of rows.
  • Pre-filter (block) before fuzzy-matching: same first letter, same postal code, same normalized email domain run the expensive functions only within each block.
  • Normalize before comparing: lowercase, strip punctuation, collapse whitespace before calling any similarity function.
  • Run it as a batch job, not inline: write candidate-match pairs to a review table on a schedule rather than scoring live in an application path.

Migration Pitfall: Oracle Jaro-Winkler Scale Mismatch

This came up directly while migrating an Oracle vendor-matching procedure that used UTL_MATCH.JARO_WINKLER_SIMILARITY:

WHERE (
    REGEXP_REPLACE(UPPER(VENDOR.VENDOR_NAME), '[^A-Z0-9]', ' ')
        LIKE '%' || REGEXP_REPLACE(UPPER(VENDOR_NAME_IN), '[^A-Z0-9]', ' ') || '%'
    OR UTL_MATCH.JARO_WINKLER_SIMILARITY(
        REGEXP_REPLACE(UPPER(VENDOR.VENDOR_NAME), '[^A-Z0-9]', ' '),
        REGEXP_REPLACE(UPPER(VENDOR_NAME_IN), '[^A-Z0-9]', ' ')
    ) > MATCH_CERTAINTY_IN
)

Two things silently break when this is migrated to PostgreSQL without adjustment – neither throws an error, both just quietly return fewer matches.

Issue 1: Scale mismatch

UTL_MATCH.JARO_WINKLER_SIMILARITY returns an integer on a 0–100 scale. The PostgreSQL equivalent (whether pg_similarity’s jaro_winkler or a fuzzystrmatch-based wrapper) returns a float on a 0–1 scale. If the migrated threshold (MATCH_CERTAINTY_IN, e.g. 85) isn’t rescaled, similarity > 85 can never be true against a 0–1 score — the fuzzy-match branch of the WHERE clause silently stops matching anything, and only the exact/LIKE branch keeps working.

Fix: scale the score at the call site (score * 100) or normalize the threshold to 0–1, consistently across every call site. In our case, we wrote a wrapper functions which will be called first in the functions this function rounds the value and calls the jarowinkler function for similarity search

CREATE OR REPLACE FUNCTION public.jarowinkler_pct(
	s1 text,
	s2 text)
    RETURNS integer
    LANGUAGE 'sql'
    COST 100
    IMMUTABLE PARALLEL SAFE 
AS $BODY$
	  SELECT round(jarowinkler(s1, s2) * 100)::integer;
	--if the above change does not work then will proceed with below one
	--SELECT round((jarowinkler(s1, s2) * 100)::NUMERIC)::integer;

$BODY$;
ALTER FUNCTION public.jarowinkler_pct(text, text)
    OWNER TO postgres;

Issue 2: regexp_replace default behavior

Oracle’s REGEXP_REPLACE replaces all matches by default. PostgreSQL’s regexp_replace replaces only the first match unless you pass the ‘g’ flag:

-- Oracle: strips every non-alphanumeric character
REGEXP_REPLACE(UPPER(name), '[^A-Z0-9]', ' ')

-- PostgreSQL equivalent needs the flag explicitly
regexp_replace(UPPER(name), '[^A-Z0-9]', ' ', 'g')

Drop the ‘g’ and a name like “A&B CO., LTD.” only gets its first punctuation character replaced the rest survives untouched. Vendor and company names routinely carry more than one punctuation mark, so this hits the majority of real names, not an edge case.

Neither bug shows up in a quick smoke test with a handful of clean names – both show up weeks later as a slow, quiet drop in match rate. The sanity checks in part 2 of this post exist specifically to catch this class of issue before it ships.

Conclusion:

In this post, we walked through installing pg_similarity and confirming which similarity functions are actually available, then sanity-checked their output before trusting it (scale, expected values on known pairs). We compared Levenshtein, Jaro-Winkler, Jaccard/Dice, Cosine, and Soundex on the same dataset to see where each one earns its keep, combined several of them into a single composite match score, and covered blocking and normalization to keep that scoring fast at scale. We also walked through a real migration pitfall — scale mismatches and regexp_replace default-flag differences — that surface specifically when porting similarity logic over from Oracle.

A few key takeaways to keep in mind:

  • No single similarity function catches every kind of duplication. Typos, reordering, and phonetic near-misses each respond to a different metric — that’s the whole reason to reach for pg_similarity alongside pg_trgm rather than instead of it.
  • Always sanity-check a function’s output range before building a threshold on it. A silent scale mismatch (0–1 vs 0–100) won’t throw an error — it’ll just quietly return fewer matches.
  • Pre-filter (block) before running expensive similarity comparisons at scale. None of these functions have native index support like pg_trgm‘s GIN/GiST trigram indexes, so a naive self-join won’t hold up past a few tens of thousands of rows.
  • If you’re migrating similarity logic from another database, verify scale and default flag behavior explicitly. A syntactically correct port isn’t necessarily a functionally correct one.

pg_similarity gets you most of the way to a working dedup pipeline with nothing but SQL — but it works best combined with blocking, and only after you’ve tested it against known values first.

If you’re evaluating this for your own data, the cheapest first step isn’t picking a threshold — it’s running the sanity checks in session 2 against a handful of your own known-duplicate and known-distinct pairs before writing a single line of matching logic. The functions behave differently across datasets, and five minutes of spot-checking now saves you from debugging a silent scale mismatch three weeks from now.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top