The way TAO (and Notion to some degree) deal with this is by tightly coupling the persisted (SQL?) data store to an in-memory cache that make KV style reads like “get foo by foo_id” extremely fast. They also limit the kinds of queries developers can write to the ones more likely to be very fast on the infrastructure.
Pushing graph queues down to a single SQL query so that the SQL DB spends massive CPU time on joins can be an issue. Instead these systems can avoid talking to the SQL DB at all for example if they’re on the happy path just chasing graph edges.
Pushing graph queues down to a single SQL query so that the SQL DB spends massive CPU time on joins can be an issue. Instead these systems can avoid talking to the SQL DB at all for example if they’re on the happy path just chasing graph edges.