Real-world genealogy graphs (as implemented in Kinova) cannot be modeled as simplistic binary trees:

  • Children have multiple biological parents (mother and father).
  • Multiple marriages create blended families and half-siblings.
  • Adoptions and cross-branch linkages produce multi-dimensional graph networks.

Simplistic column patterns like father_id and mother_id directly on a persons table cause table contention and deeply nested joins.

1. Relational Graph Pattern: Individuals vs Family Unions

In PostgreSQL, the cleanest relational abstraction decouples individuals (Persons) from parental unions (Family Unions / Marriages):

erDiagram
    PERSONS ||--o{ UNION_MEMBERS : spouse
    UNIONS ||--o{ UNION_MEMBERS : includes
    UNIONS ||--o{ PERSONS : parent_of

    PERSONS {
        uuid id PK
        string full_name
        string gender
        date birth_date
    }
    UNIONS {
        uuid id PK
        uuid union_type
        date marriage_date
    }
    UNION_MEMBERS {
        uuid union_id FK
        uuid person_id FK
        string role
    }

2. Ultra-Fast Graph Traversal via Recursive CTEs

To fetch an entire descendant or ancestral hierarchy up to $N$-generations in a single SQL query, we leverage PostgreSQL WITH RECURSIVE (Common Table Expression):

WITH RECURSIVE family_lineage AS (
    -- Anchor member: Initial root ancestor
    SELECT id, full_name, 1 AS generation_level
    FROM persons
    WHERE id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'

    UNION ALL

    -- Recursive member: Traverse children per generation
    SELECT p.id, p.full_name, fl.generation_level + 1
    FROM persons p
    INNER JOIN unions u ON p.parent_union_id = u.id
    INNER JOIN union_members um ON um.union_id = u.id
    INNER JOIN family_lineage fl ON fl.id = um.person_id
)
SELECT * FROM family_lineage ORDER BY generation_level ASC;

3. Indexing & Loop Protection

  • Covering Indexes: Place compound B-Tree indexes on (parent_union_id, id) to optimize recursive join traversals.
  • Cycle Detection: Use PostgreSQL 14+ CYCLE id SET is_cycle USING path clauses to guard against accidental cyclical anomalies in family data.

Official Documentation References: