Lorixen.
EN

Typo-tolerant customer search with PostgreSQL pg_trgm

Short answer: Staff searching your admin panel or CRM for "Mcdonald" when the record says "MacDonald", or "Jonh Smith" instead of "John Smith", often get no results, then create a duplicate customer. If your app runs on PostgreSQL, the pg_trgm extension makes customer lookup forgiving of typos and spelling variants by comparing strings on shared three-letter chunks, and a GIN or GiST index keeps it fast on large tables. Combine it with exact matching on email and phone, show the best matches first, and keep the threshold tuned to your data.

Why exact search fails staff

Most admin search boxes run a query like "name contains the typed text". That works when staff type exactly what was stored. Real life is messier:

  • Typos: "Thompsn", "Jonh".
  • Spelling variants: "Mcdonald" and "MacDonald", "Steven" and "Stephen".
  • Different order: "Smith John" against "John Smith".
  • Accents and punctuation: "Zoë" against "Zoe", "O'Brien" against "OBrien".

When search finds nothing, staff on the phone with a customer assume the customer is new and create another record. Months later, you have two or three accounts for the same person, split order histories and confused support.

How trigram matching works

PostgreSQL's documentation for the pg_trgm module explains the idea: a trigram is a group of three consecutive characters taken from a string, and the similarity of two strings can be measured by counting the trigrams they share. The documentation notes this simple idea is very effective for measuring the similarity of words in many natural languages.

The module provides:

  • A similarity function returning a number between 0 and 1.
  • A % operator that returns true when two strings are more similar than a threshold, documented as defaulting to 0.3 and adjustable through the pg_trgm.similarity_threshold setting.
  • A distance operator, <->, which is one minus the similarity, useful for sorting nearest matches first.
  • Word-similarity variants that compare the search term with parts of a longer string, useful when a name field contains first and last names.

The documentation also notes that similarity comparisons are case-insensitive in a default build.

Making it fast

Without an index, comparing the search term with every row is slow on large tables. pg_trgm provides operator classes for both GIN and GiST indexes, gin_trgm_ops and gist_trgm_ops. The documentation gives a typical query that selects rows where the column is similar to the search word, ordered by similarity from best to worst, and notes the index makes this fast even over very large data sets.

The same indexes also support LIKE and ILIKE searches, including patterns with a leading wildcard such as "%smith%", which regular B-tree indexes cannot help with. So one index improves both your existing "contains" search and the new fuzzy search.

Index the columns staff actually search, often a combined "full name" column or expression, plus company name.

Designing the lookup

Fuzzy matching is a tool, not the whole search. A good customer lookup:

  1. Tries exact identifiers first. If the input looks like an email address, phone number, account number or order number, search those exactly, after normalising formatting such as spaces in phone numbers.
  2. Then searches names fuzzily, ordered by similarity, with the best few results shown.
  3. Shows enough detail to choose correctly: email, postcode or city, last order date, so staff do not pick the wrong John Smith.
  4. Highlights possible duplicates when creating a new customer: "Similar customers already exist" with the matches listed.

Tuning the threshold

A threshold that is too low returns lots of irrelevant names; too high and typos are missed again. Test with real searches your staff make: collect a sample of names that previously failed, try a few thresholds on a copy of your data, and pick the one that finds the right person near the top without flooding the list. Short names, such as "Li" or "Ng", produce few trigrams and behave differently, so consider exact or prefix matching for very short inputs.

Rolling it out safely

Enabling the extension needs the right database privileges; the documentation notes pg_trgm can be installed by non-superusers with CREATE privilege on the current database. Build the index on a copy of production first to see how long it takes and how large it is, then create it in production during a quiet period, using concurrent index creation so the table stays writable. Release the new lookup to a few staff first, gather the searches that still fail, and adjust before switching everyone over.

Other tools PostgreSQL offers

PostgreSQL's fuzzystrmatch module offers other techniques, such as Levenshtein distance and phonetic codes like Soundex and Metaphone. Its documentation cautions that soundex, metaphone and the double metaphone functions do not work well with multibyte encodings such as UTF-8, and suggests daitch_mokotoff or levenshtein for such data. For names from many languages, trigram similarity is often the more practical starting point, with unaccenting of text before comparison if accents cause misses.

A worked example

Picture a hypothetical Ottawa home-services company whose dispatch team searched a PostgreSQL-backed admin panel for customers calling in. Exact search missed variants such as "McKenzie" and "Mackenzie", and the team regularly created duplicate customers.

A developer enabled pg_trgm, added a GIN index on the customer full-name expression, and changed the lookup to try phone and email exactly first, then return the ten closest names ordered by similarity, with address and last booking shown beside each. The new-customer form now warns when a similar customer exists. Dispatchers found callers on the first search far more often, and new duplicate records became rare.

FAQ

Do we need a separate search engine for this?

Not for customer lookup in a typical business app. PostgreSQL can handle it. Large product catalogues with ranking needs may justify a dedicated engine.

Will the index slow down saving records?

Slightly, as with any index. For customer tables this is rarely noticeable.

Can it fix existing duplicates?

It can help find likely pairs for staff to review and merge. Automatic merging is risky.

If your staff keep creating duplicate customers because search can't find them, tell us what your admin panel is built on.

Sources

WhatsAppBook a call