What is the performance of a Postgres recursive query with a large depth on millions of rows? Should I use a graph database instead?

Question

I am trying to decide whether I should use a graph database or a relational one. I am fairly new to SQL so I am not sure how to test this as I don't have the millions of rows of data yet but I figured this could become a problem down the line so I am looking for some anecdotal experiences from people if possible.

Does anyone know the performance of a recursive query for a Postgres database with millions of rows in each table? Is the performance usually good enough to be able to query data in the database through a web api in sub 100-200ms with potentially a large depth of, lets say 30? The depth is unknown, it could be 1 or all the way to something large like 30 or even more.

My data currently maps very well to the relational data model as it is relational in nature, hence why I am leaning towards Postgres but I would like to execute a specific recursive query that is making me lean towards a graph database. The tables (lets say 5-6) will most likely be millions of rows each in production and the depth of the query is essentially unknown. An example of such a query would be:

Depth 1: 

EntityA transacts with -> EntityB

Depth 2:

EntityB transacts with -> EntityC
EntityB transacts with -> EntityD

...

Depth n:

until the last entities (leaves) don't transact with any other entities. At each depth, the entities can transact with many entities and the depth is unknown.

Should I use a graph database instead just because of this 1 specific query or can Postgres handle this in a reasonable time using its RECURSIVE command?

I have recursive queries on a ~30million rows table where I retrieve trees with ~2000 elements and depth of about 15 that run in well below a second - but this all depends on the the initial condition (for the non-recursive part) and the indexes you have in place. — user330315
– user330315, Commented Feb 2, 2022 at 21:02
I have recursive queries on a database of ~10M tweets; I use them to combine related tweets within a thread (fetching the `referring_to, etc) They run within seconds on a Raspberry Pi. — wildplasser
– wildplasser, Commented Feb 2, 2022 at 21:20

FrobberOfBits · Accepted Answer · 2022-02-04 18:19:28Z

0

Whenever you're dealing with recursive JOINs in a SQL system that's a dead giveaway that you're actually dealing with graphs and trying to figure out how to navigate a graph with SQL.

Using a graph database is a better option in this case.

For a much more complete answer -- see this other answer on a related question. Performance of arbitrary queries with Neo4j

answered Feb 4, 2022 at 18:19

FrobberOfBits

18.2k5 gold badges60 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

What is the performance of a Postgres recursive query with a large depth on millions of rows? Should I use a graph database instead?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related