Pilotbase showing pairs of customer rows whose names are 0.71 to 1.0 alike by pg_trgm similarity, same city, different e-mail addresses

A customer list that has been through a few years of web forms, imports and sales people typing fast never has clean duplicates. It has Jon Smith and John Smith, SMITH, JOHN, J. Smith, Smith Plumbing Ltd and Smith Plumbing Limited. SELECT DISTINCT and GROUP BY name find none of them. Fuzzy matching finds them, and also finds the two genuinely different Mary Johnsons who live in different cities.

We wanted numbers, not a demo on six rows, so we built a made-up list where we know the answer: 50,000 rows holding 46,000 real customers and 4,000 extra rows that are the same customer typed differently. Then we ran pg_trgm on it at every threshold and counted how many of the pairs it reported were real duplicates (precision) and how many of the real duplicates it found (recall). All timings are PostgreSQL 18.6 on an Apple silicon Mac mini (10 cores, 16 GB), one session, warm cache. The scripts are at the end.

The test list: 50,000 rows, 4,720 duplicate pairs

Names follow real frequencies: first names from the US Census 1990 name files, surnames from the 2010 Census top 20,000. 70% of customers are people, 30% businesses (<surname> <trade> <suffix>, like Diaz Bakery Ltd). Because the names follow real frequencies, common names repeat the way they do in real lists: 2,181 pairs of different customers have exactly the same name (case ignored). Every customer has their own e-mail address.

The 4,000 extra rows were made from a customer by one of these changes. Some customers got two extra rows, so the list holds 4,720 true duplicate pairs:

text
kind of change         example                                       pairs
typo (one letter)      Daniel Silvestri  ->  Daniel Silvesrti         1,366
case and spaces        Wendy Reed        ->  WENDY REED, '  Wendy  Reed '   933
company suffix         Diaz Bakery Ltd   ->  Diaz Bakery Limited        453
new e-mail, same name  wreed@...         ->  wendyreed919@...           417
initial                Martha Magoon     ->  M. Magoon                  355
surname first          Martha Magoon     ->  MAGOON, Martha             354
typo and new e-mail                                                     321
& vs and               Reed & Lewis Joinery -> Reed and Lewis Joinery   159
'The' in front         The Diaz Bakery Ltd                              155
trade left out         Morales Optics LLC ->  Morales LLC               155
nickname               Robert Hall       ->  Bob Hall                    49
apostrophe / hyphen    O'Brien           ->  OBrien                       3

10% of the extra rows also have a different city (someone moved, or a business has a second branch). The truth sits in its own table, true_pairs(a, b), which none of the matching queries can see.

Clean the names first

pg_trgm already ignores case and punctuation, so JOHN SMITH and John Smith score 1.0 without any help. What it can't know is that Ltd, Limited, & Co and a leading The carry no meaning. Those words are a large part of a short name's trigrams, so they push real duplicates apart. We kept the original column and added a cleaned copy as a generated column:

sql
create extension pg_trgm;        -- similarity(), %, gin_trgm_ops
create extension fuzzystrmatch;  -- levenshtein()

-- lower case, & -> and, drop apostrophes, other punctuation -> one space,
-- then drop a leading "the" and a legal suffix
create function norm_name(t text) returns text
language sql immutable parallel safe as $$
  select btrim(regexp_replace(
           btrim(regexp_replace(
             replace(replace(lower(t), '&', ' and '), '''', ''),
           '[^a-z0-9]+', ' ', 'g')),
         '^the |( and)? (ltd|limited|llc|inc|co|gmbh|bv|plc)$', '', 'g'))
$$;

alter table customers
  add column name_norm text generated always as (norm_name(name)) stored;

select norm_name('The Smith & Sons Plumbing Ltd.');   -- smith and sons plumbing
select norm_name('O''Brien, J.');                     -- obrien j

If the list arrives as a CSV, do the boring part before it reaches the database. csvCleaner trims stray spaces, collapses double spaces, drops exact duplicate rows and empty rows, and can set one column to Title Case, all in the browser. The legal-suffix rule is specific to your data, so that one belongs in SQL where you can see and change it.

What the similarity numbers look like

similarity(a, b) splits each string into words, pads each word with two spaces in front and one behind, and takes every run of three characters: Jon Smith becomes {" j"," s"," jo"," sm",ith,jon,mit,"on ",smi,"th "}. The score is the shared trigrams divided by all distinct trigrams of the two. The % operator is true when that score is at least pg_trgm.similarity_threshold (default 0.3). Some pairs to calibrate your eye, with the Levenshtein distance (edits needed) next to it:

text
a                    b                         similarity  levenshtein
John Smith           JOHN  SMITH                    1.000            1
John Smith           Smith, John                    1.000           10
Smith Plumbing       The Smith Plumbing Co          0.682            7
Smith Plumbing Ltd   Smith Plumbing Limited         0.640            4
John Smith           Jon Smith                      0.615            1
John Smith           J. Smith                       0.583            3
John Smith           John Smyth                     0.571            1
John Smith           Joan Smith                     0.571            1   <- a different person
Ann Lee              Ann Li                         0.500            2
Robert Smith         Bob Smith                      0.353            4

Two things show up already. Word order doesn't matter to trigrams (Smith, John scores 1.0, Levenshtein says 10), which is good for lists where some people type the surname first. And John Smyth and Joan Smith score exactly the same: a one-letter typo and a different person look identical to any string measure. Only another column can tell them apart.

Thresholds on 50,000 rows: precision and recall

We stored every pair scoring 0.3 or more, once on the raw names and once on the cleaned ones, and joined each against the truth. Recall is out of the 4,720 true pairs:

text
                      raw names                         cleaned names
threshold   pairs found  real  precision  recall   pairs found  real  precision  recall
0.3           4,031,327  4,699     0.1%    99.6%     5,057,361  4,698     0.1%    99.5%
0.4             888,303  4,642     0.5%    98.3%     1,367,766  4,637     0.3%    98.2%
0.5             152,580  4,492     2.9%    95.2%       179,659  4,476     2.5%    94.8%
0.6              23,029  3,890    16.9%    82.4%        23,193  3,895    16.8%    82.5%
0.7               7,696  2,732    35.5%    57.9%         9,070  2,848    31.4%    60.3%
0.8               4,962  2,047    41.3%    43.4%         6,929  2,360    34.1%    50.0%
1.0               3,898  1,653    42.4%    35.0%         6,479  2,234    34.5%    47.3%

Read the bottom row first. Even names that are identical after cleaning are the same customer only 34.5% of the time in this list, because common names repeat and because cleaning makes Smith Plumbing Ltd and Smith Plumbing LLC (two firms) the same string. No threshold on the name alone gives a merge list you could run unattended: at 0.5 you catch 95% of duplicates buried in 180,000 pairs; at 0.7 the list is 9,070 pairs, two thirds of them wrong, and you've lost 40% of the duplicates.

Cleaning helped where we expected: at 0.7 it found 116 more real pairs, and at 1.0 it found 581 more (suffix and The changes become exact matches). It also created more same-string pairs of different businesses, which is why its precision at the top end is lower. Recall at 0.5 by kind of change, on cleaned names:

text
kind of change        pairs   found at 0.5   found at 0.7   average similarity
case and spaces         933          99.5%          94.0%                 0.97
surname first           354          99.7%          94.4%                 0.97
company suffix          453          99.8%          95.8%                 0.98
'The' in front          155         100.0%          95.5%                 0.97
typo                  1,366          95.9%          35.8%                 0.67
& vs and                159          96.9%          32.7%                 0.72
initial                 355          80.6%           2.8%                 0.54
nickname                 49          75.5%           6.1%                 0.57
trade left out          155          52.9%          12.9%                 0.51

Typos are where the threshold bites: one changed letter in a short name removes up to three of its ten or so trigrams, so most typo pairs land between 0.5 and 0.7. Initials and nicknames hardly survive 0.7. Bob for Robert shares no trigram with it at all; the 75% found at 0.5 are the cases where the surname is long enough to carry the pair.

A second column decides: e-mail and city

The name finds candidates; something else has to confirm them. We scored every candidate pair with two cheap checks, same e-mail (case-insensitive) and same city, and tried a few rules:

text
rule                                              pairs found   real  precision  recall
name >= 0.5                                           179,659  4,476      2.5%   94.8%
name >= 0.5 and same e-mail                             3,312  3,312    100.0%   70.2%
name >= 0.5 and same city                              14,153  4,059     28.7%   86.0%
name >= 0.5 and (same e-mail or same city)             14,478  4,384     30.3%   92.9%
name >= 0.6 and (same e-mail or same city)              4,943  3,813     77.1%   80.8%

That points to a two-step process instead of one magic threshold:

text
step                                          pairs   real duplicates  precision
1. same e-mail: merge without asking          3,504             3,504     100.0%
2. no shared e-mail, name >= 0.6, same city:
   look at each pair                          2,096               966      46.1%
                          found in steps 1 + 2: 4,470 of 4,720 = 94.7%
next slice: name 0.5-0.6, same city           9,070               106       1.2%

Step 1 is safe in this list because every customer has their own address. Check that in yours first: info@ and accounts@ addresses shared by two firms, or one family address, break it. Count how many distinct names share each address before you trust it.

Step 2 is 2,096 pairs, a couple of hours by eye, and about half of them are real. That's the honest size of the job for 50,000 rows with this much noise. The remaining 250 duplicates are spread thin: the best next slice holds 106 of them in 9,070 pairs. Past that point, accept the loss or ask the customers.

sql
-- step 2 as a table you can review and annotate
create table review_pairs as
select a.id, a.name, b.id as other_id, b.name as other_name,
       round(similarity(a.name_norm, b.name_norm)::numeric, 2) as name_sim,
       a.email, b.email as other_email, a.city
from customers a
join customers b on a.name_norm % b.name_norm and a.id < b.id   -- threshold 0.6, below
where a.city = b.city
  and lower(a.email) <> lower(b.email);
-- run first:  set pg_trgm.similarity_threshold = 0.6;
alter table review_pairs add column same_customer boolean;   -- fill while reviewing
Pilotbase query editor on review_pairs filtered to Austin: 16 pairs such as Johnnie White / Johnnie White at 1.0, Landin Dentaal / Landin Dental at 0.81, Martha Magon / MAGOON, Martha at 0.77, each with both e-mail addresses

In Pilotbase this is a table you filter one city at a time, sort by name_sim, and fill in same_customer cell by cell; the grid exports to CSV when the merge is done somewhere else. The first rows of Austin show the whole problem: six pairs with identical names at 1.0, where only the e-mail addresses differ, and below them the typos (Landin Dentaal, Martha Magon) that are almost certainly the same customer.

Levenshtein: a tighter check for typos

levenshtein() from fuzzystrmatch counts single-character edits. It has no index support, so use it on the trigram candidates, not on the whole table. Over every candidate pair, counting only names that differ:

text
rule (cleaned names)                     pairs found   real  precision  recall
levenshtein = 1                                3,596  1,230      34.2%   26.1%
levenshtein <= 2                              20,551  1,692       8.2%   35.8%
levenshtein = 1 and same city                  1,254  1,114      88.8%   23.6%
levenshtein <= 2 and same city                 2,656  1,533      57.7%   32.5%

Distance 1 plus same city is the best-precision rule we found that doesn't use the e-mail: 89%. But it only covers typos and spacing; swapped order and suffixes score 4 to 10 edits. It's a good way to split step 2 into a quick pile (distance 1) and a careful pile. levenshtein_less_equal(a, b, 2) stops counting once the distance passes 2, which made it 3x faster than levenshtein() in our run (4.1 s against 12.4 s on 50 million pairs). Both are limited to 255 characters per input.

Speed: the index, the join and a GiST trap

Without an index, every pair is compared. On a 10,000-row slice (50 million pairs) that took 50 s with similarity() >= 0.5; the full 50,000 rows would be 25 times as many pairs. With a GIN index the self-join asks the index for each row's candidates instead:

sql
create index customers_name_trgm on customers using gin (name_norm gin_trgm_ops);
-- 50,000 rows: built in 97 ms, 2.4 MB (the table is 5.0 MB)

set pg_trgm.similarity_threshold = 0.5;
select a.id, b.id, similarity(a.name_norm, b.name_norm)
from customers a
join customers b on a.name_norm % b.name_norm and a.id < b.id;
text
threshold   pairs returned   time (GIN, 50,000 rows)
0.7                  9,070   12.3 s
0.5                179,659   29.8 s
0.3              5,057,361   57.3 s

A lower threshold costs more for two reasons: more pairs to return, and more index entries to recheck (at 0.3 each lookup pulled 464 candidates from the index to keep 101). So pick the threshold from the precision table before you run the big join, not after.

We also built a GiST index (gist_trgm_ops), because GiST is the one that supports ORDER BY name_norm <-> 'john smith' LIMIT 5, the nearest names. It was 4.7 MB against GIN's 2.4 MB, and the trap is what the planner did with both present: it picked GiST for the self-join, which ran 28 times slower (7.2 ms per lookup against 0.25 ms at 0.7). Timed on 1,000 single-name lookups at 0.5:

text
lookup of one name (threshold 0.5)        per lookup
GIN, name_norm % 'name'                      0.77 ms
GiST, name_norm % 'name'                     8.2 ms
GiST, order by <-> limit 5                   9.7 ms

Use GIN for finding duplicates. Add GiST only if you need nearest-first ordering for something like a "did you mean an existing customer?" check on a form, and then check with EXPLAIN that your batch query still uses GIN. If it doesn't, drop the GiST index for the clean-up and build it again afterwards.

MySQL and SQLite

Neither has pg_trgm. MySQL has SOUNDEX() and SOUNDS LIKE (soundex(a) = soundex(b)), and an ngram parser for FULLTEXT that is built for search, not for pair scores. SQLite has no fuzzy matching in the default build (soundex() only with a compile option; the spellfix1 extension has an edit distance if you load it). We measured SOUNDEX on MySQL 8.4 with the same cleaned names, as a stored generated column with an index:

sql
alter table customers
  add column name_sx varchar(200) as (soundex(name_norm)) stored,
  add index (name_sx);

select a.id, b.id from customers a
join customers b on b.name_sx = a.name_sx and a.id < b.id;   -- 0.30 s
text
MySQL 8.4, SOUNDEX of the cleaned name     pairs found   real  precision  recall
soundex equal                                   92,365  3,017       3.3%   63.9%
soundex equal and (same e-mail or city)          8,128  2,943      36.2%   62.4%

Fast, because it's a plain equality join, and it misses a third of the duplicates. SOUNDEX keeps the first letter and the order of the words, so Smith, John and every J. Smith fall out, and a typo in the first letter does too.

The other route is to compute the same trigram similarity by hand in SQL. It works on both engines (window functions are needed: MySQL 8, SQLite 3.25 and later) and gives exactly pg_trgm's answer: on SQLite 3.54 it returned the same 179,659 pairs at 0.5 that Postgres did. The trick that keeps it from comparing everything with everything is prefix filtering: if two names are at least 0.5 alike, they must share one of their rarest trigrams, so common ones like " j" are never used to pair rows.

sql
-- names already cleaned: lower-case words separated by one space
create table nums (n integer primary key);
insert into nums with recursive r(n) as (select 1 union all select n + 1 from r where n < 200)
  select n from r;

-- 1. trigrams per name, padded like pg_trgm ('  word '); drop the ones spanning two words
create table tri as
  select distinct c.id, substr(c.s, nums.n, 3) as t
  from (select id, '  ' || replace(name_norm, ' ', '  ') || ' ' as s from customers) c   -- MySQL: concat()
  join nums on nums.n <= length(c.s) - 2
  where substr(c.s, nums.n, 3) not like '%  ';
create index tri_t on tri (t, id);

-- 2. keep each name's rarest n - ceil(0.5 n) + 1 trigrams
create table tri_rank as
  select tri.id, tri.t, row_number() over (partition by tri.id order by f.freq, tri.t) as pos,
         count(*) over (partition by tri.id) as n
  from tri join (select t, count(*) as freq from tri group by t) f on f.t = tri.t;
create table pref as select id, t, n from tri_rank where pos <= n - ceil(0.5 * n) + 1;
create index pref_t on pref (t, id);

-- 3. candidates share a rare trigram; then the exact score shared / (n_a + n_b - shared)
create table cand as select distinct a.id as a, b.id as b, a.n as na, b.n as nb
  from pref a join pref b on b.t = a.t and a.id < b.id;
create table matches as
  select c.a, c.b, 1.0 * count(*) / (c.na + c.nb - count(*)) as sim
  from cand c join tri x on x.id = c.a join tri y on y.id = c.b and y.t = x.t
  group by c.a, c.b, c.na, c.nb
  having 1.0 * count(*) / (c.na + c.nb - count(*)) >= 0.5;

It's slow next to the GIN index. SQLite took 5.5 minutes for all 50,000 rows: 73 s to build the 42 million candidate pairs that share a rare-ish trigram, 259 s to score them. On MySQL 8.4 the scoring step over those 42 million pairs stopped with The table '/var/folders/.../#sql...' is full: the implicit temporary table for the GROUP BY outgrew its default limits. Adding a blocking condition fixed both size and time: with ca.city = cb.city in the candidate step, MySQL needed 82 s for 2.5 million candidates and 58 s to score them, and returned the same 14,153 pairs as Postgres' name >= 0.5 and same city rule. For a one-off clean-up that's fine; for anything bigger, block on a column you'd require anyway (city, postcode, first letter of the surname), or copy the table into a Postgres database for the afternoon and use pg_trgm.

Why the CSV tool alone isn't enough

csvCleaner has a "Find near-duplicate rows" option. It sorts the rows by their whole text and compares each row with its next 14 neighbours by edit distance. We replayed that method on the same 50,000 rows:

text
csvCleaner's neighbour check            pairs   real  precision  recall
on name, e-mail and city                  960    909      94.7%   19.3%
on the name column only                 4,644  1,201      25.9%   25.4%

High precision on whole rows, because a row that differs by one or two characters across name, e-mail and city really is a copy. But it finds a fifth of the duplicates: sorting puts Smith, John far from John Smith, and a new e-mail address changes the row too much. Use it as a quick look at a file before loading (it shows the first 200 pairs), then do the real matching in the database.

The whole process, in order

text
1. clean the file: trim, collapse spaces, drop exact duplicates   (csvCleaner)
2. load it; add name_norm (lower case, punctuation, legal suffixes)
3. merge pairs with the same e-mail, after checking addresses aren't shared
4. GIN index on name_norm; set pg_trgm.similarity_threshold = 0.6
5. self-join with % plus a second column (same city, same phone, same postcode)
6. review that list by eye; split it with levenshtein = 1 for the easy ones
7. merge: repoint orders and invoices to the kept id, then delete the other row

Step 7 is the one that hurts if done in a hurry: every table with a foreign key to customers needs its rows moved to the surviving id before the duplicate goes. Once two rows are known to be one customer, picking which row survives is the same problem as removing exact duplicates; Removing duplicate rows in SQL: keep the newest per key has that part for Postgres, MySQL and SQLite.

Review the pairs where you can see both rows, sort, filter and edit in the grid, on Postgres, MySQL, SQLite and more. Pilotbase is free for Windows, macOS and Linux, or Docker on a server.

Get Pilotbase

Clean the CSV before it reaches the database: trim spaces, drop exact duplicates and empty rows, see near-duplicate rows. In your browser, nothing uploaded.

Open csvCleaner

Related: A messy CSV into Postgres without surprises: profile, clean, then load · Case-insensitive text in Postgres: citext, lower() or a nondeterministic collation

Sources: PostgreSQL 18 documentation: pg_trgm (trigram extraction, similarity_threshold default 0.3, GIN and GiST operator classes, <-> only with GiST) and fuzzystrmatch (levenshtein 255-character limit, levenshtein_less_equal, soundex/metaphone not for multibyte text). MySQL 8.4 SOUNDEX(). Names: US Census Bureau 1990 first name files and 2010 surnames. The test list and every number above come from our own run on 2026-10-27: PostgreSQL 18.6, MySQL 8.4.11, SQLite 3.54.0; the list generator and queries can be rerun with the same seed.