Recursive CTEs: SQL’s Hidden Graph Traversal Engine

Stay Ahead, Stay ONMINE

Recursive CTEs: SQL’s Hidden Graph Traversal Engine

When developers run into graph problems, such as working with hierarchies, finding routes between cities, or mapping social network connections, the first reaction is often to grab a specialised tool. We tend to think we need a graph database like Neo4j or a Python library like NetworkX to handle these challenges.For massive, billion-node graphs, those tools are necessary. But for a substantial percentage of operational data, e.g. supply chains with a few thousand nodes, organisational charts, or navigation paths, introducing a new database engine can be architectural overkill.Your current relational database can likely already handle most graph problems just fine. The key is a feature that has been in the SQL standard since 1999 but still isn’t widely used: Recursive Common Table Expressions (CTEs).This article shows how to do graph traversal, pathfinding, and cycle detection using only standard SQL.PrerequisitesIf you want to try these examples, you’ll need access to a modern relational database like Postgres, Oracle, or MySQL.I’ll use SQLite on my local computer for the examples. Keep in mind that the exact SQL syntax may vary slightly depending on your database.A Quick Refresher on Standard CTEsBefore diving into recursive common table expressions, let’s review what a regular, non-recursive CTE does.A Common Table Expression is a temporary result set you define within a single SELECT, INSERT, UPDATE, or DELETE statement. You can think of it as a named subquery or a temporary view that only exists while the query runs.The main reason to use CTEs is to make queries more straightforward to read. They help you break up complex logic into clear, step-by-step parts. Instead of writing messy queries full of subqueries, you can organise your logic in named sections at the top. For complex SQL, you can also reuse the same CTE more than once in a statement.Consider a simple sales table.CREATE TABLE sales (id SERIAL PRIMARY KEY,region TEXT,amount INT);INSERT INTO sales (region, amount) VALUES (‘North’, 100);INSERT INTO sales (region, amount) VALUES (‘North’, 150);INSERT INTO sales (region, amount) VALUES(‘South’, 200);INSERT INTO sales (region, amount) VALUES (‘South’, 50);INSERT INTO sales (region, amount) VALUES(‘East’, 300);We want to find out which region(s) sales are greater than or equal to the average sales of all regions. First, we use a CTE to calculate total sales for each region. Then, we filter these results by comparing each region’s total to the average, which we get from a subquery over the CTE.WITH region_totals AS (– Calculate total sales per regionSELECT region, SUM(amount) as total_salesFROM salesGROUP BY region)SELECT region, total_salesFROM region_totalsWHERE total_sales > (SELECT AVG(total_sales) FROM region_totals);– Output is …region total_sales—— ———–East 300Let’s check. Regional totals are:North = 250South = 250East = 300The average of these values is 266.67, and yes, only the East region has sales greater than this average.The WITH region_totals AS (…) block defines the CTE. The subsequent query treats region_totals like it’s a real table. Once the query finishes, the CTE vanishes.A Recursive CTE takes this concept one step further. Instead of just passing data down to the main query, it can reference itself to generate new rows based on previous rows.The Anatomy of a Recursive CTEA Recursive CTE works like a loop inside a query. Unlike a regular SELECT statement that runs once, a recursive CTE keeps running until there’s no more data. It builds the result set step by step.The syntax is standardised, but the logic requires a mental shift from “set-based” thinking to “iterative” thinking.Every recursive CTE has the same basic structure: an initial (or anchor) query to start the result set, a recursive query that joins back to itself to add more rows, and a stopping point when there are no more rows to add.For example,WITH RECURSIVE graph_cte AS ( — 1. Initial query to start the result set (The Anchor) SELECT * FROM table WHERE id = 1 UNION ALL — 2. Recursive query that joins back to itself to add more rows SELECT t.* FROM table t JOIN graph_cte g ON t.parent_id = g.id — 3. Stopping point: The recursion terminates automatically when this JOIN — finds no more matches and returns 0 rows.)SELECT * FROM graph_cte;When you run this, the database engine does a Breadth-First Search (BFS). It runs the anchor query, adds the results to a working table, then uses those results for the recursive step. This repeats until the recursive step returns no more rows.Example 1. The Organisational Hierarchy (Trees)The most common graph problem in business software is the tree structure. File systems, comment threads, and organisational charts all model hierarchical relationships, even though the meaning of those relationships differs in each case.Let’s define a simple employees table.CREATE TABLE employees (id SERIAL PRIMARY KEY,name TEXT NOT NULL,manager_id INT REFERENCES employees(id),role TEXT);INSERT INTO employees (id, name, manager_id, role) VALUES(1, ‘Alice’, NULL, ‘CEO’),(2, ‘Bob’, 1, ‘VP Engineering’),(3, ‘Charlie’, 1, ‘VP Sales’),(4, ‘Dave’, 2, ‘Backend Lead’),(5, ‘Eve’, 2, ‘Frontend Lead’),(6, ‘Frank’, 4, ‘Junior Dev’);select * from employees;id name manager_id role– ——- ———- ————–1 Alice CEO2 Bob 1 VP Engineering3 Charlie 1 VP Sales4 Dave 2 Backend Lead5 Eve 2 Frontend Lead6 Frank 4 Junior DevThe problemWe want to create a report that shows every employee, their management path (e.g., “Alice – > Bob – > Dave”), and their depth in the hierarchy.The SQL SolutionWITH RECURSIVE org_chart AS ( — Anchor: Start with the Boss (Depth 1) SELECT id, name, manager_id, role, 1 AS depth, name AS path FROM employees WHERE manager_id IS NULL UNION ALL — Recursive: Find employees managed by the previous layer SELECT e.id, e.name, e.manager_id, e.role, oc.depth + 1 AS depth, oc.path || ‘ – > ‘ || e.name AS path FROM employees e JOIN org_chart oc ON e.manager_id = oc.id — Depth guard to prevent infinite recursion (e.g., cycles) WHERE oc.depth Bob3 Charlie 1 VP Sales 2 Alice – > Charlie4 Dave 2 Backend Lead 3 Alice – > Bob – > Dave5 Eve 2 Frontend Lead 3 Alice – > Bob – > Eve6 Frank 4 Junior Dev 4 Alice – > Bob – > Dave – > FrankHow this worksThe process starts with the anchor query, which selects the top-level employee where manager_id is NULL. This gives us Alice and starts the result set at depth 1, with a path containing just her name.Next, the recursive query keeps joining the current results back to the employees table using manager_id = id. The first round finds Alice’s direct reports, Bob and Charlie, giving them a depth of 2 and extending their paths. Later rounds keep expanding from the new rows. Bob and Charlie lead to Dave and Eve, and Dave leads to Frank. Each level increases the depth and adds to the path.Eventually, the recursive step finds no new rows because there are no more employees whose manager_id matches the current set. At that point, the recursion stops. The final result is a flat view of the organisational hierarchy, built step by step by the database engine without any loops in your code.The end result is a flat view of the organisational hierarchy, built in SQL with a breadth-first recursive query. There’s no need for loops or extra control flow in your application.Example 2. Pathfinding in a Network (Graphs)Trees are simple because they only go in one direction, from the top down. Graphs are more complex because they can have cycles and multiple paths.Let’s look at a transportation network. Unlike an org chart, you can travel from Point A to Point B in several ways, and each connection (like a road or flight) has a weight, such as cost or distance.CREATE TABLE connections ( origin TEXT, destination TEXT, cost INT);INSERT INTO connections VALUES(‘New York’, ‘London’, 500),(‘New York’, ‘Paris’, 600),(‘London’, ‘Dubai’, 400),(‘Paris’, ‘Dubai’, 350),(‘Dubai’, ‘Tokyo’, 500),(‘Paris’, ‘Tokyo’, 800);select * from connections;origin destination cost——– ———– —-New York London 500New York Paris 600London Dubai 400Paris Dubai 350Dubai Tokyo 500Paris Tokyo 800The problemFind all possible routes from New York to Tokyo, and calculate the total cost for each route.The SQL solutionHere, we need to keep track of state as we recurse. We have to follow the running total cost and the order of cities visited.WITH RECURSIVE travel_planner AS ( — Anchor: Flights leaving New York SELECT origin, destination, cost as total_cost, origin || ‘ > ‘ || destination as route, 1 as hops FROM connections WHERE origin = ‘New York’UNION ALL — Recursive: Flights leaving from the previous destination SELECT c.origin, c.destination, tp.total_cost + c.cost, — Accumulate cost tp.route || ‘ > ‘ || c.destination, — Extend route tp.hops + 1 FROM connections c JOIN travel_planner tp ON c.origin = tp.destination)SELECT route, total_cost, hops FROM travel_planner WHERE destination = ‘Tokyo’ORDER BY total_cost ASC;How this worksThe query uses a recursive CTE to find all possible flight routes from New York to Tokyo, building each route one step at a time.Anchor phaseThe anchor query picks all direct flights leaving New York. Each row is a one-hop route, setting the total cost, route string, and hop count.Recursive expansionFor each route discovered so far, the recursive member looks for flights whose origin matches the current route’s destination. When a match is found, the query:adds the new flight’s cost to the running total,appends the destination to the route string,increments the hop count.This process repeats, extending routes one flight at a time, until no further connections can be made.Result selectionAfter all possible routes are found, the final SELECT filters for routes ending in Tokyo and sorts them by total cost, so the cheapest routes come first.The outputroute total_cost hops——————————— ———- —-New York > Paris > Tokyo 1400 2New York > London > Dubai > Tokyo 1400 3New York > Paris > Dubai > Tokyo 1450 3We’ve basically written a pathfinding algorithm using only SQL. The database engine explores the graph step by step – first New York’s neighbours, then their neighbours – until it finds the destination.Example 3. Cycle Detection (The Infinite Loop Trap)The previous flight example assumes the graph is a Directed Acyclic Graph (DAG), so you always move forward. But real-world graphs can have loops. For example, if London connects to Dubai and Dubai connects back to London, the query could get stuck in an infinite loop and run until it hits a memory limit or times out.To handle general graphs, we need to add cycle detection. This means checking if the node we’re about to visit has already been visited in the current path.Let us add a loop to our data.INSERT INTO connections VALUES (‘Dubai’, ‘New York’, 900); — The LoopIf we run the previous query now, it will crash (or run forever). Let’s patch it with cycle detection.A More Robust SQL SolutionWITH RECURSIVE travel_safe AS ( — Anchor: start from New York SELECT origin, destination, cost AS total_cost, origin || ‘- >’ || destination AS path_history, 0 AS is_cycle, 1 AS depth FROM connections WHERE origin = ‘New York’ UNION ALL — Recursive: expand paths SELECT c.origin, c.destination, ts.total_cost + c.cost AS total_cost, ts.path_history || ‘- >’ || c.destination AS path_history, CASE WHEN instr(ts.path_history, c.destination) > 0 THEN 1 ELSE 0 END AS is_cycle, ts.depth + 1 AS depth FROM connections c JOIN travel_safe ts ON c.origin = ts.destination WHERE ts.is_cycle = 0 — stop expanding cyclic paths AND ts.depth Paris- >Tokyo 1400 0New York- >London- >Dubai- >Tokyo 1400 0New York- >Paris- >Dubai- >Tokyo 1450 0How this worksTo make the traversal safe, the query keeps track of the route so far as a text string. Each time a new connection is added, the destination city is added to this path, creating a record of every city visited.Before extending a route, the recursive step checks if the next destination is already in the path string. If it is, that branch is marked as a cycle and won’t be expanded further. Other branches that aren’t cyclic keep exploring the graph.In addition to cycle detection, the query also sets a hard depth limit. This acts as a safety brake, ensuring recursion can’t run forever, even if the data contains unexpected loops or very deep chains.With these checks, the CTE can walk the graph by joining on origin = destination, building longer routes until it reaches Tokyo, finds a cycle, or hits the depth limit. This gives you a graph traversal in SQL with per-path “visited” tracking, using simple strings in SQLite instead of arrays.In short, the CTE explores the graph by joining connections on c.origin = ts.destination, building longer routes until it reaches Tokyo, finds a cycle, or hits the depth limit. This is a graph traversal with a “visited” check for each path, done in SQLite using strings. For other databases, you get away with using arrays instead.Example 4. Six Degrees of Separation (Shortest Path)Sometimes, we don’t care about the exact route, just the distance. In social networks, this is the classic “Six Degrees of Separation” idea, which says everyone is connected to everyone else by at most six people. This idea became famous in the game “Six Degrees of Kevin Bacon,” where the goal is to link the actor Kevin Bacon to another actor through the shortest path of connections.So, given two people, can we figure out what the shortest chain of friends is connecting them?Because Recursive CTEs operate in a Breadth-First manner (processing all friends, then all friends-of-friends), the first time the recursion finds the target user, it is guaranteed to be the shortest path (in an unweighted graph).First, we need some data.CREATE TABLE friendships ( user_name TEXT NOT NULL, friend_name TEXT NOT NULL);– Alice’s immediate friendsINSERT INTO friendships VALUES (‘Alice’, ‘Bob’);INSERT INTO friendships VALUES (‘Bob’, ‘Alice’);INSERT INTO friendships VALUES (‘Alice’, ‘Carol’);INSERT INTO friendships VALUES (‘Carol’, ‘Alice’);– Long chain (Alice → Bob → Dan → Erin → Frank → Grace → Kevin)INSERT INTO friendships VALUES (‘Bob’, ‘Dan’);INSERT INTO friendships VALUES (‘Dan’, ‘Bob’);INSERT INTO friendships VALUES (‘Dan’, ‘Erin’);INSERT INTO friendships VALUES (‘Erin’, ‘Dan’);INSERT INTO friendships VALUES (‘Erin’, ‘Frank’);INSERT INTO friendships VALUES (‘Frank’, ‘Erin’);INSERT INTO friendships VALUES (‘Frank’, ‘Grace’);INSERT INTO friendships VALUES (‘Grace’, ‘Frank’);INSERT INTO friendships VALUES (‘Grace’, ‘Kevin’);INSERT INTO friendships VALUES (‘Kevin’, ‘Grace’);– Shorter path (Alice → Carol → Kevin)INSERT INTO friendships VALUES (‘Carol’, ‘Kevin’);INSERT INTO friendships VALUES (‘Kevin’, ‘Carol’);– Extra noiseINSERT INTO friendships VALUES (‘Bob’, ‘Helen’);INSERT INTO friendships VALUES (‘Helen’, ‘Bob’);Now we find out how many degrees of separation there are between Kevin and everyone else.WITH RECURSIVE paths(person, degree, path) AS ( — Anchor: start at Kevin SELECT ‘Kevin’ AS person, 0 AS degree, ‘|Kevin|’ AS path UNION ALL — Recursive: expand one hop, avoid revisiting nodes already in the path SELECT f.friend_name AS person, p.degree + 1 AS degree, p.path || f.friend_name || ‘|’ AS path FROM friendships f JOIN paths p ON f.user_name = p.person WHERE p.degree < 10 AND instr(p.path, '|' || f.friend_name || '|') = 0),ranked AS ( SELECT person, degree, path, ROW_NUMBER() OVER ( PARTITION BY person ORDER BY degree ) AS rn FROM paths WHERE person ‘Kevin’)SELECT person, degree, replace(trim(path, ‘|’), ‘|’, ‘ – > ‘) AS shortest_pathFROM rankedWHERE rn = 1ORDER BY degree, person;How this worksThe query expands Kevin’s paths using a recursive CTE. The first query begins with Kevin, sets his degree to zero, and starts tracking the route. Each recursive step extends the search by one hop, joining the current results back to the relations table. As new people are found, the degree increases by 1, and their names are added to the path.To prevent loops, each path keeps a record of visited names. Before extending a path, the query checks if the next person is already in the path and skips them if so. A hard depth limit adds another safety brake, making sure recursion doesn’t go on forever. This process creates many possible paths, including different ways to reach the same person at different depths.After all paths are found, a second CTE ranks them by length for each person. A window function selects the shortest path for each person and retains it, discarding longer paths. The final SELECT formats these paths and orders the results by degree, so the closest connections come first.The resulting output is a “six degrees” view of the network: the minimum number of steps from Kevin to every other reachable person, computed entirely inside SQLite using recursive SQL and a small amount of post-processing.person degree shortest_path—— —— —————————————Carol 1 Kevin – > CarolGrace 1 Kevin – > GraceAlice 2 Kevin – > Carol – > AliceFrank 2 Kevin – > Grace – > FrankBob 3 Kevin – > Carol – > Alice – > BobErin 3 Kevin – > Grace – > Frank – > ErinDan 4 Kevin – > Grace – > Frank – > Erin – > DanHelen 4 Kevin – > Carol – > Alice – > Bob – > HelenPerformance Optimisation and LimitsRecursive CTEs are powerful, but they aren’t a replacement for a dedicated graph engine. They work well within certain limits, but performance drops quickly if you go beyond those limits.One key thing to watch is indexing. During recursion, the database keeps joining the working set back to the base table. If the join columns aren’t indexed, each step turns into a full table scan. What should be a quick operation can become much slower, so you should always think about indexing columns used in the recursive join.Another limit is the size of the working table. Recursive CTEs keep intermediate results as they go. In very wide graphs, such as social networks where a single node may have thousands of neighbours, these sets can grow very large, and if they spill to disk, performance can suffer. Because of this, recursive CTEs shouldn’t be used for wide, highly connected graphs.Finally, always use recursion carefully. In the real-world data is often messy, and cycles can appear without you even realising it. Without safeguards, a recursive query can loop forever or use up all system resources. Adding a simple depth limit to the recursive WHERE clause is a reliable safety brake, ensuring the query stops even in the presence of unexpected loops or bad data.SummarySQL is often seen as just a language for simple reports and CRUD operations. But it’s actually a declarative logic programming language. This article shows how standard SQL, using recursive Common Table Expressions (CTEs), can solve many real-world graph problems without a dedicated graph database.It shows how relational databases can do hierarchy traversal, pathfinding, cycle detection, and shortest-path calculations using just SQL recursion. With real-world examples such as org charts, route planning, cycle-safe traversal, and six degrees of separation, the article explains both how recursive CTEs work and where their limits lie.The main point is that while recursive CTEs aren’t a replacement for large graph engines, they are a powerful and underused tool for working with small to medium graphs inside your existing relational database—as long as you know their performance limits and use safety checks.You don’t always need to set up a Neo4j instance or write a Python script to traverse a tree or find a path. Sometimes, all you need is SQL and a new way of thinking.

For massive, billion-node graphs, those tools are necessary. But for a substantial percentage of operational data, e.g. supply chains with a few thousand nodes, organisational charts, or navigation paths, introducing a new database engine can be architectural overkill.

Your current relational database can likely already handle most graph problems just fine. The key is a feature that has been in the SQL standard since 1999 but still isn’t widely used: Recursive Common Table Expressions (CTEs).

This article shows how to do graph traversal, pathfinding, and cycle detection using only standard SQL.

Prerequisites

If you want to try these examples, you’ll need access to a modern relational database like Postgres, Oracle, or MySQL.

I’ll use SQLite on my local computer for the examples. Keep in mind that the exact SQL syntax may vary slightly depending on your database.

A Quick Refresher on Standard CTEs

Before diving into recursive common table expressions, let’s review what a regular, non-recursive CTE does.

A Common Table Expression is a temporary result set you define within a single SELECT, INSERT, UPDATE, or DELETE statement. You can think of it as a named subquery or a temporary view that only exists while the query runs.

The main reason to use CTEs is to make queries more straightforward to read. They help you break up complex logic into clear, step-by-step parts. Instead of writing messy queries full of subqueries, you can organise your logic in named sections at the top. For complex SQL, you can also reuse the same CTE more than once in a statement.

Consider a simple sales table.

CREATE TABLE sales (id SERIAL PRIMARY KEY,region TEXT,amount INT);INSERT INTO sales (region, amount) VALUES ('North', 100);INSERT INTO sales (region, amount) VALUES ('North', 150);INSERT INTO sales (region, amount) VALUES('South', 200);INSERT INTO sales (region, amount) VALUES ('South', 50);INSERT INTO sales (region, amount) VALUES('East', 300);

We want to find out which region(s) sales are greater than or equal to the average sales of all regions. First, we use a CTE to calculate total sales for each region. Then, we filter these results by comparing each region’s total to the average, which we get from a subquery over the CTE.

WITH region_totals AS (-- Calculate total sales per regionSELECT region, SUM(amount) as total_salesFROM salesGROUP BY region)SELECT region, total_salesFROM region_totalsWHERE total_sales > (SELECT AVG(total_sales) FROM region_totals);-- Output is ...region  total_sales------  -----------East    300

Let’s check. Regional totals are:

North = 250
South = 250
East = 300

The average of these values is 266.67, and yes, only the East region has sales greater than this average.

The WITH region_totals AS (…) block defines the CTE. The subsequent query treats region_totals like it’s a real table. Once the query finishes, the CTE vanishes.

A Recursive CTE takes this concept one step further. Instead of just passing data down to the main query, it can reference itself to generate new rows based on previous rows.

The Anatomy of a Recursive CTE

A Recursive CTE works like a loop inside a query. Unlike a regular SELECT statement that runs once, a recursive CTE keeps running until there’s no more data. It builds the result set step by step.

The syntax is standardised, but the logic requires a mental shift from “set-based” thinking to “iterative” thinking.

Every recursive CTE has the same basic structure: an initial (or anchor) query to start the result set, a recursive query that joins back to itself to add more rows, and a stopping point when there are no more rows to add.

For example,

WITH RECURSIVE graph_cte AS (    -- 1. Initial query to start the result set (The Anchor)    SELECT *    FROM table    WHERE id = 1    UNION ALL    -- 2. Recursive query that joins back to itself to add more rows    SELECT t.*    FROM table t    JOIN graph_cte g ON t.parent_id = g.id        -- 3. Stopping point: The recursion terminates automatically when this JOIN    --    finds no more matches and returns 0 rows.)SELECT * FROM graph_cte;

When you run this, the database engine does a Breadth-First Search (BFS). It runs the anchor query, adds the results to a working table, then uses those results for the recursive step. This repeats until the recursive step returns no more rows.

Example 1. The Organisational Hierarchy (Trees)

The most common graph problem in business software is the tree structure. File systems, comment threads, and organisational charts all model hierarchical relationships, even though the meaning of those relationships differs in each case.

Let’s define a simple employees table.

CREATE TABLE employees (id SERIAL PRIMARY KEY,name TEXT NOT NULL,manager_id INT REFERENCES employees(id),role TEXT);INSERT INTO employees (id, name, manager_id, role) VALUES(1, 'Alice', NULL, 'CEO'),(2, 'Bob', 1, 'VP Engineering'),(3, 'Charlie', 1, 'VP Sales'),(4, 'Dave', 2, 'Backend Lead'),(5, 'Eve', 2, 'Frontend Lead'),(6, 'Frank', 4, 'Junior Dev');select * from employees;id name    manager_id role-- ------- ---------- --------------1  Alice              CEO2  Bob     1          VP Engineering3  Charlie 1          VP Sales4  Dave    2          Backend Lead5  Eve     2          Frontend Lead6  Frank   4          Junior Dev

The problem

We want to create a report that shows every employee, their management path (e.g., “Alice -> Bob -> Dave”), and their depth in the hierarchy.

The SQL Solution

WITH RECURSIVE org_chart AS (    -- Anchor: Start with the Boss (Depth 1)    SELECT        id,        name,        manager_id,        role,        1 AS depth,        name AS path    FROM employees    WHERE manager_id IS NULL    UNION ALL    -- Recursive: Find employees managed by the previous layer    SELECT        e.id,        e.name,        e.manager_id,        e.role,        oc.depth + 1 AS depth,        oc.path || ' -> ' || e.name AS path    FROM employees e    JOIN org_chart oc      ON e.manager_id = oc.id    -- Depth guard to prevent infinite recursion (e.g., cycles)    WHERE oc.depth < 10)SELECT    id,    name,    manager_id,    role,    depth,    pathFROM org_chartORDER BY depth, path;

And here is the output.

id  name     manager_id  role            depth  path--  -------  ----------  --------------  -----  -----------------------------1   Alice                CEO             1      Alice2   Bob      1           VP Engineering  2      Alice -> Bob3   Charlie  1           VP Sales        2      Alice -> Charlie4   Dave     2           Backend Lead    3      Alice -> Bob -> Dave5   Eve      2           Frontend Lead   3      Alice -> Bob -> Eve6   Frank    4           Junior Dev      4      Alice -> Bob -> Dave -> Frank

How this works

The process starts with the anchor query, which selects the top-level employee where manager_id is NULL. This gives us Alice and starts the result set at depth 1, with a path containing just her name.

Next, the recursive query keeps joining the current results back to the employees table using manager_id = id. The first round finds Alice’s direct reports, Bob and Charlie, giving them a depth of 2 and extending their paths. Later rounds keep expanding from the new rows. Bob and Charlie lead to Dave and Eve, and Dave leads to Frank. Each level increases the depth and adds to the path.

Eventually, the recursive step finds no new rows because there are no more employees whose manager_id matches the current set. At that point, the recursion stops. The final result is a flat view of the organisational hierarchy, built step by step by the database engine without any loops in your code.

The end result is a flat view of the organisational hierarchy, built in SQL with a breadth-first recursive query. There’s no need for loops or extra control flow in your application.

Example 2. Pathfinding in a Network (Graphs)

Trees are simple because they only go in one direction, from the top down. Graphs are more complex because they can have cycles and multiple paths.

Let’s look at a transportation network. Unlike an org chart, you can travel from Point A to Point B in several ways, and each connection (like a road or flight) has a weight, such as cost or distance.

CREATE TABLE connections (    origin TEXT,    destination TEXT,    cost INT);INSERT INTO connections VALUES('New York', 'London', 500),('New York', 'Paris', 600),('London', 'Dubai', 400),('Paris', 'Dubai', 350),('Dubai', 'Tokyo', 500),('Paris', 'Tokyo', 800);select * from connections;origin    destination  cost--------  -----------  ----New York  London       500New York  Paris        600London    Dubai        400Paris     Dubai        350Dubai     Tokyo        500Paris     Tokyo        800

The problem

Find all possible routes from New York to Tokyo, and calculate the total cost for each route.

The SQL solution

Here, we need to keep track of state as we recurse. We have to follow the running total cost and the order of cities visited.

WITH RECURSIVE travel_planner AS (    -- Anchor: Flights leaving New York    SELECT         origin,        destination,        cost as total_cost,        origin || ' > ' || destination as route,        1 as hops    FROM connections    WHERE origin = 'New York'UNION ALL    -- Recursive: Flights leaving from the previous destination    SELECT         c.origin,        c.destination,        tp.total_cost + c.cost, -- Accumulate cost        tp.route || ' > ' || c.destination, -- Extend route        tp.hops + 1    FROM connections c    JOIN travel_planner tp ON c.origin = tp.destination)SELECT route, total_cost, hops FROM travel_planner WHERE destination = 'Tokyo'ORDER BY total_cost ASC;

How this works

The query uses a recursive CTE to find all possible flight routes from New York to Tokyo, building each route one step at a time.

Anchor phase

The anchor query picks all direct flights leaving New York. Each row is a one-hop route, setting the total cost, route string, and hop count.

Recursive expansion

For each route discovered so far, the recursive member looks for flights whose origin matches the current route’s destination. When a match is found, the query:

adds the new flight’s cost to the running total,
appends the destination to the route string,
increments the hop count.

This process repeats, extending routes one flight at a time, until no further connections can be made.

Result selection

After all possible routes are found, the final SELECT filters for routes ending in Tokyo and sorts them by total cost, so the cheapest routes come first.

The output

route                              total_cost  hops---------------------------------  ----------  ----New York > Paris > Tokyo           1400        2New York > London > Dubai > Tokyo  1400        3New York > Paris > Dubai > Tokyo   1450        3

We’ve basically written a pathfinding algorithm using only SQL. The database engine explores the graph step by step – first New York’s neighbours, then their neighbours – until it finds the destination.

Example 3. Cycle Detection (The Infinite Loop Trap)

The previous flight example assumes the graph is a Directed Acyclic Graph (DAG), so you always move forward. But real-world graphs can have loops. For example, if London connects to Dubai and Dubai connects back to London, the query could get stuck in an infinite loop and run until it hits a memory limit or times out.

To handle general graphs, we need to add cycle detection. This means checking if the node we’re about to visit has already been visited in the current path.

Let us add a loop to our data.

INSERT INTO connections VALUES ('Dubai', 'New York', 900); -- The Loop

If we run the previous query now, it will crash (or run forever). Let’s patch it with cycle detection.

A More Robust SQL Solution

WITH RECURSIVE travel_safe AS (    -- Anchor: start from New York    SELECT        origin,        destination,        cost AS total_cost,        origin || '->' || destination AS path_history,        0 AS is_cycle,        1 AS depth    FROM connections    WHERE origin = 'New York'    UNION ALL    -- Recursive: expand paths    SELECT        c.origin,        c.destination,        ts.total_cost + c.cost AS total_cost,        ts.path_history || '->' || c.destination AS path_history,        CASE            WHEN instr(ts.path_history, c.destination) > 0 THEN 1            ELSE 0        END AS is_cycle,        ts.depth + 1 AS depth    FROM connections c    JOIN travel_safe ts      ON c.origin = ts.destination    WHERE ts.is_cycle = 0          -- stop expanding cyclic paths      AND ts.depth < 10            -- safety brake)SELECT    path_history,    total_cost,    is_cycleFROM travel_safeWHERE destination = 'Tokyo';

And our output

path_history                    total_cost  is_cycle------------------------------  ----------  --------New York->Paris->Tokyo          1400        0New York->London->Dubai->Tokyo  1400        0New York->Paris->Dubai->Tokyo   1450        0

How this works

To make the traversal safe, the query keeps track of the route so far as a text string. Each time a new connection is added, the destination city is added to this path, creating a record of every city visited.

Before extending a route, the recursive step checks if the next destination is already in the path string. If it is, that branch is marked as a cycle and won’t be expanded further. Other branches that aren’t cyclic keep exploring the graph.

In addition to cycle detection, the query also sets a hard depth limit. This acts as a safety brake, ensuring recursion can’t run forever, even if the data contains unexpected loops or very deep chains.

With these checks, the CTE can walk the graph by joining on origin = destination, building longer routes until it reaches Tokyo, finds a cycle, or hits the depth limit. This gives you a graph traversal in SQL with per-path “visited” tracking, using simple strings in SQLite instead of arrays.

In short, the CTE explores the graph by joining connections on c.origin = ts.destination, building longer routes until it reaches Tokyo, finds a cycle, or hits the depth limit. This is a graph traversal with a “visited” check for each path, done in SQLite using strings. For other databases, you get away with using arrays instead.

Example 4. Six Degrees of Separation (Shortest Path)

Sometimes, we don’t care about the exact route, just the distance. In social networks, this is the classic “Six Degrees of Separation” idea, which says everyone is connected to everyone else by at most six people. This idea became famous in the game “Six Degrees of Kevin Bacon,” where the goal is to link the actor Kevin Bacon to another actor through the shortest path of connections.

So, given two people, can we figure out what the shortest chain of friends is connecting them?

Because Recursive CTEs operate in a Breadth-First manner (processing all friends, then all friends-of-friends), the first time the recursion finds the target user, it is guaranteed to be the shortest path (in an unweighted graph).

First, we need some data.

CREATE TABLE friendships (    user_name   TEXT NOT NULL,    friend_name TEXT NOT NULL);-- Alice's immediate friendsINSERT INTO friendships VALUES ('Alice', 'Bob');INSERT INTO friendships VALUES ('Bob', 'Alice');INSERT INTO friendships VALUES ('Alice', 'Carol');INSERT INTO friendships VALUES ('Carol', 'Alice');-- Long chain (Alice → Bob → Dan → Erin → Frank → Grace → Kevin)INSERT INTO friendships VALUES ('Bob', 'Dan');INSERT INTO friendships VALUES ('Dan', 'Bob');INSERT INTO friendships VALUES ('Dan', 'Erin');INSERT INTO friendships VALUES ('Erin', 'Dan');INSERT INTO friendships VALUES ('Erin', 'Frank');INSERT INTO friendships VALUES ('Frank', 'Erin');INSERT INTO friendships VALUES ('Frank', 'Grace');INSERT INTO friendships VALUES ('Grace', 'Frank');INSERT INTO friendships VALUES ('Grace', 'Kevin');INSERT INTO friendships VALUES ('Kevin', 'Grace');-- Shorter path (Alice → Carol → Kevin)INSERT INTO friendships VALUES ('Carol', 'Kevin');INSERT INTO friendships VALUES ('Kevin', 'Carol');-- Extra noiseINSERT INTO friendships VALUES ('Bob', 'Helen');INSERT INTO friendships VALUES ('Helen', 'Bob');

Now we find out how many degrees of separation there are between Kevin and everyone else.

WITH RECURSIVE paths(person, degree, path) AS (    -- Anchor: start at Kevin    SELECT        'Kevin' AS person,        0       AS degree,        '|Kevin|' AS path    UNION ALL    -- Recursive: expand one hop, avoid revisiting nodes already in the path    SELECT        f.friend_name AS person,        p.degree + 1  AS degree,        p.path || f.friend_name || '|' AS path    FROM friendships f    JOIN paths p      ON f.user_name = p.person    WHERE p.degree < 10      AND instr(p.path, '|' || f.friend_name || '|') = 0),ranked AS (    SELECT        person,        degree,        path,        ROW_NUMBER() OVER (            PARTITION BY person            ORDER BY degree        ) AS rn    FROM paths    WHERE person  'Kevin')SELECT    person,    degree,    replace(trim(path, '|'), '|', ' -> ') AS shortest_pathFROM rankedWHERE rn = 1ORDER BY degree, person;

How this works

The query expands Kevin’s paths using a recursive CTE. The first query begins with Kevin, sets his degree to zero, and starts tracking the route. Each recursive step extends the search by one hop, joining the current results back to the relations table. As new people are found, the degree increases by 1, and their names are added to the path.

To prevent loops, each path keeps a record of visited names. Before extending a path, the query checks if the next person is already in the path and skips them if so. A hard depth limit adds another safety brake, making sure recursion doesn’t go on forever. This process creates many possible paths, including different ways to reach the same person at different depths.

After all paths are found, a second CTE ranks them by length for each person. A window function selects the shortest path for each person and retains it, discarding longer paths. The final SELECT formats these paths and orders the results by degree, so the closest connections come first.

The resulting output is a “six degrees” view of the network: the minimum number of steps from Kevin to every other reachable person, computed entirely inside SQLite using recursive SQL and a small amount of post-processing.

person  degree  shortest_path------  ------  ---------------------------------------Carol   1       Kevin -> CarolGrace   1       Kevin -> GraceAlice   2       Kevin -> Carol -> AliceFrank   2       Kevin -> Grace -> FrankBob     3       Kevin -> Carol -> Alice -> BobErin    3       Kevin -> Grace -> Frank -> ErinDan     4       Kevin -> Grace -> Frank -> Erin -> DanHelen   4       Kevin -> Carol -> Alice -> Bob -> Helen

Performance Optimisation and Limits

Recursive CTEs are powerful, but they aren’t a replacement for a dedicated graph engine. They work well within certain limits, but performance drops quickly if you go beyond those limits.

One key thing to watch is indexing. During recursion, the database keeps joining the working set back to the base table. If the join columns aren’t indexed, each step turns into a full table scan. What should be a quick operation can become much slower, so you should always think about indexing columns used in the recursive join.

Another limit is the size of the working table. Recursive CTEs keep intermediate results as they go. In very wide graphs, such as social networks where a single node may have thousands of neighbours, these sets can grow very large, and if they spill to disk, performance can suffer. Because of this, recursive CTEs shouldn’t be used for wide, highly connected graphs.

Finally, always use recursion carefully. In the real-world data is often messy, and cycles can appear without you even realising it. Without safeguards, a recursive query can loop forever or use up all system resources. Adding a simple depth limit to the recursive WHERE clause is a reliable safety brake, ensuring the query stops even in the presence of unexpected loops or bad data.

Summary

SQL is often seen as just a language for simple reports and CRUD operations. But it’s actually a declarative logic programming language. This article shows how standard SQL, using recursive Common Table Expressions (CTEs), can solve many real-world graph problems without a dedicated graph database.

It shows how relational databases can do hierarchy traversal, pathfinding, cycle detection, and shortest-path calculations using just SQL recursion. With real-world examples such as org charts, route planning, cycle-safe traversal, and six degrees of separation, the article explains both how recursive CTEs work and where their limits lie.

The main point is that while recursive CTEs aren’t a replacement for large graph engines, they are a powerful and underused tool for working with small to medium graphs inside your existing relational database—as long as you know their performance limits and use safety checks.

You don’t always need to set up a Neo4j instance or write a Python script to traverse a tree or find a path. Sometimes, all you need is SQL and a new way of thinking.

Stay Ahead

Explore More Insights

Stay ahead with more perspectives on cutting-edge power, infrastructure, energy, bitcoin and AI solutions. Explore these articles to uncover strategies and insights shaping the future of industries.

Cisco bulks up its AI infrastructure portfolio with Supermicro’s liquid-cooled servers

“This expansion enables customers to easily manage complex, high-density AI clusters alongside non-AI workloads. Customers will also now be able to deploy rack-to-fabric liquid cooling, featuring Cisco liquid-cooled AI networking systems alongside Supermicro’s liquid-cooled servers. This unlocks trillion-parameter training and high-throughput inference use cases with platforms including Nvidia Vera Rubin NVL72 and Nvidia

Taking your temperature from the inside

Oral and forehead thermometers may not accurately capture a person’s core body temperature, and the few ingestible temperature sensors on the market are so big they are hard to swallow and risk obstructing the GI tract. But MIT engineers created one that can send continuous temperature updates at a size

Cisco taps Teleport for infrastructure identity management tech

Cisco is continuing to embed identity management capabilities deeper into its product portfolio by teaming with Teleport, a security vendor headquartered in Oakland, Calif., that’s focused on identity-based infrastructure access management. Cisco is investing in and partnering with Teleport as part of its efforts to bring infrastructure identity everywhere, Matt Caulfield,

Google adds AI-powered assessments to Migration Center to speed up cloud migration planning

“This tool is fundamentally bounded by its inputs such as infrastructure inventory, price of compute and storage, but it cannot see application dependencies, compliance obligations, or real-world performance behavior, each of which requires separate, deliberate discovery,” said Manoj Chandra Jha, principal analyst at Nord-IQ Research. “Thus, enterprises should treat AI-generated

DOE and SBA Launch SBIC-E Initiative to Unleash Private Capital for American Innovation and Small Businesses

WASHINGTON—The U.S. Department of Energy (DOE) and the U.S. Small Business Administration (SBA) today signed a Memorandum of Agreement establishing the Small Business Investment Company-Energy (SBIC-E) Initiative, a new strategic partnership advancing President Trump’s commitment to supporting America’s small businesses, strengthening domestic manufacturing and supply chains, and ensuring the United States leads in the technologies critical to our national and economic security. The new SBIC-E Initiative brings together DOE’s scientific and technical expertise with SBA’s proven Small Business Investment Company (SBIC) Program, which currently has $58 billion in combined portfolio value. Since 1958, the SBIC Program has invested $147 billion in American small businesses, and since 1995, SBIC-backed businesses have created or supported 10.6 million jobs. “America’s small businesses drive American innovation and affordable, reliable energy access,” said U.S. Secretary of Energy Chris Wright. “By partnering with the Small Business Administration, the Energy Department is committing to invest its resources in American small businesses that will create jobs, strengthen our domestic manufacturing base, and unleash American energy production.” Through DOE’s Office of Technology Commercialization (OTC), the Department will identify strategic technology priorities, provide technical and commercialization expertise, and help engage the investment community. SBA, through its Office of Investment and Innovation, will administer the initiative and encourage the formation and growth of investment funds focused on those priorities. SBIC-E adds another tool to that effort by connecting innovators with private capital to help promising technologies grow, scale, and build here at home. “President Trump is establishing American energy dominance, ending the Green New Scam, and putting our nations’ producers and innovators back in control at the dawn of a new era of energy reliability and abundance,” said SBA Administrator Kelly Loeffler. “Through this partnership, the SBA and Department of Energy are strengthening access to capital in the private sector to

Energy Department Announces $500 Million Award to Revitalize American Steelmaking

WASHINGTON—The U.S. Department of Energy (DOE) today announced a $500 million award to support a $1 billion investment at Cleveland-Cliffs’ Middletown Works facility in Middletown, Ohio. Vice President JD Vance and U.S. Energy Secretary Chris Wright visited Middletown Works today to highlight the Trump Administration’s commitment to American steelworkers and the resurgence of American manufacturing. The investment will modernize American steelmaking, protect 2,300 American jobs, and strengthen the domestic steel supply chain. The project advances President Trump’s commitment to put American workers first, bring investment back to American communities, and strengthen the industries critical to America’s economic and national security. Cleveland-Cliffs determined that the business case for the original project scope no longer made sense given customers’ unwillingness to pay a “green premium” for steel. Working with DOE, Cleveland-Cliffs identified a viable alternative that will upgrade and improve the efficiency of the existing coal-fired blast furnace while also capturing and commercializing co-product blast furnace gas (BFG). “President Trump is rebuilding America’s industrial base,” said Secretary Wright. “This investment puts American workers and American manufacturing first. It will modernize one of our nation’s critical steelmaking facilities, protect thousands of jobs, and strengthen our domestic steel production—keeping Ohio at the heart of American manufacturing and strengthening our national security.” The investment will modernize critical steelmaking operations at Middletown Works by rebuilding and upgrading the plant’s main coal-fired ironmaking furnace, deploying AI to optimize furnace operations and improve energy efficiency, and building an on-site facility to convert steel mill process gases into electricity. Follow-on investments will turn industrial byproducts into materials for concrete used in regional infrastructure. “This landmark investment at Middletown Works will secure a reliable domestic supply of high-purity steel while protecting thousands of quality jobs in Ohio,” said Assistant Secretary of Energy Audrey Robertson. “DOE is proud to partner with Cleveland-Cliffs to reduce America’s dependence on foreign products

Energy Secretary Keeps Critical Generation Available in Mid-Atlantic

WASHINGTON—U.S. Secretary of Energy Chris Wright today issued an emergency order to address critical grid reliability issues facing the Mid-Atlantic region of the United States. The emergency order directs PJM Interconnection L.L.C. (PJM), in coordination with Constellation Energy Corporation, to ensure Units 3 and 4 of the Eddystone Generating Station in Pennsylvania remain available to operate and to employ economic dispatch to minimize costs for the American people. The units were originally slated to shut down on May 31, 2025. “The energy sources that perform when you need them most are the most valuable,” Secretary Wright said. “During recent Mid-Atlantic heat waves, coal, natural gas, and nuclear kept the lights and air conditioners on. President Trump and the Energy Department are committed to keeping critical generation available when demand is highest, reducing the risk of blackouts and ensuring Americans have affordable, reliable, and secure power—regardless of whether the wind is blowing or the sun is shining.” As outlined in DOE’s Resource Adequacy Report, power outages could increase by 100 times in 2030 if the U.S. continues to take reliable power offline. This order is in effect beginning on August 23, 2026, through November 20, 2026. ###

Energy Department Announces $500 Million to Secure America’s Critical Mineral and Battery Supply Chains

WASHINGTON—The U.S. Department of Energy’s (DOE) Office of Critical Minerals and Energy Innovation (CMEI) today announced $500 million for seven selected projects to expand critical mineral and material processing, battery manufacturing, and recycling capacity in the United States. In accordance with President Trump’s Executive Order, Unleashing American Energy, the selected projects advance the President’s agenda to strengthen America’s domestic critical minerals and materials supply chains, reduce reliance on foreign sources, bolster national security, and advance American energy dominance. “For too long, America has depended on foreign actors for critical materials essential to modern life that underpin our economy, energy security, and national security,” said U.S. Secretary of Energy Chris Wright. “President Trump is reversing that dependence by securing our critical supply chains, unleashing American industry, and bringing critical materials production and processing back to the United States.” “DOE is taking decisive action to secure the critical supply chains necessary to power our nation,” said Assistant Secretary of Energy Audrey Robertson. “These projects underscore DOE’s commitment to driving innovation, reducing reliance on foreign sources, and promoting American energy dominance.” This is the third round of funding from DOE’s Battery Materials Processing and Battery Manufacturing and Recycling programs, which support battery materials processing, recycling, and manufacturing projects. These include demonstration projects, construction of commercial-scale facilities, and retrofitting or retooling existing facilities. Critical minerals and materials are essential to American industry, energy production, and national security. Expanding domestic capacity will help ensure the resources America needs are processed, manufactured, and recycled in the United States. Information on the selected projects is available here and here.

bp lets Shah Deniz compression automation contract

bp has let a contract to Emerson to deliver automation technologies for the Shah Deniz Compression project offshore Azerbaijan. Emerson will provide integrated control and safety systems aimed at enhancing production, safety, and reliability on the new offshore compression platform. The contract includes systems to provide process control, safety shutdown, fire and gas detection, and power management. Together, these systems deliver real-time visibility and remote control of critical operations, Emerson said. The $2.9 billion Shah Deniz Compression project, which includes an electrically powered, normally unattended offshore production platform, is a next stage development of the Caspian Sea Shah Deniz field. Designed to access low-pressure gas reserves and maximize overall recovery, the platform will be equipped with four 11 Mw compressors and serve as the central compression hub for gas from the Shah Deniz Alpha and Bravo platforms. The platform will operate remotely from bp’s onshore Sangachal terminal 55 km south of Baku. The project is expected to enable about 50 billion cu m of additional gas and about 25 million bbl of condensate production and export. Construction is scheduled to be completed in 2029, with first gas compression expected from the Shah Deniz Alpha platform in 2029 and from the Shah Deniz Bravo platform in 2030. The agreement follows a previous automation contract bp signed with Emerson for the Azeri Central East and Shah Deniz Stage 2 developments. bp is operator at Shah Deniz (29.99%) with partners Lukoil (19.99%), TPAO (19%), Cenub Qaz Dehlizi (16.02%), NICO (10%), and MVM (5%).

Federal court voids Texas GulfLink license over agency’s ‘serious procedural errors’

The ruling voids the license, halting all construction or progress. Sentinel Midstream declined comment on the ruling and would not answer questions about the status of construction. GulfLink, sited about 30 miles offshore Freeport, Tex., is designed to export up to 1 million b/d via Very Large Crude Carriers (VLCCs) to the government of Japan and Freeport Commodities. The project involves a 44-mile, 42-in. OD pipeline and was scheduled to begin operations around 2028. The estimated $2.1 billion investment was funded as part of a broader trade agreement between the US and Japan. The legal battle stems from a specific rule in the Deepwater Port Act of 1974 that dictates that the federal government can only permit one crude oil deepwater port, including any supporting infrastructure, within a single designated “application area.” Because the competing SPOT project’s pipeline route physically overlaps and intersects GulfLink’s lines, the plaintiff—Citizens for Clean Air & Clean Water in Brazoria County (Better Brazoria), represented by Earthjustice—successfully argued that MARAD violated the “one port” rule when issuing GulfLink’s license in February. The three-judge panel found that MARAD “improperly drew” the map designing the project’s official boundaries to exclude the pipelines and approved two overlapping projects in the same zone instead of only licensing one. The court wrote that the scope of the error made vacatur, not the less serious remand without vacatur, the appropriate remedy. Vacatur deems the license invalid and is used when the court finds “serious procedural errors” that cannot be easily explained or fixed with minor changes. Remand without vacatur sends the decision back to the agency for corrections but leaves the current license in place in the meantime. SPOT project status The $2.5-3-billion SPOT project, developed by Enterprise Products Partners in partnership with Enbridge Inc., also lies about 30 miles from Freeport. Designed to handle VLCCs,

IBM unveils dual-architecture processor to run Arm-native apps on Z mainframes

“These caches have enormously low latency, and that is one of the key reasons and key engineering choices to support the performance and scalability of enterprise workloads, very data-intensive workloads like databases and transactions,” Jacobi said. “In addition, we have an on-chip data processing unit for IO acceleration and dedicated AI accelerators as well as accelerators for data compression, cryptography and data sorting.” One of the biggest takeaways from this processor announcement is that the enormous catalog of software already built for Arm becomes accessible on a mainframe without anyone having to port it first, notes Matt Kimball, senior datacenter analyst at Moor Insights & Strategy, in a research note about the news. Still, “this is a 2027 conversation, and with no date, supported software list, or Arm licensing treatment, the work now is inventory and scenario planning rather than financial modeling,” Kimball wrote.

PJM’s New Data Center Power Equation

PJM Interconnection has now filed one of the most consequential proposed changes yet in the relationship between data centers and the electric grid. Rather than simply treating a new hyperscale or AI facility like any other customer whose demand will be backed through regional capacity procurement, PJM is proposing a framework under which the largest new loads would need to be supported by new capacity, have their needs covered through the Reliability Backstop Procurement, or face potential curtailment when the regional power system is short of supply. The approach has been developing since PJM launched its Critical Issue Fast Path process for large loads in 2025, but it became substantially more concrete in late July and August 2026. PJM filed its proposed Reliability Backstop Procurement with FERC on July 31 and began accepting applications that day for its FERC-approved Expedited Interconnection Track. On Aug. 13, PJM filed its proposed Interim Resource Adequacy Service, or IRAS, along with the Large Load Registry that would support it. The immediate numbers explain the urgency. PJM’s July 2026 capacity auction for the 2028/2029 delivery year procured 138,318 MW of unforced capacity through the centralized auction. Even after including Fixed Resource Requirement resources, however, PJM came up 6,831 MW short of its reliability requirement. The auction cleared at the FERC-approved $325/MW-day price cap. It was the second consecutive auction in which the PJM region failed to procure its full reliability requirement, something that had not happened before these two auctions. That gap is occurring while demand continues to accelerate. PJM’s 2026 long-term forecast projects summer peak demand growing at an average 3.6% annually over the next decade, compared with just 0.3% in the comparable forecast issued in 2021. Summer peak demand is projected to rise by nearly 66 GW over 10 years. Data centers are

Zayo, NVIDIA Build the Long-Haul Backbone for Distributed AI

The data center industry’s increasingly power-first approach to site selection has created a follow-on question: Once the megawatts are found, is there enough network infrastructure to make the site useful at AI scale? Zayo and NVIDIA are putting real infrastructure behind that question. Zayo said it is working with NVIDIA to expand network capacity supporting AI factories across North America, including an 8,000-route-mile program targeting some of the fastest-growing AI corridors in the United States. The project encompasses six new long-haul routes along with overbuilds of existing network across 10 high-demand corridors. The announcement arrives as AI data center development moves beyond the largest established hubs toward markets where power and land may be more readily available, but fiber capacity cannot necessarily be taken for granted. That geography is increasingly important. NVIDIA has separately developed “scale-across” networking technology designed to allow AI infrastructure distributed among different buildings — or even data centers separated by hundreds of kilometers — to operate as a more unified computing environment. Put together, the developments suggest that networking is becoming inseparable from the AI factory buildout itself. Power may determine where the next generation of AI infrastructure can be built. Fiber will increasingly determine how effectively those sites can participate in the larger AI ecosystem. Fiber Follows the Power Zayo CEO Steve Smith said AI demand is changing both where network infrastructure is needed and how aggressively capacity must be deployed ahead of development. “AI is fundamentally reshaping where and how network infrastructure needs to be built across the U.S.,” Smith said. The company’s 8,000-mile program is more nuanced than that top-line number might suggest. Zayo disclosed in April that the expansion includes approximately 3,000 route miles across six new long-haul routes, plus more than 5,000 route miles of overbuilds across 10 existing corridors. Zayo

Southern’s 17 GW Pipeline Puts AI Power Demand Into Utility Math

The headline number from Southern Company’s latest earnings report is hard to miss: electricity use by data centers across the utility’s system increased 55% in the second quarter compared with a year earlier. But the more consequential numbers may be the ones sitting behind it. Southern now has more than 1.2 GW of operating data center load, up by more than 500 MW from a year ago. At the same time, its electric utilities have signed contracts and large-load agreements totaling more than 17 GW by the mid-2030s, with another 8 GW in late-stage development and a prospective pipeline of large industrial and data center projects exceeding 75 GW. That leaves an enormous gap between the data center megawatts consuming electricity today and the load Southern has contractually positioned itself to serve during the next decade. For the data center industry, that gap may be the most important part of Southern’s second-quarter story. It offers a look at how utilities are beginning to convert the AI infrastructure boom from forecasts and campus announcements into contracts, generation procurement, transmission investment and eventually energized capacity. From Contracts to Megawatts Southern added roughly 6 GW of contracted large load during the quarter alone. Alabama Power signed three projects representing about 3 GW, while Georgia Power reached a 25-year agreement to serve OpenAI’s planned project in Effingham County near Savannah. That facility is expected to require approximately 3.2 GW and begin taking electric service in phases in 2028. The numbers nevertheless require an important distinction. Seventeen gigawatts contracted does not mean 17 GW will suddenly appear on Southern’s grid. Large data center campuses ramp gradually, often over several years, and Southern executives acknowledged that actual customer ramp schedules do not always match the assumptions made when projects are first approved. CEO Chris Womack said

PORTS-Pike Takes Shape as an 8-GW AI Infrastructure Model

Back on March 31, 2026, we discussed we discussed SoftBank and SB Energy’s plans to redevelop the former Portsmouth Gaseous Diffusion Plant site near Piketon as a 10-GW artificial intelligence data center campus supported by almost an equal amount of new power generation. At the time, the plan called for as much as 10 GW of new generation, including 9.2 GW of natural gas capacity, along with approximately $4.2 billion of high-voltage transmission infrastructure developed with AEP Ohio. An initial 800-MW data center phase was targeted for service in 2028. The March story was notable because Pike County appeared to offer a preview of a new model for building hyperscale infrastructure: develop the generation, transmission and data center simultaneously rather than wait for an increasingly congested regional grid to deliver multiple gigawatts of capacity. Not to mention the reuse of a brownfield site with the encouragement of the federal government. Since then, almost every important part of the project has moved forward, and on August 17, the most consequential missing pieces fell into place. NVIDIA announced that it will become the exclusive AI compute infrastructure provider for the PORTS-Pike Technology Campus. OpenAI will be the data center customer, signing a 20-year lease with SB Energy for approximately 8 GW of IT capacity. NVIDIA will invest another $1.5 billion in SB Energy and provide credit support for the land, power and shell infrastructure behind an initial 4.25 GW of IT load, with an option covering approximately another 3.75 GW. The Securities and Exchange Commission filing accompanying the announcement makes the financial commitment even more significant. NVIDIA disclosed that its aggregate payment obligation associated with its initial commitment is capped at $105 billion. That is not a conventional capital commitment to spend $105 billion building the campus, nor is it simply a

Nvidia scales back financing guarantee for OpenAI data center

Nvidia is scaling back a proposed financial guarantee tied to a massive OpenAI data center project in Ohio, reducing its initial commitment from as much as $250 billion to less than $120 billion, according to report in the Wall Street Journal. Earlier this month, Nvidia announced partnerships with major financial firms including Apollo Global Management, BlackRock, Blackstone, Brookfield Asset Management, Goldman Sachs and KKR, aimed at mobilizing more than $500 billion in capital for AI computing infrastructure. The change represents a significant restructuring of Nvidia’s role in financing the planned facility, which is being developed by SB Energy, a subsidiary of SoftBank. Under the revised arrangement, Nvidia would guarantee financing for the project’s first phase, representing roughly 5 gigawatts of capacity, or half of the total proposed capacity. Financing for the remaining capacity would be considered separately at a later stage.

Microsoft will invest $80B in AI data centers in fiscal 2025

And Microsoft isn’t the only one that is ramping up its investments into AI-enabled data centers. Rival cloud service providers are all investing in either upgrading or opening new data centers to capture a larger chunk of business from developers and users of large language models (LLMs). In a report published in October 2024, Bloomberg Intelligence estimated that demand for generative AI would push Microsoft, AWS, Google, Oracle, Meta, and Apple would between them devote $200 billion to capex in 2025, up from $110 billion in 2023. Microsoft is one of the biggest spenders, followed closely by Google and AWS, Bloomberg Intelligence said. Its estimate of Microsoft’s capital spending on AI, at $62.4 billion for calendar 2025, is lower than Smith’s claim that the company will invest $80 billion in the fiscal year to June 30, 2025. Both figures, though, are way higher than Microsoft’s 2020 capital expenditure of “just” $17.6 billion. The majority of the increased spending is tied to cloud services and the expansion of AI infrastructure needed to provide compute capacity for OpenAI workloads. Separately, last October Amazon CEO Andy Jassy said his company planned total capex spend of $75 billion in 2024 and even more in 2025, with much of it going to AWS, its cloud computing division.

John Deere unveils more autonomous farm machines to address skill labor shortage

Join our daily and weekly newsletters for the latest updates and exclusive content on industry-leading AI coverage. Learn More Self-driving tractors might be the path to self-driving cars. John Deere has revealed a new line of autonomous machines and tech across agriculture, construction and commercial landscaping. The Moline, Illinois-based John Deere has been in business for 187 years, yet it’s been a regular as a non-tech company showing off technology at the big tech trade show in Las Vegas and is back at CES 2025 with more autonomous tractors and other vehicles. This is not something we usually cover, but John Deere has a lot of data that is interesting in the big picture of tech. The message from the company is that there aren’t enough skilled farm laborers to do the work that its customers need. It’s been a challenge for most of the last two decades, said Jahmy Hindman, CTO at John Deere, in a briefing. Much of the tech will come this fall and after that. He noted that the average farmer in the U.S. is over 58 and works 12 to 18 hours a day to grow food for us. And he said the American Farm Bureau Federation estimates there are roughly 2.4 million farm jobs that need to be filled annually; and the agricultural work force continues to shrink. (This is my hint to the anti-immigration crowd). John Deere’s autonomous 9RX Tractor. Farmers can oversee it using an app. While each of these industries experiences their own set of challenges, a commonality across all is skilled labor availability. In construction, about 80% percent of contractors struggle to find skilled labor. And in commercial landscaping, 86% of landscaping business owners can’t find labor to fill open positions, he said. “They have to figure out how to do

2025 playbook for enterprise AI success, from agents to evals

Join our daily and weekly newsletters for the latest updates and exclusive content on industry-leading AI coverage. Learn More 2025 is poised to be a pivotal year for enterprise AI. The past year has seen rapid innovation, and this year will see the same. This has made it more critical than ever to revisit your AI strategy to stay competitive and create value for your customers. From scaling AI agents to optimizing costs, here are the five critical areas enterprises should prioritize for their AI strategy this year. 1. Agents: the next generation of automation AI agents are no longer theoretical. In 2025, they’re indispensable tools for enterprises looking to streamline operations and enhance customer interactions. Unlike traditional software, agents powered by large language models (LLMs) can make nuanced decisions, navigate complex multi-step tasks, and integrate seamlessly with tools and APIs. At the start of 2024, agents were not ready for prime time, making frustrating mistakes like hallucinating URLs. They started getting better as frontier large language models themselves improved. “Let me put it this way,” said Sam Witteveen, cofounder of Red Dragon, a company that develops agents for companies, and that recently reviewed the 48 agents it built last year. “Interestingly, the ones that we built at the start of the year, a lot of those worked way better at the end of the year just because the models got better.” Witteveen shared this in the video podcast we filmed to discuss these five big trends in detail. Models are getting better and hallucinating less, and they’re also being trained to do agentic tasks. Another feature that the model providers are researching is a way to use the LLM as a judge, and as models get cheaper (something we’ll cover below), companies can use three or more models to

OpenAI’s red teaming innovations define new essentials for security leaders in the AI era

Join our daily and weekly newsletters for the latest updates and exclusive content on industry-leading AI coverage. Learn More OpenAI has taken a more aggressive approach to red teaming than its AI competitors, demonstrating its security teams’ advanced capabilities in two areas: multi-step reinforcement and external red teaming. OpenAI recently released two papers that set a new competitive standard for improving the quality, reliability and safety of AI models in these two techniques and more. The first paper, “OpenAI’s Approach to External Red Teaming for AI Models and Systems,” reports that specialized teams outside the company have proven effective in uncovering vulnerabilities that might otherwise have made it into a released model because in-house testing techniques may have missed them. In the second paper, “Diverse and Effective Red Teaming with Auto-Generated Rewards and Multi-Step Reinforcement Learning,” OpenAI introduces an automated framework that relies on iterative reinforcement learning to generate a broad spectrum of novel, wide-ranging attacks. Going all-in on red teaming pays practical, competitive dividends It’s encouraging to see competitive intensity in red teaming growing among AI companies. When Anthropic released its AI red team guidelines in June of last year, it joined AI providers including Google, Microsoft, Nvidia, OpenAI, and even the U.S.’s National Institute of Standards and Technology (NIST), which all had released red teaming frameworks. Investing heavily in red teaming yields tangible benefits for security leaders in any organization. OpenAI’s paper on external red teaming provides a detailed analysis of how the company strives to create specialized external teams that include cybersecurity and subject matter experts. The goal is to see if knowledgeable external teams can defeat models’ security perimeters and find gaps in their security, biases and controls that prompt-based testing couldn’t find. What makes OpenAI’s recent papers noteworthy is how well they define using human-in-the-middle