Beyond Joins: Optimizing Graph Traversals in SurrealDB

In traditional relational databases, modeling complex relationships—like a user following other users or a comment belonging to a specific post—requires multiple nested JOIN operations. As your dataset grows, the computational cost of these joins scales poorly, often forcing the engine to scan indexes or tables across disparate sets to find matching IDs.

SurrealDB addresses this bottleneck by using a multi-model approach that treats relationships as first-class citizens. Instead of calculating connections at query time via joins, SurrealDB uses direct record pointers to traverse edges. This allows you to navigate graph structures with near-constant time performance, significantly reducing the latency of deeply nested data structures.

The Problem: The Join Bottleneck

In a standard SQL environment, fetching a list of friends' latest posts' requires joining the users table with a follows table, and then back to the posts table. Each join adds a layer of complexity to the execution plan. If the database has millions of rows, the overhead of matching keys across these tables becomes the primary performance killer.

SurrealDB replaces this with edges. An edge is a record that connects two other records using their unique IDs (e.g., user:alice). Because the edge stores the direct pointer to the target, the database doesn't "search" for the connection; it simply follows the pointer.

Practical Example: Traversing Social Data

Let's look at a concrete scenario where we want to find all posts from users that a specific user follows. In SurrealDB, we define a follows edge between users and a post table.

First, we create our data and the relationship:

# Create users and posts
CREATE user:alice { name: 'Alice' };
CREATE user:bob { name: 'Bob' };
CREATE post:1 { content: 'Hello from Bob!', author: user:bob };
CREATE post:2 { content: 'Graph DBs are fast', author: user:bob };

# Create a follow relationship (the relationship)
REL follows FROM user:alice TO user:bob;

To retrieve all posts by the people Alice follows, we use the graph traversal syntax (the arrow ->). This replaces what would be a complex multi-table join in SQL:

# Fetch all posts from users Alice follows
SELECT * FROM (user:alice<-follows<---post);

In this query, the engine starts at user:alice, jumps across the follows edges to the connected users, and then jumps to their post records. This is a direct path-following operation rather than a set-matching operation across the entire posts table.

Performance Verification and Diagnostics

To ensure your graph traversals are performing as expected, you should use the EXPLAIN statement. This allows you to see if the database is performing an efficient index lookup or falling back to a table scan.

# Check the execution plan
EXPLAIN SELECT * FROM (user:alice<-follows<---post);

When reviewing the output, look for IndexLookup or DirectLookup. If you see TableScan, it indicates that you may need an index on the attributes you are filtering during the traversal (e.g., a timestamp or a status field on the post record).

Trade-offs and Limitations

While graph traversal is fast, it is not a silver bullet for every use case. The primary trade-off is data modeling flexibility. Because SurrealDB relies on record pointers for its speed, restructuring your data schema (moving records between table types) can be more complex than updating a foreign key in a flat relational table.

Furthermore, deep recursive traversals (e.g., "friends of friends of friends") can still become resource-intensive if not bounded. Each hop in the graph requires a separate memory access operation. For extremely deep hierarchical data, it is often better to limit the depth of your query or use caching strategies for frequently accessed paths.

Actionable Takeaway

When designing applications that require frequent relationship traversal, consider modeling your core entities as SurrealDB records with explicit edges rather than relying on foreign keys. This approach excels for use cases like social networks, recommendation engines, or any domain where you frequently ask "what is connected to what?" rather than "what are the properties of these related records?" The key is to identify your most common query patterns early and structure your schema to minimize the need for traditional joins.