Do you need a graph database? Recursive CTEs for blast-radius queries

-- ======================================================================
-- Q: You have graph-like relationships (asset → depends on → asset, user →
-- member of → group). Do you need a graph database?
--
-- For hierarchies and a few levels, no: PostgreSQL with a recursive CTE is
-- enough. A graph database (Neo4j, Neptune) is worthwhile when the queries are
-- mostly multi-hop paths (blast radius, shortest path, "who is connected to this
-- one within 4 hops") on a large graph.
-- UNION (not UNION ALL) discards duplicate rows and helps avoid looping forever
-- on cycles. The depth limit is a second safeguard.
-- ======================================================================

-- All assets affected if asset 10 goes down (blast radius)
WITH RECURSIVE impacted AS (
    SELECT dependent_id AS id, 1 AS depth
    FROM asset_dependencies WHERE depends_on_id = 10
  UNION
    SELECT d.dependent_id, i.depth + 1
    FROM asset_dependencies d JOIN impacted i ON d.depends_on_id = i.id
    WHERE i.depth < 6                         -- limit for cycles/cost
)
SELECT a.id, a.hostname, min(i.depth) AS distance
FROM impacted i JOIN assets a ON a.id = i.id
GROUP BY a.id, a.hostname ORDER BY distance;