Graph DB vs Postgres for connection paths

1. TL;DR

The rest of this document explains why — with concrete numbers, side-by-side queries, and a decision record. Skim sections 6–9 if you only have five minutes.

2. The actual question

Spec 007 has two query patterns, and they are not equally hot:

The list view is an aggregation problem keyed on (collection_id, organization_id). The drill-in is a fixed 2-hop traversal with time-windowed bridges. Neither is multi-hop, neither requires shortest-path, neither needs centrality or community detection.

3. Query shape (visual)

The "graph" in 007 has fixed depth and fixed pattern. Two relationship types, one bridge:

What's actually being computed

• Direct connection: ViaC has CURRENT_AT TargetOrg directly (1 hop from contact set into org).
• Intro path: ViaC and TgtC share a BridgeOrg, with positive time overlap; TgtC has CURRENT_AT TargetOrg (2 hops).
• Bridge condition: same organization_id AND date_ranges intersect. This is a property filter on the WORKED_AT edges, not a path-finding question.

4. Why it looks like a graph problem

• The spec uses the word "path" everywhere.
• The data has people, organizations, and relationships — the textbook graph definition.
• A whiteboard sketch of the answer is a literal graph: nodes for contacts/orgs/users, edges for UCC and work-history.
• Cypher / openCypher reads more naturally than SQL for "find paths."

All of these are real, but they don't establish that a graph engine is the right runtime. They establish that the conceptual model is graph-shaped. Conceptual model and storage substrate are independent decisions.

5. Why it isn't deeply graph-shaped

Three structural reasons.

5.1 Depth is fixed at 2

007 has exactly one query pattern with two hops; there is no recursion, no transitive closure, no shortest-path. Graph databases earn their keep at depth ≥ 3, where SQL self-joins or recursive CTEs start to degrade. At depth 2, a well-indexed Postgres join is actually faster than most graph engines because it doesn't pay the per-edge object-graph traversal cost.

5.2 Aggregation, not traversal, is the hot path

The list view is "count direct + count intro per org, sort, render." That's a GROUP BY. Graph databases are bad at GROUP BY across thousands of rows — they're optimized for "find pattern X in the graph," not "summarize all matches per key." Postgres planners are very mature at this.

5.3 The interesting edges don't physically exist

The coemployment edge ("Mike worked alongside Sarah at Stripe 2020-2022") is not stored anywhere as a record. It's inferred from a JOIN of two contact_work_experiences rows where organization_id matches and date ranges overlap. So no matter what storage we pick — Postgres tables, Neo4j, Apache AGE — we face the same compute problem:

1. Precompute and materialize the inferred edges, OR
2. Compute at query time via a JOIN.

A graph DB doesn't change that fundamental choice. It changes the syntax; the compute stays the same.

6. Options evaluated

7. Performance comparison

Estimated p99 read latency for the two query patterns. Numbers are order-of-magnitude based on workload shape, not benchmarks; treat as rough guidance.

7.1 List view (50 orgs on the list, 10k shared contacts in collection)

7.2 Drill-in (one org, enumerate all paths)

Two takeaways:

• Precomputed Postgres wins both query patterns at this depth. The win comes from indexes, not from the query language.
• Network hop matters. Any separate-DB option pays a 30–100ms tax just for the round trip. At a 50ms budget, that's the entire budget.

8. Operational cost

What it costs to run, beyond the latency story.

9. SQL vs Cypher (the same query)

The drill-in question: "list every direct contact and every intro path into a single organization, scoped to one collection."

-- Direct contacts
SELECT contact_id, current_title, strongest_source
FROM   collection_org_current_shared_contacts
WHERE  collection_id = $1
  AND  organization_id = $2;

-- Intro paths
SELECT via_contact_id,
       other_contact_id,
       organization_id   AS bridge_org,
       overlap_from,
       overlap_to,
       overlap_months
FROM   contact_coemployment_edges
WHERE  collection_id = $1
  AND  other_contact_id IN (
         SELECT contact_id
         FROM   collection_org_current_shared_contacts
         WHERE  collection_id = $1
           AND  organization_id = $2
       )
ORDER BY overlap_to DESC;

// Direct contacts
MATCH (org:Org {id: $org_id})
      <-[:CURRENT_AT]-(direct:Contact)
      <-[:UCC_SHARED {collection: $cid}]-(u:User)
RETURN direct, u, 'direct' AS type;

// Intro paths
MATCH (org:Org {id: $org_id})
      <-[:CURRENT_AT]-(target:Contact)
      -[w1:WORKED_AT]->(bridge:Org)
      <-[w2:WORKED_AT]-(via:Contact)
      <-[:UCC_SHARED {collection: $cid}]-(u:User)
WHERE w1.date_from <= w2.date_to
  AND w2.date_from <= w1.date_to
RETURN via, target, bridge, w1, w2,
       'intro' AS type
ORDER BY w1.date_to DESC;

Observation: Cypher reads more like a sentence; SQL is more verbose. That's it. Cypher's edge-walking syntax doesn't reduce the work — both queries do the same JOINs, evaluate the same time-overlap predicate, and produce the same rows. At depth 2 with proper indexes, the planners run both in similar time. The Postgres version is the one we already know how to operate.

10. Scale walkthrough

Concrete sizing for a collection with 10,000 shared contacts (the upper end of what we expect for an active VC fund or accelerator network).

10.1 Edge cardinality

• 10,000 contacts × 5 past employers each = 50,000 work-history rows.
• Per past employer, average ~20 coemployees (people who happened to work there at any time). Most coemployees are not currently at any org we care about.
• After filtering to "the other side is currently at an org on at least one of our lists": ~5–20 useful edges per contact.
• Total edges per collection: ~100,000 rows in ContactCoemploymentEdge.

10.2 Storage

~50 bytes/row × 100,000 rows = ~5 MB per collection. Trivial. Indexes add ~3× — still under 20 MB.

10.3 Read at page-load

Index on (collection_id, organization_id); the list-view query returns 50 rows (one per org on the list). ~30ms p99 on standard Postgres hardware.

10.4 Write amplification

When a contact_work_experience row is added or updated:

• Find collections this contact is shared in (small, indexed lookup).
• For each collection, find candidate coemployees from contact_work_experiences by matching organization + time. Bounded query.
• Upsert ~10–100 edge rows. Cheap.

Per write event: tens to a few hundred milliseconds in a Sidekiq worker. Doesn't block user-facing requests.

11. Multiple edge types (education, investors, board peers)

Spec 007 v1 ships coemployment only, but the broader product intent treats education, co-investment, investor → employee, and board peer as valid intro paths too. Adding these is the most likely near-term schema change. This section answers: does adding them change the storage decision?

11.1 The five path types side by side

All five share the same shape: via_contact → bridge_entity ← target_contact. Only the bridge entity and the time-bound rule change.

11.2 Visual: same shape, different bridge

11.3 Schema strategy: per-type tables + unified summary

Two viable storage shapes. We recommend Option B.

contact_relationship_edges (
  collection_id      bigint,
  kind               enum,
  via_contact_id     bigint,
  other_contact_id   bigint,
  bridge_entity_type enum,
  bridge_entity_id   bigint,
  bridge_metadata    jsonb,  -- date ranges
  ...
)

ContactCoemploymentEdge
contact_coeducation_edges
contact_coinvestment_edges
contact_investor_employee_edges
contact_board_peer_edges

-- Plus the rollup the list view reads:
shared_list_network_summary (
  shared_list_id           bigint,
  organization_id          bigint,
  direct_count             int,
  intro_count_work         int,
  intro_count_edu          int,
  intro_count_investor     int,
  intro_count_board        int,
  intro_count_total        int,
  strength_score           numeric,
  top_intro_preview        jsonb,
  ...
)

11.4 How this changes the math

11.5 The non-storage risk: investor data acquisition

Coemployment and coeducation derive from data Getro already owns. Investor and board edges require a data deal or vendor pipeline:

This is the v1 vs v2 scoping question — it isn't a storage question. Whichever DB substrate we pick, the data has to come from somewhere.

11.6 Where multiple edge types could shift the storage decision

None of the five path types alone changes the answer. Combinations or extensions do:

For 007 v1 (coemployment) and even 007 v2 (all 5 types, still 2-hop), Postgres remains the right substrate. Revisit only if a 3+ hop mixed-type product feature is on the explicit roadmap.

12. When to revisit

Reconsider a graph layer if any of these become true:

None of these are in 007 or 008 today. If any land in a future quarter, the right move is to keep Postgres as source of truth and introduce a graph layer for the specific feature — not retrofit the entire stack.

13. What about 008?

Spec 008 (relationship strength) is not a graph problem at all. The hot computation is per-pair signal aggregation:

• For each (team_member, contact) pair: counters for emails sent/received in 12mo, meeting count in 24mo, last-contact recency, reply rate.
• Compare counters against tier predicates (Warm / Known / Cold rules).
• Roll up tiers per (contact, collection) for Reachability.

That's event-stream ingestion + counter rollup + threshold check. A graph DB doesn't help. You'd have to either store the interaction stats outside the graph DB (now you have two systems) or contort the data into edges-with-counters that no native graph algorithm benefits from. Adding a graph DB makes 008 worse, not better.

14. Recommendation

1. Build 007 v1 storage in Postgres tables, maintained by Sidekiq workers using Rails after_commit hooks. Start with ContactCoemploymentEdge, CollectionOrgCurrentSharedContact, and SharedListNetworkSummary.
2. Design the schema for multiple edge types from day one. Keep one table per edge kind (coemployment, coeducation, coinvestment, investor-employee, board-peer) and one unified SharedListNetworkSummary with intro_count_* columns nullable per kind. v1 ships with only the coemployment column populated; v2+ light up the rest without touching the hot read path.
3. Use the existing kernel logic from Contacts::ConnectionPaths::Finder as the per-write computation core (same bridge + time-overlap rule, same signal weighting). Reuse it across edge types — only the bridge entity and time predicate change.
4. Use Postgres recursive CTEs as a fallback for any drill-in question that needs traversal beyond what the precomputed edges cover. No extension needed.
5. Skip Apache AGE and dedicated graph DBs for now. Park them as "revisit if X happens" — see section 12, plus the multi-edge-type triggers in section 11.6.
6. Resolve the investor-data source question (Findem vs direct Pitchbook/Crunchbase vs manual) before locking 007 v2 scope. This is a data-acquisition question, not a storage question — but it gates the v2 schema's investor and board-peer tables.
7. Do not introduce graph storage for 008 under any scenario. It's the wrong tool for that workload.

15. Decision record

16. Open questions