Ask HN: SQL or NoSQL?

Social media are typically quite heavy on tree traversals. That kind of pattern is very similar to trying to resolve a deep ORM query or a deep GraphQL query which also doesn't map very well on 'traditional' relational databases https://en.wikipedia.org/wiki/Object%E2%80%93relational_impe.... I believe this 'issue' depends on: A) the type of join B) whether your relational databases flattens between consecutive joins. C) is there easy/efficient pagination on multiple levels

The type of join shouldn't be a problem, SQL engines should in most cases be able to determine the best join. In the cases it can't you can go start tweaking (although tricky to get right, especially if your data evolves, it's possible, you probably want to fix your query plan). B is however tricky and a performance loss since it's really a bit silly that data is flattened into a set each time to be then (probably) put into a nested (Object-Oriented or JSON) format to provide the data to the client. This is closely related to C, in a social graph you might have nodes (popular people or tweets) who have a much higher amount of links than others. That means if you do a regular join on tweets and comments and sort it, on the tweet you might not get beyond the first person. Instead, you probably only want the first x comments. That query might result in an amount of nested groups. So it might look more like the following SQL (wrote it by heart, probably not correct):

SELECT tweet.*, jsonb_agg(to_jsonb(comment)) ->> 0 as comments, FROM tweet JOIN comment ON tweet.id = comment.tweet_id

GROUP BY tweet.id HAVING COUNT(comment.tweet_id) That obviously becomes increasingly complex if you want a feed with comments, likes, retweets, people, etc.. all in one. There are reasons why two engineers that helped to scale twitter create a new database (https://fauna.com/) where I work. Although relational, the relations are done very differently. Instead of flattening sets, you would essentially walk the tree and on each level join. I did an attempt to explain that here for the GraphQL case: https://www.infoworld.com/article/3575530/understanding-grap...

TLDR, in my opinion you can definitely use a traditional relational database. But it might not be the most efficient choice due to the impedance mismatch. Relational applies to more than traditional SQL databases though, graph database or something like fauna is also relational and would be a better match (Fauna is similar in the sense that joins are very similar to how a graph database does these). Obviously I'm biased though since I work for Fauna.

I would go a step further, you can't even talk about NoSQL vs SQL. It's about database features, the join patterns, how scaling happens, both are overlapping more and more and will continue to overlap more. Products built on SQL are aiming to scale and 'NoSQL' is aiming to provide the features that SQL provides in a scalable manner. Her original statement was already quite confusing. A relational store doesn't necessarily mean SQL, many 'NoSQL' offer relations and are a perfect fit for social media or were even built to support this kind of applications :)

Freeform flexibility is one aspect, but a document-style could also simply be a preference for how you want to structure your data or typically has an impact on how joins happen (if the document-database offers joins). Those joins will work in a graph-like fashion instead of how flat sets are typically joined. Or a nested document could be an optimization to provide data in the exact format that your client wants to see it. Although some document databases have popularized the idea that you should join in the client because they didn't provide joins initially, it doesn't have to be that way.

Mixing paradigms in one database is probably going to be happening more. Just like Postgres is offering a 'document' style, some document databases are offering documents with relations. It wouldn't surprise me to see document databases offer optional schemas. I think that the future is a mix of options and tools in one database (which JSONB columns are a first step for). Depending on the situation we'll just model differently. The best database might become the one that makes us use these different tools most elegantly together. The difference between a document and a table is only a 'flatten' and a 'schema' away.