from Hacker News

A Different Type of SQL Recursion with PostgreSQL

by vbilopav on 9/16/23, 2:59 PM with 10 comments

• by ttfkam on 9/16/23, 3:25 PM

  Good comparison but seems to be missing how fast they run. If they're about the same, the skill set and expectations of the devs tips the scales. However if one is noticeably faster than the other, especially on a frequently run query, devs need to adapt to understanding the faster variant despite their initial comfort level.

• by roenxi on 9/16/23, 9:21 PM

  Toy examples struggle here because if things are simple it is much cleverer to use multiple queries from a real programming language that can be debugged. Recursion in SQL is more challenging to debug than a loop in a language with flow control. And if the problem isn't a toy problem, trying to solve it with Postgres as the graph engine is probably going to open up a new can of worms that need to be dealt with.

  It takes a strange situation for Postgres' recursion features to be a good idea. All 3 solutions would be red flags to me if I saw them in a real codebase - and accompanied by of a large number of other unmaintainable SQL functions. This is the fast way to turn a small for loop into a week of Senior Dev billable hours. Someday I'll see an exception to that rule of thumb, so far no luck.

• by Rizz on 9/16/23, 5:43 PM

  It seems like the first variant is overly complicated. When you write about set theory you should be able to figure out that you don't have to rely on the effects of the union operator, the result you want is a simple difference with previous levels of the recursive cte, or a terminal query that groups by the table and shows the minimum level

• by vbilopav on 9/17/23, 1:31 PM

  A follow up on this article was written today

  [Recursion with PostgreSQL, Follup 1, Perfomances](https://github.com/vb-consulting/blog/discussions/4)

  I managed to get some really good perfomances (757K records in 5 seconds).

  Also, method 1 contained a nasty bug :(

  That's why next time I'll try to focus on debugging and testing.

• by convolvatron on 9/16/23, 7:33 PM

  this is where you really want to consider something like datalog instead.

  but wouldn't an abstraction (I think like 'connect by') that computes the fixed point or transitive closure be really useful in many of these cases and involve less cognitive overhead (even if its less general)

• by cryptonector on 9/17/23, 4:47 PM

  > Recursive CTE queries are powerful but confusing and limiting. This old-school procedural programming approach may be a much better fit for developers untrained with SQL, and that is, in my opinion, the majority. On the other hand, the procedural approach may be something most developers feel comfortable with.

  No please. Recursive CTEs are really easy to understand, and they are tail-recursive loops, so they are just loops, and they are easy to understand as such if loops is what you understand. Writing a loop in procedural SQL is not better, just a) wordier, b) not easily amenable to relational algebra. (b) is a big deal.