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.
Someday I'll see an exception to that rule of thumb, so far no luck.
I used to think so too, then I found a use case. TBF, there exists an alternative that doesn't use recursion, but you wouldn't like that either, because it'd require a stored procedure / SQL UDF.
Requirement: Split a large sequential key-space (of a SERIAL PK) into the smallest number of non-overlapping, exhaustive key ranges where no range contains more than N keys. Extracting a range is very fast, so communicating with the app layer for coordination between two successive ranges would introduce too much latency overhead. But the DB also has a small timeout (order of minutes) on how long any statement may execute.
Breaking it down: we need iteration to start extracting the next key range after we've extracted one key range. But this iterative process cannot synchronise with app layer at the end/beginning of each iteration.
Solution: Synchronise with app layer / transaction manager every M iterations. If M is too large for statement timeout, reduce M and try again.
This can be done by either procedural code or by iteration via recursion in a single pass over the data. There exist alternative methods that can do this, but require multiple passes. In fact, the first attempted solution used one of those alternatives: bucket based partitioning.
But runtime performance objectives demanded the lowest latency, and the redundancy of extra passes could not be optimised away by PG 12. The single pass iteration-via-recursion method was twice as fast, and the recursion skeleton code was simple enough — equivalent to a recursive function with one tail call and an accumulator — so it was shipped.
Comments
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.
I used to think so too, then I found a use case. TBF, there exists an alternative that doesn't use recursion, but you wouldn't like that either, because it'd require a stored procedure / SQL UDF.
Requirement: Split a large sequential key-space (of a SERIAL PK) into the smallest number of non-overlapping, exhaustive key ranges where no range contains more than N keys. Extracting a range is very fast, so communicating with the app layer for coordination between two successive ranges would introduce too much latency overhead. But the DB also has a small timeout (order of minutes) on how long any statement may execute.
Breaking it down: we need iteration to start extracting the next key range after we've extracted one key range. But this iterative process cannot synchronise with app layer at the end/beginning of each iteration.
Solution: Synchronise with app layer / transaction manager every M iterations. If M is too large for statement timeout, reduce M and try again.
This can be done by either procedural code or by iteration via recursion in a single pass over the data. There exist alternative methods that can do this, but require multiple passes. In fact, the first attempted solution used one of those alternatives: bucket based partitioning.
But runtime performance objectives demanded the lowest latency, and the redundancy of extra passes could not be optimised away by PG 12. The single pass iteration-via-recursion method was twice as fast, and the recursion skeleton code was simple enough — equivalent to a recursive function with one tail call and an accumulator — so it was shipped.