Why do you think this approach isn't scalable? would love to hear your input on that. Also what commercial database systems do you think will be good for this?
The suggested approach most likely requires a lot of recursive backtracking. Of course, there's an efficient way to implement this, and that's what most commercial databases' path analytics features do. Here's one example by Oracle: https://docs.oracle.com/database/121/DWHSG/pattern.htm
I've always found it befuddling why so many developers want to use Solr/Elasticsearch for analytics heavylifting. It's probably because
1. SQL is not the most intuitive (although most pervasive) API for data analysis
2. Much of the data is already in Solr/Elasticsearch to make your data searchable/perform simple roll-ups and filtering, etc., so it'd be great if you can do more complex analytics against them as well
AS to why Solr/Elasticsearch is not ideal: the existence of superior alternatives that is OLAP databases.
I've always found it befuddling why so many developers want to use Solr/Elasticsearch for analytics heavylifting. It's probably because
1. SQL is not the most intuitive (although most pervasive) API for data analysis
2. Much of the data is already in Solr/Elasticsearch to make your data searchable/perform simple roll-ups and filtering, etc., so it'd be great if you can do more complex analytics against them as well
AS to why Solr/Elasticsearch is not ideal: the existence of superior alternatives that is OLAP databases.