Entity Resolution at 900M Scale

Entity resolution sounds like a matching problem. At scale, it’s a search problem — and the search space is what kills you.

At Vieu (product: seeqe.com), I build data pipelines that enrich a B2B relationship intelligence graph. The graph has one job: given a salesperson trying to reach someone at Company X, find a warm introduction path through their existing network. The graph has ~900M person nodes sourced from LinkedIn, with pre-computed edges representing real-world relationships — co-worker, co-student, co-athlete, co-author.

Every new edge type starts with the same problem: you have an external data source (NCAA rosters, academic publications, nonprofit boards) with entity mentions (“Aman Jain, MIT, 2023”), and you need to link each mention to the correct person node in a 900M-row Postgres table. Get it right and you’ve added a warm introduction path that didn’t exist before. Get it wrong — link a mention to the wrong person — and you’ve fabricated a relationship that could embarrass someone in a sales call.

This post covers two entity resolution pipelines I built: the NCAA co-athlete pipeline (linking college athletes to person nodes) and the pubnet co-authorship pipeline (linking academic authors to person nodes). Same graph, same core problem, very different data characteristics — and the contrast between them is where the useful lessons live.

1. The Graph#

Vieu’s graph uses three databases. Postgres is primary (~90% of all data work), holding ~900M person nodes in public.person (921M rows, 1.3TB) and 384M education records in public.school_degree (292GB). OpenSearch indexes ~46M documents — roughly 5% of Postgres, skewed toward well-hydrated senior professionals. MongoDB is rarely used.

Person nodes carry: person_id (format PERS-<uuid>), person_name, title, LinkedIn work experiences (company + start/end dates), and education (school + start/end years). Companies are also nodes. Edges are pre-computed by scheduled Lambda jobs and stored bidirectionally in pathing.edge_table — the application reads pre-computed paths, never calculates connections at query time.

The edge types before my work: co-worker (same company, overlapping employment dates) and co-student (same school, overlapping enrollment). I added co-athlete (same NCAA sports team, overlapping years) and designed co-author (academic co-authorship).

Co-authorship is architecturally different from the other three edge types in a way that matters for the product. Co-worker, co-student, and co-athlete edges are same-institution by construction — both endpoints sit inside one company or one school. Co-authorship is the only edge type in the graph that is natively cross-organizational. A warm introduction path into a target account requires an edge that crosses the account boundary, and co-authorship produces those at scale.

2. The Core Problem: Canonicalization#

Given a scraped entity — “Jane Smith, Harvard, 2019” from an NCAA roster, or “J. Smith, MIT CSAIL” from an OpenAlex author record — find the correct person_id in a 900M-row table. This is canonicalization: mapping a noisy external identity to a canonical node in the graph.

The asymmetry that dominates every design decision: false positives are worse than misses. If I link an NCAA athlete to the wrong person, the graph now contains a fabricated relationship. A salesperson trusts the graph, references the wrong connection in a cold outreach, and credibility is destroyed. A miss just means we don’t surface that path — the salesperson never knows it could have existed.

This asymmetry pushes every threshold, every scoring function, and every resolution rule toward precision over recall. The system is designed to under-resolve rather than mis-resolve.

The Methodological Foundation#

The foundational rule (inherited from Boro’s knowledge transfer before he left): build a ground-truth dataset first, then iterate. Never randomly tune heuristics on probabilistic matches.

Concretely: before running canonicalization at scale, manually verify ~50 matches against actual LinkedIn profiles. Use those 50 as ground truth to set confidence thresholds. Only then scale. For NCAA, I manually verified the first 20 RESOLVED matches. For pubnet, 100 edges were hand-labeled across the decision boundary.

This sounds obvious and is almost never done. The temptation is to tune thresholds until the numbers look right, without a fixed reference point for what “right” means. The ground-truth-first methodology caught systematic errors early — for example, graduation year inference was off by 2–4 years for ~43% of Harvard athletes due to how class_year labels mapped to actual graduation dates. Without ground truth, that error would have silently inflated false positives.

3. NCAA Co-Athlete Pipeline: Direct DB Resolution#

The NCAA pipeline links college athletes to person nodes. The data source is NCAA rosters — athlete name, school, sport, graduation year. The challenge is matching these against 900M person records with enough confidence to write an edge.

Blocking: How Candidate Pairs Are Generated#

The first version used Google Custom Search (SERP) to find LinkedIn profiles. I ran a proof-of-concept on 98 tennis athletes across two deliberately chosen schools — Harvard (high LinkedIn density) and Norfolk State (HBCU, D1, low LinkedIn density).

Result: 74% discard rate. Of 98 SERP queries, only 25 athletes were worth passing to the scoring step. The two schools failed for different reasons:

• Norfolk State — silence (70% zero results). Athletes simply aren’t on LinkedIn. Many were international athletes (Brazil, Eastern Europe, Latin America) who returned home without building US-facing professional profiles.
• Harvard — noise (48.3% “fixed spelling”). Google helpfully “corrected” athlete names, returning results for the wrong person entirely. Root cause: inferred graduation years were off by 2–4 years, so the query constraints didn’t narrow effectively.

The 74% discard rate killed the SERP approach for production — not because the precision was bad on surviving candidates, but because Google Custom Search costs money at scale and doesn’t give us control over the matching logic.

The pivot: direct Postgres queries. The public.person table has a btree index on person_name. Exact match lookups against this index are fast. The correct query pattern:

1. Name-first lookup against public.person using exact = match on the btree index
2. school_degree verification by the returned person_ids — never the reverse

The reverse pattern (school-first, then name) always times out. school_degree has 384M rows, and school-first queries without a person_id filter cause sequential scans that never complete against production traffic.

Tiered Query Strategy#

The pipeline uses six query tiers, relaxing constraints progressively:

graph TD
    A["Tier 1\nExact name + school + exact grad year"] -->|miss| B["Tier 2\nExact name + school + grad year ±1"]
    B -->|miss| C["Tier 3\nExact name + school (no year)"]
    C -->|miss| D["Tier 4\nFuzzy name variant matching"]
    D -->|miss| E["Tier 6\nOpenSearch rescue"]
    E -->|miss| F["NOT_FOUND"]

    A -->|hit| G["Score candidates"]
    B -->|hit| G
    C -->|hit| G
    D -->|hit| G
    E -->|hit| G

    class A green
    class B,C,D amber
    class E,F red
    class G blue

A removed tier is worth mentioning: Tier 5 used ILIKE with leading wildcards, which caused full table scans across the entire 900M-row person table. It was removed entirely. The lesson: any query pattern that bypasses the btree index is a production incident waiting to happen when the table has nearly a billion rows.

The Hidden Complexity: School ID Discovery#

Schools in Vieu’s Postgres are denormalized. Harvard alone has 33,724 rows in public.school — one for every sub-entity: Harvard Business School, Harvard Law, Harvard Medical, Harvard Extension, Harvard Divinity. Most sub-entity school IDs have linked_in_url = NULL, making them invisible to standard URL-based discovery.

I built a four-probe ID discovery strategy:

1. LinkedIn URL exact matching via btree index on public.school.linked_in_url
2. Exact school_name matching
3. Co-occurrence queries against resolved person IDs from prior pipeline runs
4. Sibling ID scanning

Shadow IDs (e.g., 272101974, 272177857, 141 for Harvard) have largely disjoint person_id sets because they represent different LinkedIn scraping passes. Missing any shadow ID means missing the athletes whose education records point to that specific scrape.

Scoring#

Each candidate is scored on a weighted sum of matching signals:

A finding that shaped the system: hometown and high school signals had a 0% fire rate. All 497 RESOLVED Norfolk State records scored an identical 0.75 (name_exact + school_match + grad_year_exact). Hometown strings were never normalized in the source data, and high school data is rarely present in LinkedIn profiles. The signals were kept in the scorer (they cost nothing to evaluate) but carried zero weight in practice.

Resolution Logic#

The uniqueness condition (no other candidate ≥ 0.70) is as important as the score threshold itself. A candidate scoring 0.85 is useless if there’s another candidate scoring 0.75 — you can’t distinguish them, and picking the higher score is a coin flip, not a resolution.

Results#

Two schools were deliberately chosen for contrast:

The 3× gap between them isn’t a pipeline issue — it’s a data density finding. Schools with more alumni on LinkedIn resolve at higher rates. This is calibration data, not a bug.

At the 5-school proof-of-concept scale (Harvard, Norfolk State, Navy, MIT, Michigan): ~48% canonicalization rate, producing ~160K candidate pairs. Harvard H3 validation confirmed 97.5% accuracy on RESOLVED matches and zero SCORER_DISAGREED cases — the system under-resolves rather than mis-resolves, exactly as designed.

Projected at 100 schools: ~2–3M PLAYED_TOGETHER edge pairs linking ~500K–800K unique person nodes.

Why OpenSearch Didn’t Work#

OpenSearch seemed like a natural secondary lookup — its test-pers-search-22051009 index has fuzzy matching, faceting, and a relevance scorer built in. I evaluated it as a rescue tier for athletes that Postgres couldn’t find.

The result: 98.3% false positive rate. Of 761 OpenSearch results, 748 were OS_NO_SCHOOL_MATCH false positives, with only 13 genuine OS_RESOLVED athletes. The root cause: OpenSearch has ~46M documents — 5% of Postgres. “Exactly one match” in OpenSearch doesn’t mean unique in the full person graph. The incremental lift over Postgres-only was ~0.5%.

OpenSearch is useful as a person search product feature. It is not useful as a canonicalization backend.

4. Pubnet Co-Authorship Pipeline: Dimension-Based Resolution#

The pubnet pipeline links academic authors to person nodes, producing co-authorship edges. The data sources are OpenAlex (~320.9M works, ~120.2M authors, CC0 licensed, actively updated) and OAG (~152.5M records, frozen since February 2024). Both inherit from Microsoft Academic Graph (shut down 2021), so there’s significant duplication between them.

The Load-Bearing Architectural Decision#

The NCAA pipeline resolves entity mentions one at a time — each athlete gets a tiered query cascade. This works at ~10K entities. Academic publications have ~69.7M author slots across 3 shards in our sources. One-at-a-time resolution would take months.

The architectural pivot: resolve against dimensions, never against the fact table. Stage 1 builds an author_dim — one row per distinct (source, author_id), aggregating institution affiliations across all papers. This reduces the resolution space from ~69.7M author slots to ~14.9M distinct author entities — a 4.678:1 reduction, same ratio at full scale. The same author appearing on 10 papers gets resolved once, not 10 times.

The Bronze-Silver-Gold Pipeline#

graph LR
    subgraph "Stage 0 · Bronze"
        A["OpenAlex\n320.9M works"] --> B["Normalize to\ncanonical work + work_author"]
        C["OAG\n152.5M records"] --> B
        B --> D["Flattened Parquet\n(work + work_author)"]
        D --> E["Load to Postgres\nUNLOGGED staging"]
    end

    subgraph "Stage 1 · Silver"
        E --> F["author_dim\n14.9M distinct entities"]
        E --> G["institution_dim\nnormalized institutions"]
    end

    subgraph "Stage 2-3 · Resolution"
        G --> H["Institution resolution\nvs company + school tables"]
        F --> I["Author canonicalization\nvs 900M person nodes"]
        H --> I
    end

    subgraph "Stage 4 · Gold"
        I --> J["Pairwise edge join\ncanonical ordering"]
        J --> K["Tier + confidence\nfiltering"]
        K --> L["Edge export\nto ingestion pipeline"]
    end

    class D amber
    class F violet
    class I blue
    class K green

Stage 0 (Bronze): Per-source adapters normalize raw data to canonical work and work_author records in flattened Parquet (not nested arrays — DuckDB aggregation is awkward with nesting). No admission filtering — even a paper with 5,104 authors is written in full; n_authors is preserved as the raw slot count. Poison rows (malformed records) are quarantined to per-shard poison files with the raw line, line number, and error class. Bronze is immutable and content-addressed by source version.

Stage 0c (Load to Postgres): Bronze Parquet → staging tables via COPY into UNLOGGED tables with synchronous_commit = off. Chunked INSERT with ON CONFLICT DO NOTHING. Statement timeout at 120 seconds. Sleep between chunks. Honors dry_run — when true, stages into a temp schema and logs would-be counts without touching production data.

Stage 1 (Silver — Dimension Construction): Builds author_dim (one row per distinct author entity) and institution_dim (one row per normalized institution string). Two-phase: Stage 1a produces per-shard partial Parquets, Stage 1b is a barrier merge into global author-space shards. Idempotent by overwrite.

Stage 2 (Institution Resolution): Three passes per institution string against the company index. Normalize + AND, strip-leading, then OR. Class-aware: company vs. school tracks are distinguished because the downstream affiliation matching uses different tables.

Stage 3 (Author Canonicalization): Per author entity, gated by resolved institution. Two-track affiliation: work_history.company_id for company track, school_degree.school_id for school track. Candidate retrieval → score → resolution status.

Stage 4 (Gold — Edge Materialization): Pairwise join with canonical ordering via LEAST/GREATEST COLLATE "C". Tiered by paper author count. Edge confidence = min of the two endpoint confidence scores. Deliberately no gold edge table — this is a defended design decision, not an omission. The gold layer produces edges directly for the existing ingestion pipeline.

Cross-Source Deduplication: canonical_work_id#

The original design was missing cross-source dedup entirely. The problem: author_key is (source, author_id), so the same human is two entities across OAG and OpenAlex, and the same paper is two publication_ids. Two researchers with 3 shared papers present in both sources would aggregate to n_shared_papers = 6, shifting a genuine strong-tier pair (2–5 authors) into medium (6–15).

The solution: canonical_work_id = COALESCE('doi:' || normalize_doi(doi), publication_id). Papers with DOIs collapse across sources for free; papers without DOIs stay self-canonical. One derived column, computed in bronze with no join and no cross-reference table, eliminates an entire class of duplicate-counting bugs. COUNT(DISTINCT canonical_work_id) replaces COUNT(*) in every downstream aggregate.

A dirty-DOI guard handles the edge case of issue-level DOIs shared across entire journal volumes: any canonical_work_id mapping to more than N distinct works within a single source, or to works whose titles disagree beyond a threshold, is demoted back to self-canonical. The demotion count is an alarm metric.

Co-Authorship Edge Scoring#

The tier ladder for co-authorship edges is based on paper author count:

The ≤50-author filter reduces raw edges by ~71%, but removes noise that would dominate the graph. One paper with 2,963 authors alone fabricates ~4.4M pairs — more edges from a single paper than most entire data sources produce. These aren’t real relationships. Nobody on a 2,963-author paper knows the other 2,962 co-authors.

All four policy knobs live in one versioned edge policy config: tier thresholds, degree caps (top-250 per person, both endpoints must survive), confidence floors (0.55 minimum), and admission rules (which resolution statuses are edge-eligible). A repolicy run means bumping the version and re-running Stages 4–5 only — no re-acquiring, re-parsing, or re-resolving.

Results#

The honest numbers:

Resolution rates: OpenAlex canon rate 16.5%, OAG canon rate 17.9%. Joint Tier-1 edge resolution rate (both endpoints resolve) at ~3.5–3.8% — indicative, measured on an induced subgraph with only 1.19% institutional coverage. This killed the optimistic end of the prior yield estimate.

Yale slice (measurement run): 6.34M unique co-authorship edges, of which 5.4M are Strong-tier and cross-organizational. 38.32% of papers are single-author (zero edges). 18.26% of author pairs share two or more papers — a repeat-collaboration signal that strengthens the relationship.

The volume funnel: 141.4M raw author-pair slots → 89.9M after structural filtering (single-author removal, >50-author exclusion) → 14.7M after resolution → 5.4M after confidence and tier filtering. Each step is defensible. Resolution is the bottleneck by a wide margin, not the data. We hold 90M potential edges; somewhere between 1M and 8M are writable with current resolution rates.

5. What Worked and What Didn’t#

What Worked#

Postgres-primary, OpenSearch-secondary. Direct Postgres query with the btree index on person_name covers 80-90% of cases. OpenSearch’s 98.3% false positive rate and 5% coverage make it a rescue tier at best.

Name-first query pattern. Always query person by exact name match first, then verify school_degree by person_id. The reverse (school-first) never completes at production scale.

Build ground truth before iterating. Manually verifying 50 matches before scaling caught graduation year inference errors that would have been invisible in aggregate metrics.

Precision over recall. The 0.82 resolution threshold with a uniqueness condition (no other candidate ≥ 0.70) means the system under-resolves rather than mis-resolves. Harvard H3: 97.5% accuracy on RESOLVED, zero scorer disagreements.

Dimension-based resolution. Resolving 14.9M author entities instead of 69.7M author slots. The 4.678:1 reduction is the single biggest architectural win in the pubnet pipeline.

canonical_work_id. One derived column, no join, no cross-reference table, eliminates an entire class of duplicate-counting bugs. The simplest solutions to deduplication problems are the ones that work.

What Didn’t Work#

SERP as primary canonicalization. Google Custom Search works for prototyping but doesn’t scale. Direct DB query is the production approach; SERP is a data repair tool.

OpenSearch as primary lookup. 46M documents out of 900M. “Exactly one match” doesn’t mean unique in the full graph.

Inferred graduation years. Harvard’s class_year → grad_year mapping was off by 2–4 years for ~43% of clean matches. The +0.20 grad_year_exact signal actively hurt these athletes.

Hometown and high school signals. 0% fire rate. Hometown strings were never normalized. High school data is rarely in LinkedIn profiles. The signals sounded good in design and contributed nothing in production.

ILIKE with leading wildcards. Full table scan on 900M rows. Removed from the pipeline entirely. Any query pattern that bypasses the btree index is a production incident.

The Numbers I Reported as Findings, Not Failures#

74% discard rate (SERP PoC). This killed the SERP approach and led to the DB-direct architecture. Reporting a 74% discard rate is more valuable than hiding it — it’s a precision-first gate, not a pipeline failure.

3.5–3.8% joint resolution rate (pubnet). This killed the optimistic yield estimate. It also clarified exactly where the bottleneck is (resolution, not data) and what would improve it (better institution coverage, fuzzy name matching at scale).

98.3% false positive rate (OpenSearch). This permanently settled the “should we use OpenSearch for canonicalization?” question. The answer is no, and now we have the data to defend it.

6. Production DB Safety#

One thread runs through every design decision that isn’t about accuracy: don’t break the production database. The person table serves live application traffic. Every query I run shares the same Postgres instance.

The rules I learned (some the hard way):

statement_timeout=30000 on every connection. Thirty seconds. If a query hasn’t returned by then, it’s doing something wrong — probably a sequential scan.

Never pass >10K IDs in an ANY() clause. Above 500 is risky. The workaround: cache entity IDs as an in-process Python set (~100ms one-time), narrow to ~50 candidates via an indexed name predicate, then verify membership with a bounded ANY(...) lookup.

Never JOIN on school_id without a person_id filter first. Harvard has 33,724 school table rows. An unconstrained join explodes.

EXTRACT(YEAR FROM dates_to) is 12× cheaper than range-style date predicates. Discovered during query profiling. The simpler extraction avoids the planner choosing a sequential scan.

Never use ILIKE with a leading wildcard on large tables. This bypasses all indexes. Tier 5 was removed for this reason.

Bulk fetch patterns are forbidden. Never bulk-fetch large record sets from primary. Tier 1 filters on cheap match signals; expensive analysis runs only on high-confidence survivors.

These aren’t performance optimizations. They’re safety boundaries. A runaway query on a 900M-row table shared with live traffic is a production incident, not a slow notebook cell.

7. The Scoring Denominator Problem (and Why It Stays)#

A small technical detail that illustrates a broader principle about production scoring systems.

The pubnet author scorer uses: name_jw × 4 + affiliation_match × 2 + year_match × 2, divided by 10. The weights sum to 8, not 10. The /10 is residue from the NCAA five-signal scorer (name 4, school 2, year 2, hometown 1, high school 1), where hometown and high school were dropped after measuring a 0% fire rate. The denominator was never updated.

This means the maximum achievable score is 0.80, not 1.0. A CONFIRMED threshold of ≥ 0.70 actually means 7 of 8 achievable points = 87.5% — requiring name_jw ≥ 0.75 given perfect affiliation and year matches. That’s genuinely strict.

The temptation is to “fix” the denominator to 8. Don’t. If someone changes /10 to /8 without rescaling all thresholds, CONFIRMED drops from requiring name_jw ≥ 0.75 to name_jw ≥ 0.40, which would admit garbage. Every threshold, every validation metric, and every ground-truth label was calibrated against the /10 denominator. Changing it invalidates all existing measurements.

The broader principle: in a production scoring system, the denominator is an arbitrary convention — but the thresholds calibrated against it are empirical. Change the convention only if you’re willing to re-calibrate everything downstream. Usually, it’s not worth it.

Summary#

Entity resolution at graph scale isn’t a matching algorithm. It’s a system design problem with constraints that dominate every technical choice: production DB safety, false positive asymmetry, ground-truth methodology, and the irreducible fact that your external data source will never cover the full graph.

The pipeline architecture generalizes: blocking (reduce the search space), scoring (weight the signals you have), resolution (decide with a uniqueness condition, not just a threshold), and tiered confidence (not all matches are equal — some write edges, some need review, some are discarded).

The specific findings that transferred across both pipelines:

• Build ground truth first. 50 manually verified matches before scaling catches errors that aggregate metrics hide.
• Precision over recall. A missed edge is invisible. A wrong edge is a fabricated relationship.
• The query pattern matters more than the scoring function. Name-first with index coverage beats any clever fuzzy matching that causes a full table scan.
• Report discard rates as findings. A 74% discard rate that kills a bad approach is more valuable than a 90% match rate on the wrong methodology.
• The scoring denominator is a contract. Calibrate once, then never change the denominator without re-calibrating everything downstream.

The system under-resolves rather than mis-resolves. That’s not a limitation — it’s the design.

I build graph enrichment pipelines at Vieu. I’m Aman Jain. If you’re working on entity resolution, canonicalization, or knowledge graph construction, reach me at amanjain.codes.