4 ms·

Doing graph processing by explicitly populating a temp table is nearly as fast in many cases as neo4j with the added benefit of a vastly improved analytics expe

by batbomb 13y ago

Doing graph processing by explicitly populating a temp table is nearly as fast in many cases as neo4j with the added benefit of a vastly improved analytics experience if you plan to analyze the data or do range queries, etc... It is more work to do it smartly and efficiently, but it is possible. I'll try to find a python script I wrote as a PoC with SQLite. CTEs and recursive queries are okay for many cases, but they can blow up your RDBMS temp space easily if you aren't careful. Especially useless is a non-materialized view based on them.

aidos 13y ago
I'd be interested in seeing how you approach some of the things that graphs are better at. I have an understanding of creating efficient tree structures in a relational model - I haven't thought about the more general case of graphs though. I was recently involved in a project that was using neo4j as a datastore. In the end we switched to postgres because the cognitive load was lower, the tooling was better and the relationship management was easier and more explicit. The trade off is going to be that later the relationship exploration will be a little harder. My plan was to add a graph datastore back in to mirror the relationships in the system so we could query it.
einhverfr 13y ago
I think your big limiting factor in graph analysis with recursive cte's is that PostgreSQL is relational and therefore set-based. This effectively forces any search into a breadth-first search. I don't see any reason why doing breadth-first searches would be any slower on PostgreSQL than Neo4j. But the question is, what happens when bredth-first is not what you want to do? Now with some work, it should be quite possible to do depth-first or other sorts of searches, but you'd probably want to think very, very carefully about the implementation and use something besides a recursive cte. So I would expect something like "show me everything within two hops of x" to perform pretty well if you designed your query right (and you'd want to put the starting point and the limit in your CTE so you aren't pulling the whole graph first to check). I would expect "show me the shortest route from x to y" to perform much better through a non-relational approach. However getting anything to work well would require carefully thinking through your query.

by batbomb 13y ago

aidos 13y ago

I'd be interested in seeing how you approach some of the things that graphs are better at. I have an understanding of creating efficient tree structures in a relational model - I haven't thought about the more general case of graphs though. I was recently involved in a project that was using neo4j as a datastore. In the end we switched to postgres because the cognitive load was lower, the tooling was better and the relationship management was easier and more explicit. The trade off is going to be that later the relationship exploration will be a little harder. My plan was to add a graph datastore back in to mirror the relationships in the system so we could query it.

einhverfr 13y ago

I think your big limiting factor in graph analysis with recursive cte's is that PostgreSQL is relational and therefore set-based. This effectively forces any search into a breadth-first search. I don't see any reason why doing breadth-first searches would be any slower on PostgreSQL than Neo4j. But the question is, what happens when bredth-first is not what you want to do? Now with some work, it should be quite possible to do depth-first or other sorts of searches, but you'd probably want to think very, very carefully about the implementation and use something besides a recursive cte. So I would expect something like "show me everything within two hops of x" to perform pretty well if you designed your query right (and you'd want to put the starting point and the limit in your CTE so you aren't pulling the whole graph first to check). I would expect "show me the shortest route from x to y" to perform much better through a non-relational approach. However getting anything to work well would require carefully thinking through your query.