5 ms·

This post is only scratching the surface of what can be achieved with a.. fully armed, and operational battle station.. powered by recursive SQL in postgres. S

by Dowwie 6y ago

This post is only scratching the surface of what can be achieved with a.. fully armed, and operational battle station.. powered by recursive SQL in postgres. Simply adding a parent_id column to a table (and a few constraints) can facilitate all sorts of useful network topologies. I doubt that the recursive query against a DAG is going to give better performance than a query against a DAG within a graph database, but the relational query may be sufficient for your work and not require the cost of graph db investment. I think that people need to move beyond simple recursive sql example blog posts and share the more challenging examples. Recursive queries can become challenging quickly.

e1g 6y ago
In Postgres, there is a data type ltree[0] which can materialize the full path in a DAG and help do relatively complex searches. Tree coordinates are typically stable, so it's easier and faster to recalculate the path on writes vs reads. We use parent_id + ltree to do search hundreds of thousands of relationships in milliseconds. ltree is no match in power to CTEs, but it is perfect for a simple domain like the one used in the article. [0] https://www.postgresql.org/docs/current/ltree.html https://www.postgresql.org/docs/current/ltree.html
- Tostino 6y ago
  I 2nd this recommendation. If nothing else, it helps to have fewer recursive queries which are still treated as optimization barriers when you need to do complex queries joining to your DAG. So using a nice indexable ltree is much better for performance when your queries get complicated.
- Guillaume86 6y ago
  Similarly SQL Server has hierarchyid (https://docs.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/t-sql/data-types/hierar...) if anyone is interested.
timClicks 6y ago
Do you know of any other resources for learning more?
- samokhvalov 6y ago
  Check out this slide deck, it's full of interesting stuff, including recursive CTE, arrays, LATERAL, and practically useful complex examples: https://www.slideshare.net/pgdayasia/how-to-teach-an-elephant-to-rocknroll https://www.slideshare.net/pgdayasia/how-to-teach-an-elephan...
082349872349872 6y ago
How far do you really want to go? It's possible to do a metacircular lisp-in-lisp-in-sql. If Turing says there's no attractive Schelling endpoint to the challenging examples, might as well stop after factorial[1]. [1] see also Checking a large routine, 1949
deleted 6y ago
[deleted]

by Dowwie 6y ago

e1g 6y ago

In Postgres, there is a data type ltree[0] which can materialize the full path in a DAG and help do relatively complex searches. Tree coordinates are typically stable, so it's easier and faster to recalculate the path on writes vs reads. We use parent_id + ltree to do search hundreds of thousands of relationships in milliseconds. ltree is no match in power to CTEs, but it is perfect for a simple domain like the one used in the article. [0] https://www.postgresql.org/docs/current/ltree.html https://www.postgresql.org/docs/current/ltree.html

Tostino 6y ago
I 2nd this recommendation. If nothing else, it helps to have fewer recursive queries which are still treated as optimization barriers when you need to do complex queries joining to your DAG. So using a nice indexable ltree is much better for performance when your queries get complicated.
Guillaume86 6y ago
Similarly SQL Server has hierarchyid (https://docs.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/t-sql/data-types/hierar...) if anyone is interested.

timClicks 6y ago

Do you know of any other resources for learning more?

samokhvalov 6y ago
Check out this slide deck, it's full of interesting stuff, including recursive CTE, arrays, LATERAL, and practically useful complex examples: https://www.slideshare.net/pgdayasia/how-to-teach-an-elephant-to-rocknroll https://www.slideshare.net/pgdayasia/how-to-teach-an-elephan...

082349872349872 6y ago

How far do you really want to go? It's possible to do a metacircular lisp-in-lisp-in-sql. If Turing says there's no attractive Schelling endpoint to the challenging examples, might as well stop after factorial[1]. [1] see also Checking a large routine, 1949

deleted 6y ago

[deleted]