Backfill Scale Reality Check

1. The numbers

1.1 Production universe — measured 2026-05-13 via prod reader DB

SELECT
  (SELECT COUNT(DISTINCT contact_id)
     FROM contact_work_experiences
     WHERE organization_id IS NOT NULL) AS contacts_to_backfill,
  (SELECT COUNT(*) FROM contact_work_experiences) AS total_cwes,
  (SELECT COUNT(*) FROM contact_work_experiences WHERE organization_id IS NOT NULL) AS cwes_with_org,
  (SELECT COUNT(*) FROM contact_work_experiences WHERE organization_id IS NULL) AS cwes_without_org,
  (SELECT COUNT(DISTINCT organization_id)
     FROM contact_work_experiences
     WHERE organization_id IS NOT NULL) AS distinct_orgs_in_cwe;

-- Run on prod to find the effective org count:
SELECT COUNT(DISTINCT cwe.organization_id) AS in_cwe,
       COUNT(o.id)                          AS exist_in_orgs_table
FROM contact_work_experiences cwe
LEFT JOIN organizations o ON o.id = cwe.organization_id
WHERE cwe.organization_id IS NOT NULL;

~11% of CWEs have no organization_id. Those contacts' employment is recorded as company_name strings only and will not produce edges until an upstream resolver links them to an organizations row. Separate ticket; not blocking GET-109.

1.2 Sandbox5 empirical throughput (2026-05-12 → 2026-05-13)

Observed edge-write throughput with optimized SQL on healthy Sidekiq (8 active workers):

• ~12,000 edges/min sustained
• ~10–60k edges per 5-minute window depending on which contacts are being processed (mega-org contacts spike higher)

1.3 Per-contact job latency — sandbox5, contact 372722 (104 CWEs)

40× speedup. Most prod contacts will be faster than this — 372722 was picked deliberately as one of the heaviest cases (104 CWEs across multiple large orgs).

2. Production projection

2.1 Expected total edges

Multiplying sandbox5's ~152 edges-per-contact (measured on the first 17k contacts) by 3.5M prod contacts:

3,508,418 contacts × 152 edges/contact ≈ 533M edges

2.2 Expected runtime

At sandbox5's measured 12k edges/min throughput on a healthy Sidekiq fleet (8 workers):

570M edges ÷ 12,000 edges/min ÷ 60 min/hr ≈ 790 hours per worker thread

That's ~33 days on one worker. With parallel workers (each independent), runtime divides linearly until the DB itself becomes the bottleneck.

2.3 Sidekiq concurrency budget

Each WorkOverlapEdgeWorker invocation:

• Holds 1 Postgres connection for the duration of the cursor scan.
• Uses bounded Ruby memory (batch_size of 1,000 rows × a few hundred bytes each ≈ <10 MB).
• Runs for 1–15 seconds on a typical contact, can spike to 60+ seconds on contacts at multiple mega-orgs.

100 concurrent workers = 100 long-held PG connections. Confirm RDS connection pool headroom before the maintenance window.

3. Read-path readiness — DeepFinder covering indexes

The backfill ships data; DeepFinder reads it. Without the covering indexes below, the depth-3 recursive walk hits 489 ms on real data — past the 250 ms SLO. With them, the same query lands at 9 ms in EXPLAIN ANALYZE (~130 ms warm-cache on real sandbox5 with 12.4M edges). The indexes must be in place before backfill starts: building them after 570M rows have landed would take hours on its own, and any DeepFinder traffic during that gap would time out.

3.1 Index design + UNION ALL rewrite

Two symmetric covering partial indexes — one per endpoint of the undirected edge — let the planner pull pre-sorted rows directly from the index, no heap fetch, no in-loop sort:

-- db/migrate/20260513213400_add_deep_finder_covering_indexes.rb
add_index :contact_connections, [:contact_a_id, :date_to],
          order: { date_to: :desc },
          include: %i[contact_b_id bridge_entity_id date_from],
          where: "kind = 'work_overlap'",
          algorithm: :concurrently,
          name: 'idx_cc_walk_a'

add_index :contact_connections, [:contact_b_id, :date_to],
          order: { date_to: :desc },
          include: %i[contact_a_id bridge_entity_id date_from],
          where: "kind = 'work_overlap'",
          algorithm: :concurrently,
          name: 'idx_cc_walk_b'

Why two indexes. contact_connections is undirected — a walk from contact X needs to find rows where X is either contact_a_id OR contact_b_id. A single composite OR-scan can't preserve sort order through the OR clause, so the planner falls back to a sort step. Splitting into two single-key indexes lets each branch read pre-sorted rows.

Why include (covering). The recursive walk needs bridge_entity_id, date_from, date_to on every hop. Putting them in the INCLUDE clause makes the index a covering index — Postgres serves the query from the index leaf pages without touching the table heap.

Why partial on kind='work_overlap'. The table will hold other connection kinds eventually (linkedin, etc.). The partial predicate keeps these indexes small and dense — only the rows DeepFinder cares about. Smaller index → fits in shared_buffers → cache-friendly.

The matching SQL rewrite. Without the UNION ALL split in DeepFinder#fetch_path_rows, the planner sees a single OR'd predicate and can't tell which index to use — it picks a worse plan. The recursive case rewrite forces it:

-- inside the recursive CTE (see deep_finder.rb fetch_path_rows)
INNER JOIN LATERAL (
  SELECT next_contact_id, bridge_entity_id, date_from, date_to
  FROM (
    (SELECT contact_b_id AS next_contact_id, bridge_entity_id, date_from, date_to
     FROM contact_connections
     WHERE contact_a_id = b.current_contact_id AND kind = 'work_overlap'
     ORDER BY date_to DESC
     LIMIT 25)              -- ← reads idx_cc_walk_a, already sorted
    UNION ALL
    (SELECT contact_a_id AS next_contact_id, bridge_entity_id, date_from, date_to
     FROM contact_connections
     WHERE contact_b_id = b.current_contact_id AND kind = 'work_overlap'
     ORDER BY date_to DESC
     LIMIT 25)              -- ← reads idx_cc_walk_b, already sorted
  ) merged
  WHERE next_contact_id <> ALL(b.contact_path)
    AND EXISTS (SELECT 1 FROM user_contact_collections ucc ...)
  ORDER BY date_to DESC
  LIMIT 25
) cc ON TRUE

3.2 EXPLAIN ANALYZE — before vs after

Measured on sandbox5 (12.4M contact_connections rows, depth-3 walk on contact 3083 — Sebastian Stadil, ~15k edges):

Pre-flight checklist — index hygiene

1. Run the migration first. db/migrate/20260513213400_add_deep_finder_covering_indexes.rb uses CONCURRENTLY + if_not_exists — safe on prod, idempotent. Finishes in seconds against an empty work_overlap partition.
2. Verify presence before kickoff: SELECT indexname FROM pg_indexes WHERE tablename='contact_connections' AND indexname IN ('idx_cc_walk_a','idx_cc_walk_b'); — must return both.
3. After backfill, run ANALYZE contact_connections. Autoanalyze threshold is 10% of table size; bulk inserts outpace it. Without manual ANALYZE the planner picks bad plans against stale stats.
4. Warm shared_buffers with sample DeepFinder queries (extended timeouts) before re-enabling user traffic. Cold-cache first reads dominate latency.

4. Known blockers

4.1 Sandbox5 Sidekiq stability — gating issue Open

Symptom: Every ~5 hours of running the backfill task, the MaintenanceTasks::TaskJob (the enqueuer) gets killed by Sidekiq shutdown and the framework's interrupted status doesn't resume. Workers continue draining whatever was already enqueued, but no new contacts get added to the queue until a human cancels and restarts.

Observed twice: Run #5 (2026-05-12 18:47, interrupted at 17,101/429,904), Run #7 (2026-05-12 22:57, interrupted at 16,801/429,904).

Likely root causes (not yet diagnosed):

• Sidekiq container restarted by orchestrator (ECS task replacement, k8s eviction)
• OOM kill (less likely now that streaming is in)
• Scheduled deploy or maintenance hook hitting nightly

Mitigations (in order of preference):

1. Investigate why Sidekiq restarts on sandbox5 and fix at the infra layer.
2. Add a Maintenance Tasks resume detector — a small cron worker that finds interrupted runs older than N minutes and re-enqueues them. Not in PR #6724; would be a follow-up.
3. Live with manual cancel-and-restart cycles. Loses progress between cycles but is correct (idempotent upserts).

4.2 Unique-jobs lock leakage on purge Documented

When we purge queue:default to start clean, the sidekiq-unique-jobs locks (created by lock: :until_executed) remain in Redis with infinite TTL (lock_ttl: null). Sandbox5 cleanup required deleting ~360k orphan locks separately. Document this in the prod runbook — purging only the queue without the locks will silently swallow re-enqueues for those contacts.

Cleanup pattern (run on prod redis if a purge ever happens):

# In Rails console with Sidekiq client connected to prod Redis:
deleted = 0
cursor = 0
loop do
  cursor, keys = Sidekiq.redis { |r| r.scan(cursor, match: 'uniquejobs:*', count: 1000) }
  Sidekiq.redis { |r| r.unlink(*keys) } if keys.any?
  deleted += keys.size
  break if cursor.to_i == 0
end
puts "Deleted #{deleted} uniquejobs locks"

4.3 11% of CWEs have no organization_id Out of scope

3 million CWE rows on prod have organization_id IS NULL. The contacts those rows belong to won't appear in contact_connections for that employer until an upstream company-resolution process links them. This is a coverage limitation customers may notice in DeepFinder. Not in scope for GET-109, but worth flagging on the GTM side.

5. Recommended prod backfill plan

Pre-flight — week before

1. Run the diagnostic queries (§1.1 plus the orphan-org check) and update this doc with the effective edge-count target.
2. Identify the 10–20 dominant mega-orgs. Spot-check EXPLAIN ANALYZE for the per-contact SQL on one contact at each.
3. Apply the DeepFinder index migration (20260513213400_add_deep_finder_covering_indexes.rb). Idempotent; finishes in seconds against an empty work_overlap partition. See §3.
4. Sandbox5 Sidekiq stability: investigate or accept. If accepting, build a small cron that auto-recovers interrupted Maintenance Tasks runs (~30 LOC follow-up PR).
5. RDS capacity check: confirm connection pool can absorb ~100 long-held connections concurrently.

Day of

1. Window: weekend or low-traffic block, ≥12 hours.
2. Scale Sidekiq: temporarily boost :default queue concurrency. Coordinate with ops.
3. Run: Maintenance::BackfillContactConnectionsByContactTask from /maintenance_tasks on prod backoffice.
4. Monitor: Sidekiq Busy/Queues tabs + SELECT COUNT(*) FROM contact_connections WHERE kind='work_overlap' AND computed_at > NOW() - interval '5 min' for live throughput.
5. Throttle if necessary: if customer query latency spikes, lower Sidekiq concurrency on :default mid-run.

Acceptance criteria

• contact_connections row count for kind='work_overlap' stabilizes (no significant deltas for 30+ minutes).
• Maintenance Tasks run status: Succeeded (not Interrupted or Cancelled).
• ANALYZE contact_connections run; planner stats refreshed against post-backfill row count.
• Spot-check 5 random contacts: DeepFinder returns paths within the 500 ms SLO (warm cache).

6. What's done vs what's left

Shipped on PR #6724 (008-contact-connections-table)

Follow-ups (not in #6724)

• Auto-recovery worker for stuck interrupted Maintenance Tasks runs (~40 LOC + spec).
• Investigation of why sandbox5 Sidekiq restarts every ~5 hours.
• Company-name-to-Organization resolver for the 11% NULL-org CWEs (separate ticket).
• Prod runbook for the maintenance window (cancel/restart, purge with lock cleanup, throttle).

7. Cross-references

• DeepFinder query latency: performance.html (different concern — read-path SLO at 250 ms)
• Synthetic seed task: Maintenance::SeedSyntheticConnectionGraphTask
• Per-contact worker: Contacts::ConnectionPaths::WorkOverlapEdgeWorker
• Per-contact service entry: Contacts::ConnectionPaths::WorkOverlapBackfillService.call(contact_id: X)