Oracle CONNECT BY vs. Postgres Recursive CTEs: Translation Patterns and Performance

The Oracle CONNECT BY Model

Oracle's hierarchical query syntax uses three clauses: START WITH (defines the root nodes), CONNECT BY (defines the parent-child relationship), and optionally ORDER SIBLINGS BY (orders children within each level). Oracle provides several pseudo-columns that expose traversal metadata: LEVEL, CONNECT_BY_ROOT, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE, and SYS_CONNECT_BY_PATH.

-- Oracle: traverse an org chart hierarchy
SELECT
  LEVEL,
  employee_id,
  LPAD(' ', (LEVEL-1)*2) || first_name || ' ' || last_name AS name,
  manager_id,
  SYS_CONNECT_BY_PATH(last_name, '/') AS path,
  CONNECT_BY_ISLEAF AS is_leaf,
  CONNECT_BY_ROOT employee_id AS root_employee
FROM employees
START WITH manager_id IS NULL        -- root: employees with no manager
CONNECT BY PRIOR employee_id = manager_id  -- parent: prior row's employee_id = this row's manager_id
ORDER SIBLINGS BY last_name;

The PRIOR keyword identifies the parent side of the join. CONNECT BY PRIOR employee_id = manager_id means "connect this row to the row where employee_id (from the prior/parent row) equals this row's manager_id." Reversing PRIOR direction — CONNECT BY employee_id = PRIOR manager_id — traverses upward from a leaf to its ancestors.

The Postgres Recursive CTE Model

Postgres implements hierarchical traversal using WITH RECURSIVE. A recursive CTE has two parts separated by UNION ALL: the anchor (non-recursive base case) and the recursive member (the self-join that extends the result set one level at a time). Execution continues until the recursive member produces no new rows.

-- Postgres: equivalent org chart traversal
WITH RECURSIVE org_hierarchy AS (
  -- Anchor: root employees (no manager)
  SELECT
    employee_id,
    first_name,
    last_name,
    manager_id,
    1 AS level,
    ARRAY[employee_id] AS path_array,
    last_name::TEXT AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive member: join children to their parent row
  SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.manager_id,
    h.level + 1,
    h.path_array || e.employee_id,
    h.path || '/' || e.last_name
  FROM employees e
  JOIN org_hierarchy h ON h.employee_id = e.manager_id
)
SELECT
  level,
  employee_id,
  repeat('  ', level - 1) || first_name || ' ' || last_name AS name,
  manager_id,
  path,
  -- CONNECT_BY_ISLEAF equivalent: leaf if no children in the hierarchy result
  NOT EXISTS (
    SELECT 1 FROM employees c WHERE c.manager_id = org_hierarchy.employee_id
  ) AS is_leaf
FROM org_hierarchy
ORDER BY path_array;  -- approximates ORDER SIBLINGS BY

Translating Oracle Pseudo-Columns

LEVEL

Straightforward: carry a level counter in the CTE, incrementing by 1 in the recursive member.

-- Oracle
SELECT LEVEL, employee_id FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- Postgres
WITH RECURSIVE h AS (
  SELECT employee_id, 1 AS level FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, h.level + 1
  FROM employees e JOIN h ON h.employee_id = e.manager_id
)
SELECT level, employee_id FROM h;

SYS_CONNECT_BY_PATH

Build a path string by concatenating the path from the parent with the current node's value. Using an array and then converting to text provides better flexibility:

-- Oracle
SELECT SYS_CONNECT_BY_PATH(last_name, '/') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- Postgres: build path as concatenated string
WITH RECURSIVE h AS (
  SELECT employee_id, last_name, '/' || last_name AS path
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.last_name, h.path || '/' || e.last_name
  FROM employees e JOIN h ON h.employee_id = e.manager_id
)
SELECT path FROM h;

CONNECT_BY_ISLEAF

Oracle computes this as part of the traversal. In Postgres you need a post-traversal join or subquery to determine whether a node has children:

-- Postgres: CONNECT_BY_ISLEAF equivalent
WITH RECURSIVE h AS (
  SELECT employee_id, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, h.level + 1
  FROM employees e JOIN h ON h.employee_id = e.manager_id
)
SELECT
  h.employee_id,
  h.level,
  CASE WHEN child.employee_id IS NULL THEN 1 ELSE 0 END AS is_leaf
FROM h
LEFT JOIN employees child ON child.manager_id = h.employee_id
GROUP BY h.employee_id, h.level, child.employee_id IS NULL;

CONNECT_BY_ROOT

Carry the root node's value through the recursion from the anchor:

-- Postgres: CONNECT_BY_ROOT equivalent
WITH RECURSIVE h AS (
  SELECT employee_id, manager_id, employee_id AS root_employee_id
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, h.root_employee_id
  FROM employees e JOIN h ON h.employee_id = e.manager_id
)
SELECT employee_id, root_employee_id FROM h;

Cycle Detection

Oracle detects cycles using NOCYCLE and exposes CONNECT_BY_ISCYCLE. Without NOCYCLE, a cyclic graph causes an infinite loop. Postgres 14+ added native cycle detection with the CYCLE clause:

-- Oracle: cycle-safe traversal
SELECT employee_id, CONNECT_BY_ISCYCLE AS is_cycle
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

-- Postgres 14+: native CYCLE clause
WITH RECURSIVE h AS (
  SELECT employee_id, manager_id FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id
  FROM employees e JOIN h ON h.employee_id = e.manager_id
)
CYCLE employee_id SET is_cycle TO TRUE DEFAULT FALSE
USING cycle_path
SELECT employee_id, is_cycle FROM h;

For Postgres 13 and earlier, cycle detection requires carrying an array of visited node IDs and checking membership before each recursive step:

-- Postgres 13 and earlier: manual cycle detection
WITH RECURSIVE h AS (
  SELECT employee_id, manager_id,
         ARRAY[employee_id] AS visited,
         FALSE AS is_cycle
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id,
         h.visited || e.employee_id,
         e.employee_id = ANY(h.visited)
  FROM employees e
  JOIN h ON h.employee_id = e.manager_id
  WHERE NOT h.is_cycle
    AND e.employee_id != ALL(h.visited)
)
SELECT employee_id, is_cycle FROM h;

ORDER SIBLINGS BY: The Hard Part

Oracle's ORDER SIBLINGS BY orders children within each parent while preserving depth-first traversal order. Postgres has no equivalent. The standard workaround is to carry an ordering array through the recursion and sort by it at the end:

-- Postgres: approximating ORDER SIBLINGS BY last_name
WITH RECURSIVE h AS (
  SELECT
    employee_id, last_name, manager_id,
    ARRAY[lpad(last_name, 50)] AS sort_path
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT
    e.employee_id, e.last_name, e.manager_id,
    h.sort_path || lpad(e.last_name, 50)
  FROM employees e JOIN h ON h.employee_id = e.manager_id
)
SELECT employee_id, last_name
FROM h
ORDER BY sort_path;

The lpad ensures alphabetic comparison works correctly across levels. The padding width must be consistent and large enough to hold the longest sibling name. This is a reasonable workaround but fragile if values contain special characters or vary widely in length. For production code, use integer sort keys (sequence numbers) rather than name strings.

Performance Comparison

Oracle's CONNECT BY is a native operator with decades of optimizer work behind it. Postgres recursive CTEs are a general-purpose mechanism not specifically optimized for tree traversal. For deep hierarchies (hundreds of levels) or very wide trees (millions of nodes), Postgres recursive CTEs can be significantly slower than Oracle CONNECT BY on equivalent hardware.

The practical mitigation strategies:

• Index the parent column. The recursive join JOIN h ON h.employee_id = e.manager_id needs an index on manager_id. Without it, each recursive step is a sequential scan.
• Use SEARCH DEPTH FIRST or BREADTH FIRST clauses (Postgres 14+) to explicitly control traversal order rather than relying on sort arrays.
• Consider ltree for deep static hierarchies. The ltree extension stores materialized path representations and supports fast subtree queries without recursion — effectively pre-computing what CONNECT BY computes at query time.

-- Postgres 14+: explicit depth-first search ordering
WITH RECURSIVE h AS (
  SELECT employee_id, manager_id FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id
  FROM employees e JOIN h ON h.employee_id = e.manager_id
)
SEARCH DEPTH FIRST BY employee_id SET traversal_order
SELECT employee_id FROM h ORDER BY traversal_order;