Article summary
Most list endpoints start the same way: a SQL query, a few joins, a LIMIT/OFFSET, and a DTO.
It works fine. Until it doesn’t.
I had a breaking point with a list page that needed to filter by data three relationships away from the root entity. The join got ugly fast. Pagination got weird. Adding one more filter meant another join, another subquery, or another DISTINCT we didn’t fully trust.
So we rewrote the endpoint as a graph traversal.
Same data. Different shape. Instead of building one increasingly complex SQL query, we started at a node, walked relationships, applied filters along the way, and collected the results.
Here’s what I’d want to know before doing that again.
When Joins Are Still the Right Call
If your filters live on the root entity, or maybe one relationship away, use a join.
Seriously. Joins are simple, familiar, and your database is very good at optimizing them. You do not need a graph-shaped solution for every list endpoint.
A graph query starts to make sense when:
-
- Filters span multiple relationships
- Many-to-many joins are multiplying rows
- The same entity can be reached through multiple paths
- Pagination counts don’t match the results
- You keep adding
DISTINCTto make bugs go away
That last one is usually the smell.
DISTINCT is not bad. But when it becomes a reflex, it’s worth asking whether the shape of the query still matches the shape of the problem.
What You Gain
The biggest win was not performance.
A well-tuned SQL join can absolutely beat a graph traversal. The win was expressiveness.
Some filters are easier to describe as a path:
Find leases where the unit has a bedroom connected to a tenant who signed within this date range.
You can write that as SQL. But once enough relationships are involved, the query starts hiding the business rule. The graph version made the rule easier to see.
It also made pagination more honest.
Join fan-out can make LIMIT 25 behave like “25 joined rows,” not “25 leases.” Then you end up deduplicating after the fact, and suddenly a page that asked for 25 results has 17.
With a traversal, we were collecting distinct root nodes by design. LIMIT 25 actually meant 25 leases.
What It Costs
This was not free.
First, graph queries introduced a new mental model. Everyone knows joins. Not everyone thinks in nodes and edges. That meant spending time explaining how the traversal worked and where filters should live.
Second, unit tests were not enough. The bugs showed up at the boundary between traversal logic, filter predicates, and real data shape. Integration tests against a real database were the only tests that caught the important stuff.
We found one bug where a lease was incorrectly included because of how a bedroom relationship was traversed. A mock would never have caught it.
Third, this needed a migration path. We did not rewrite every list endpoint. We picked the endpoint that was hurting and left the boring ones alone.
That was the right call.
The Takeaway
Graph queries are not a better version of joins. They solve a specific problem.
If your endpoint has simple filters, use SQL joins and move on.
But if your joins keep getting longer, your DISTINCTs are multiplying, and your pagination is lying to you, the problem may not be the database. It may be that you’re trying to express a graph-shaped question as a table-shaped query.
That’s when a graph traversal is worth considering.