Skip to main content

// advent-of-postgres/day-9

Day 9 of 24IntermediateRecursive CTEsGraphs

The Blast Radius

If the DNS service goes down, what else breaks? Walk the dependency graph with a recursive query, and mind the loop.

Opens 9 December 2026, 00:00 UTC

The DNS servers for chimney lookups are getting a risky upgrade tonight. Before Alabaster signs off, she wants to know how many services could go down with it.

Every call between services is in dependencies:

Column Type Notes
service text This service
depends_on text A service it calls

If a service goes down, every service that calls it goes down too, and so on up the chain. Somewhere in the table there is an old loop that nobody cleaned up.

Your task

If chimney-dns goes down, how many other services go down with it, directly or through other services?

Submit the number.

Stuck? Show a hint

A recursive CTE with UNION, not UNION ALL, stops when it finds no new rows. That is what keeps the loop from running forever.

No database yet. You get a real Postgres database on Neon, free, with no account.
One statement at a time. Ctrl+Enter or Cmd+Enter runs it.

Download today's data as SQL to solve it on your own Postgres (14 or later).

Solved this puzzle? Share it.

Tag @thedevopsdaily and share it with the community.