Load Test: connection_paths v1 vs v2

1. Setup

Target contacts

Local (collection 4386): 3349:wren_133, 3338:ellis_109, 3300:logan_94, 3690:jordan_106, 3439:emerson_117, 1033791:perfsynth1054_171, 1033638:perfsynth901_107

Sandbox5 (collection 728): 3083:sebastian_15k, 14080:kristin_11k, 23352:abdulrahman_13k, 84573:chris_225, 372722:gheran_41, 158317:enaho_151

2. Headline: v1 vs v2 at depth=2

Apples-to-apples comparison. Legacy can only do depth=2; v2 is benchmarked at the same depth to match.

3. v2 sweep — sandbox5 (real data)

Observations

• Depth 1 is essentially free. Pure UCC join with no recursion. 600–700 ms p95 at ~18 RPS sustained.
• Depth 2 is the production sweet spot. 5 s p95 — well above the original 250 ms SLO but under the 1500 ms backend timeout. Should improve as the DB buffer cache warms.
• Depth 3 doubles-to-triples latency. 20–30 s p95. Workable for "show me deeper paths" exploration, not for routine reads.
• Depth 4 has wide variance. 23–50 s p95 with max_paths=100 and small samples. The fanout cap (MAX_EDGES_PER_HOP=25) is doing its job (no >60 s timeouts), but the absolute latency is risky.

4. v2 sweep — local (synthetic Perf collection)

5. Implications for production rollout

1. v2 is non-negotiable. v1's 38 s p95 on sandbox5 would saturate Puma workers and page within minutes of real traffic. Roll out via the relationship_strength feature flag — flip per collection as v2 perf is verified.
2. Depth=2 is the production contract. Matches legacy semantic and lands ~5 s p95 on real data. ANALYZE after backfill + buffer-cache warming should push this closer to 2–3 s.
3. Depth=3 is acceptable for exploratory surfaces — playground, admin tools — but not for tab-loading reads. ~20–30 s p95.
4. Depth=4 is research-grade. Variable, sometimes 50 s. Keep behind explicit user action ("show deeper paths"), not on default loads.
5. max_paths matters far less than max_depth. At depth=2, max_paths=25 vs 100 are nearly identical (5.0 vs 4.9 s p95). The expensive work is the walk, not the result sort.

6. How to reproduce

Prereqs

• Docker dev environment up (make up)
• k6 installed (brew install k6)
• For sandbox5: a Bearer JWT from a confirmed user with shared UCC access in collection 728 (use admin-portal devtools snippet in the playground)

Local sweep

VUS=10 DURATION=20s ./backend/load/sweep.sh
# outputs:
#   backend/load/results/local/sweep-summary.csv
#   backend/load/results/local/sweep-summary.md
#   backend/load/results/local/raw/{impl}_d{depth}_p{paths}.json

Sandbox5 sweep

ENV=sandbox5 TOKEN=eyJ... VUS=10 DURATION=20s ./backend/load/sweep.sh
# outputs go to backend/load/results/sandbox5/

Narrow sweep

DEPTHS="2,3" PATHS_SWEEP="100" IMPLS="v2" ./backend/load/sweep.sh

Single-cell comparison (v1 vs v2)

./backend/load/compare_v1_v2.sh

7. Caveats

• Sandbox5 v1 cells have tiny sample sizes (11–13 reqs / 20s) because each request takes 30+ seconds. p95 is "the slowest of the few we got". Production load would be far worse.
• Buffer cache state varies between runs. First requests after deploy/restart pay disk I/O; warm cache is much faster. Sandbox5 v2 depth=1 numbers (600 ms) likely benefit from warm cache.
• Token expiry: 15-min TTL. Long sweeps need a fresh token midway, or re-mint between cells.
• Rails dev mode = 1 Puma worker. Local sweep results are good for testing the code path but not for absolute latency.

Cross-references

• Single-request perf benchmark: performance.html
• Backfill scale & prod planning: backfill-scale.html
• Interactive playground: playground.html
• Source: backend/load/sweep.sh, backend/load/connection_paths.k6.js, backend/load/compare_v1_v2.sh
• v2 service: backend/app/services/contacts/connection_paths/finder_v2.rb
• Routing controller: backend/app/controllers/api/v2/collections/contacts/connection_paths_controller.rb

10. Optimization journey (phases)

Iterative perf work. After each phase we re-run the sweep on local + sandbox5 and capture the delta. The numbers at the top of this doc are Phase 0 (baseline) — uniform MAX_EDGES_PER_HOP=25. Each phase below replaces or adds to the baseline.

Phase 1 — Lower per-hop edge cap (MAX_EDGES_PER_HOP: 25 → 15)

What changed: Recursive walk now follows at most 15 most-recent edges per hop instead of 25. Bounds worst-case walk rows at depth 3 from 15,625 → 3,375 (4.6× less work).

Sandbox5 sweep — Phase 0 vs Phase 1 (real data)

Local sweep skipped from the analysis — Rails dev mode (1 Puma worker, cold caches, background-process noise) makes cap-tuning numbers too volatile to read. See full local CSV at backend/load/results/local/sweep-summary.csv for the data, but trust sandbox5 for the verdict.

Commit: e1ddc72845 — deep_finder: lower MAX_EDGES_PER_HOP 25 → 15.

Phase 2 — Broaden in-network UCC partial index to all NETWORK_SOURCES shipped

What actually shipped: the existing partial index index_ucc_in_network_membership was scoped to source = 5 (shared) only. Commit c02cafdd72 had broadened DeepFinder's in-network filter to source IN (0, 1, 2, 3, 5, 10) weeks earlier, but the partial predicate was never updated. With the predicates out of sync the planner couldn't use the partial index and fell back to index_user_contact_collections_on_contact_id (wide, single column) plus an in-row filter — one heap fetch per probe.

The fix is a one-line predicate broadening:

# db/migrate/20260515121009_broaden_in_network_membership_index_to_network_sources.rb
remove_index :user_contact_collections, name: 'index_ucc_in_network_membership', algorithm: :concurrently
add_index :user_contact_collections, [:contact_id, :collection_id],
  name: 'index_ucc_in_network_membership',
  where: 'source IN (0, 1, 2, 3, 5, 10) AND user_id IS NOT NULL',
  algorithm: :concurrently

No SQL change. DeepFinder is unchanged; the planner picks the broadened partial automatically once source IN (...) is subsumed by the partial WHERE. Confirmed via EXPLAIN ANALYZE on six sandbox5 targets — every plan now reads index_ucc_in_network_membership.

Sandbox5 single-shot EXPLAIN ANALYZE at depth=4 (cold cache)

Sandbox5 sweep — Phase 1 vs Phase 2 (10 VUs · 20 s · 6 contacts)

Commit: 0a89e1f734 — ucc: broaden in_network_membership partial to NETWORK_SOURCES.

Phase 3 — Reverse the UCC partial-index column order shipped

Hypothesis: Phase 2's partial is (contact_id, collection_id) — fine for "is this contact in any collection", but our actual access pattern is the opposite. DeepFinder's recursive lateral does ~100k EXISTS probes per request, all with the same fixed collection_id and ~100k different contact_ids. Putting collection_id first in the composite means every row for collection 728 lives in a contiguous btree slice. Once that slice is in the buffer pool, every probe within the request is a hot-cache hit.

Shape: a sibling partial index with reversed column order. Same WHERE clause. Old index kept in place — planner picks whichever wins on stats.

# db/migrate/20260515160233_add_ucc_collection_contact_in_network_index.rb
add_index :user_contact_collections, [:collection_id, :contact_id],
  name: 'index_ucc_collection_contact_in_network',
  where: 'source IN (0, 1, 2, 3, 5, 10) AND user_id IS NOT NULL',
  algorithm: :concurrently

Original estimate was 1.5–2.5× cold-cache win. Actual win was an order of magnitude bigger — the warm-slice effect compounds across the ~3,500 lateral iterations at depth=4.

Sandbox5 single-shot EXPLAIN ANALYZE at depth=4 (warm cache, 3 runs averaged)

Cold-cache on the very first run was 1–2 s (slice load). Every subsequent run is 40–60 ms — the collection's btree slice stays in the buffer cache.

Sandbox5 sweep — Phase 2 vs Phase 3 (10 VUs · 20 s · 6 contacts)

Why the win was bigger than predicted: the pre-shipping estimate assumed btree-pages-loaded-into-buffer would be a marginal effect on top of an already-cached index. In reality, the (contact_id, collection_id) partial scattered collection 728's rows across the entire btree's leaf pages (one slice per contact_id, of which there are 459k). The reversed (collection_id, contact_id) partial collapses all of collection 728 into one contiguous slice (~459k rows in a few MB of leaf pages). The buffer-pool footprint per request collapses by orders of magnitude. Locality won far harder than expected.

Commit: 0fc6718886 — ucc: reversed partial index (collection_id, contact_id) for in-network EXISTS.

Phase 4 — Two Ruby-side fixes (over-fetch bug + push sort into SQL) shipped

Phase 3 looked complete. Then we re-benched on a different day and the same code returned 30-second p95 numbers instead of 300 ms. Two issues compounded:

1. Sandbox5 was running with one backend container (desired/running 1/1). The original Phase 3 sweep had landed during a window when sandbox5 was autoscaled to a larger fleet — that's why throughput sustained 29 RPS. With one container and 10 VUs from k6, the per-container queue depth explodes.
2. A real code-level over-fetch bug in FinderV2 was multiplying response sizes 5–10× at max_depth > 2. Single-container saturation made the bug visible; multi-container autoscaling had been hiding it.

Both fixes shipped together as Phase 4.

4a. FinderV2 over-fetch bug at max_depth > 2

FinderV2#call multiplies DeepFinder's limit by OVER_FETCH_MULTIPLIER = 10 to give the legacy depth=2 sort + grouping a wider candidate pool. That logic only runs for depth=2; for depth>2 the early-return passes deep_result.paths through unchanged. But the over-fetch was still happening, so the API was returning 5–10× more paths than the user asked for.

Concretely, for max_depth=4 max_paths=100:

The fix is one conditional in FinderV2:

deep_limit = max_depth > 2 ? limit : limit * OVER_FETCH_MULTIPLIER

For depth>2 callers, request limit directly; for depth=2 keep the legacy over-fetch (still needed for the grouping pool).

Commit: c7dd2b24a3 — finder_v2: skip over-fetch at depth>2 (was 5-10x response bloat).

4b. Push DeepFinder sort into SQL + drop SQL_OVER_FETCH_MULTIPLIER 25→5

Originally DeepFinder over-fetched 25× the public limit so Ruby could sort the richer pool by [depth, recency, user-vantage]. That meant a max_paths=100 request parsed 2,500 PG-array rows in Ruby even though only 100 ever made it to the response.

The recency tiebreaker (max of date_to across the path, NULL treated as today) and user-vantage tiebreaker (ucc.user_id = ?) are both expressible in SQL:

ORDER BY
  (b.depth + 1) ASC,
  (SELECT max(COALESCE(t, CURRENT_DATE)) FROM unnest(b.to_path) AS t) DESC NULLS LAST,
  CASE WHEN ucc.user_id = ? THEN 0 ELSE 1 END
LIMIT ?

With SQL doing the sort, the over-fetch drops to 5× (still needed for the truncated/total reporting), Ruby parses 5× fewer rows, no Ruby re-sort.

Commit: 561243c6a9 — deep_finder: push sort into SQL ORDER BY, drop over-fetch 25x -> 5x.

Phase 4 measured win (sandbox5, 10 VUs · 20 s · single backend container)

Same sandbox5 single-container state for all three columns; same load shape; same code path. The first column is the broken state that surfaced after the FinderV2 bug had time to compound under saturation:

RPS climbed proportionally:

• v2 d=4 mp=25: 0.71 → 6.44 RPS (9×)
• v2 d=4 mp=100: 0.22 → 2.04 RPS (9×)