Introduction

Finding similar rows in a database can mean very different things depending on the data. Sometimes you want exact duplicates, sometimes near-duplicates such as names with spelling variation, and sometimes rows that are numerically close across several columns. The right query depends on which type of "similar" you actually care about.

Exact Matches and Near-Exact Matches

If the goal is deduplication, start with exact comparison on the fields that define sameness. For example, suppose a customer table should not contain duplicate combinations of email and phone number.

1SELECT email, phone, COUNT(*) AS duplicate_count
2FROM customers
3GROUP BY email, phone
4HAVING COUNT(*) > 1;

That query finds groups of rows that match exactly on both fields.

If you want the actual duplicate rows rather than grouped counts:

1SELECT c.*
2FROM customers c
3JOIN (
4    SELECT email, phone
5    FROM customers
6    GROUP BY email, phone
7    HAVING COUNT(*) > 1
8) d
9  ON c.email = d.email
10 AND c.phone = d.phone
11ORDER BY c.email, c.phone, c.id;

This is the cheapest and most reliable form of row similarity because it uses deterministic equality.

Fuzzy Matching for Text Columns

Things get harder when rows are "close" rather than identical. Names such as Jon Smith and John Smyth may represent the same person, but plain equality will miss them.

For PostgreSQL, a practical option is trigram similarity with the pg_trgm extension:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Then:

1SELECT a.id, b.id, a.full_name, b.full_name,
2       similarity(a.full_name, b.full_name) AS score
3FROM people a
4JOIN people b
5  ON a.id < b.id
6WHERE similarity(a.full_name, b.full_name) >= 0.6
7ORDER BY score DESC;

This self-join compares each pair once and returns likely matches. The threshold depends on your tolerance for false positives.

For large tables, pairwise comparison can be expensive. In practice, add a blocking step first, such as only comparing rows with the same postal code or first letter of the surname.

Numeric Similarity Across Several Columns

For numeric rows, similarity is often distance-based. Suppose you have products with price, weight, and rating, and you want rows close to a target product.

1SELECT p2.id,
2       p2.price,
3       p2.weight,
4       p2.rating,
5       SQRT(
6           POWER(p2.price - p1.price, 2) +
7           POWER(p2.weight - p1.weight, 2) +
8           POWER(p2.rating - p1.rating, 2)
9       ) AS distance
10FROM products p1
11JOIN products p2
12  ON p1.id <> p2.id
13WHERE p1.id = 101
14ORDER BY distance
15LIMIT 10;

This uses Euclidean distance. It is easy to understand, but it has an important weakness: columns on larger scales dominate the score.

That means you should usually normalize values first or compare on standardized columns. Otherwise a price difference of 10 may outweigh a large rating difference simply because the units are different.

Choosing a Strategy

A useful decision process is:

1. define similarity in business terms
2. pick a comparison method that matches the data type
3. reduce the candidate set before expensive comparisons
4. rank results and review threshold quality

For example:

• emails and IDs usually need exact equality
• person names often need fuzzy text matching
• measurements often need numeric distance
• documents often need token-based or vector-based similarity

Trying to solve all four with one generic SQL query usually ends badly.

Performance Considerations

Similarity queries often turn into self-joins, and self-joins can become quadratic. That is why indexing and prefiltering matter.

Useful tactics include:

• indexes on blocking keys such as city or postal code
• materialized normalized columns
• limiting comparisons to recent rows
• using specialized extensions or search systems for fuzzy text

If you need semantic similarity across large text corpora, a relational database alone may not be enough. Vector search or a dedicated search engine may be the more honest tool.

Common Pitfalls

The biggest mistake is not defining "similar" precisely. A database cannot infer whether you mean exact duplicates, edit distance, phonetic closeness, or numeric proximity.

Another common issue is pairwise matching every row against every other row. That works on tiny tables but becomes unmanageable quickly.

People also forget normalization for numeric distance. If features are on different scales, the similarity score becomes misleading.

Finally, fuzzy matching thresholds need evaluation. A threshold such as 0.6 may be acceptable in one dataset and terrible in another, so sample the results instead of trusting a number blindly.

Summary

• "Similar rows" can mean exact duplicates, fuzzy text matches, or numeric proximity.
• Use GROUP BY and equality for exact duplicate detection.
• For fuzzy text in PostgreSQL, trigram similarity is a practical starting point.
• Numeric similarity usually needs normalization before distance calculations.
• Performance depends on reducing the candidate set before expensive comparisons.