Writing

Scaling to 1M Users: Building Duplicate Checks (Part Two)

Second article offering optimizations to my first article on building robust signup systems with deduplication logic

Having duplicate user records is non-negotiable for some applications. If your application cares about proper identity checks, strict no duplicates, advanced means of identifying users beyond “unique email”. You can have “John Doe” born on 24th March, 1987 with email [email protected], however here are three entries in your database with slightly different information, but they’re the same person.

The first article discussed a solution (you might want to read that first). However, that solution is good for a couple thousand users. Once your data grows to 500k — 1M+ people, the algorithm slows down significantly.

miky rola, a software engineer, asked great questions to verify scaling needs. His questions included: 

  • What is our plan for deduplication when the user table grows from thousands to millions?
  • Are we using any blocking/indexing method to reduce the number of candidate rows we compare against?
  • Have we considered using k-nearest neighbour (KNN) to retrieve only the top-k similar users instead of comparing against the whole users list?

Handling Data Growth

Our current solution scans the entire database every single time a signup is made. After scanning, we do a pairwise comparison (incoming user details against all the potential matches). Two issues arise with millions of users:

  1. Fetching potential duplicate users from the database (full scan) becomes expensive
  2. The set of potential matches can still be a large set (>200,000) and would take time to calculate a similarity score for all of them.

Can we change how we fetch potential duplicate users from the database? So that instead of checking every row, we smartly retrieve which data best fits. This is where Blocking / Indexing method comes in.

Blocking / Indexing

This is the most foundational fix and should be your first layer. Instead of querying broadly, you partition your dataset into blocks so you only compare a new signup against records that share at least one “blocking key.”

Common blocking strategies for your schema:

  • Phonetic blocking — Soundex or Metaphone on first/last name catches “Jon” vs “John”, “Smith” vs “Smyth”. You precompute and index these.
  • n-gram indexing — Index trigrams of the full name. PostgreSQL has native pg_trgm support with GIN indexes, which gives you fast fuzzy-match candidates without a full scan.
  • Sorted neighborhood — Sort the entire dataset by a key (e.g. soundex(lastname) + dob) and only compare records within a sliding window. Good for batch dedup, less useful for real-time signup flow.

For your use case, a composite blocking key like (soundex(lastname), dob_year) would dramatically shrink the candidate pool before you pull anything into the application.

kNN / Vector Search

This is the most powerful approach but also the heaviest to implement. The idea is to embed user records into a vector space where similar records are geometrically close, then use approximate nearest neighbor search (ANN) to retrieve candidates.

For structured identity data, your embedding can be fairly simple — concatenate normalized fields, encode categoricals, and use something like a shallow MLP or even just TF-IDF vectors over the name fields. Then index those vectors with HNSW (Faiss, Pinecone, pgvector) for sub-linear lookup time.

Where this shines in your context: it naturally handles fuzzy matches across multiple fields simultaneously, without you having to enumerate blocking keys. A record with a slightly wrong DOB but matching name and phone will still land close in vector space.

The practical concern is that this requires either a managed vector DB or adding pgvector to your Postgres setup, plus you need to decide when to re-index (on every signup? batched?).

New architecture

Other nice tools I came across