This question is likely to generate a lot of opinions. In comes the "NoSQL" crowd touting in vague terms why relational is bad and this and that.
The issue is complicated because the relational database method is intended to be a declarative approach where you specify your solution and the relational database management system (RDBMS) would then optimize your declarative expressions.
And that's where the problems arise. A specific RDBMS might not have the method that might be best for optimizing the specific thing. Or it might not recognize your expression of benefitting from whatever method of optimization in does have. And there are so many twists and tweaks where something can go totally off the rails for unknown reasons (I have found Oracle to be prone to such weird problems, even total crash bugs, which they are slow to fix, if ever.)
To get this question out of hand-waving opinion mongering, you should probably show some work of how you express your requirements and then speculate about how RDBMS might realize your query, and then only can one start discussing trade-offs of different solutions. You also have to be aware of the framing of the question. NoSQL always wins because they can just hard-code to one partial problem but the full context of the full system (especially ACID requirements) don't go away. It's not like graph databases did not exist before the relational model was invented by Edgar Codd.
CREATE TABLE User_ (
id UUID PRIMARY KEY,
name text
);
CREATE TABLE Post (
id UUID PRIMARY KEY,
respondingToPostId UUID REFERENCES Post(id),
time timestamp NOT NULL DEFAULT current_timestamp,
authorId UUID REFERENCES User_(id),
text text
);
CREATE TABLE Reaction (
postId UUID REFERENCES Post(id),
time timestamp NOT NULL DEFAULT current_timestamp,
reactionType char NOT NULL DEFAULT 'L'
);
Can this be made any simpler? I don't think you want anonymous reactions just counted, so you won't be able to do without the Reaction table. No premature optimization here!
Now the transactions, every Post is an INSERT INTO Posts, every click of "like" or "haha" or whatever becomes an INSERT INTO Reaction. Simple.
I merged Post and Comment into Post (kind'a like Twitter style). I don't think for optimization it helps much to have separate Post vs. Comment tables, but if you want you can of course do it
CREATE TABLE Post (
id UUID PRIMARY KEY,
time timestamp NOT NULL DEFAULT current_timestamp,
authorId UUID REFERENCES User_(id),
text text
);
CREATE TABLE Comment (
id UUID PRIMARY KEY,
postId UUID REFERENCES Post(id),
respondingToCommentId Comment(id),
time timestamp NOT NULL DEFAULT current_timestamp,
authorId UUID REFERENCES User_(id),
text text
);
This just pushed the issue one down. So I stick with the simpler model I gave above. But for topological sorting, we still need some response ordinal which is a zero padded number of the post.
CREATE TABLE Post (
id UUID PRIMARY KEY,
respondingToPostId UUID REFERENCES Post(id),
responseOrdinal CHAR(4) DEFAULT (
SELECT to_char(to_number(max(reponseOrdinal) + 1), '0999')
FROM Post r
WHERE r.respondingToPostId = this.respondingToPost),
time timestamp NOT NULL DEFAULT current_timestamp,
authorId UUID REFERENCES User_(id),
text text,
);
On this DEFAULT of the Post.responseOrdinal I am taking some liberty, probably no RDBMS would allow such as a default, but it could be done as a trigger. I pushed this in here for my transitive closure table that comes further below.
Reactions Ranking
First we have to deal with the Reactions (likes). We want some ranking order by likes. This is totally dynamic and likes will accumulate quickly. Likewise you might want to rank by how many comments a post got.
So just like I put the responseOrdinal in above, which I might have just written as
SELECT *,
TO_CHAR(
RANK() OVER(
PARTITION BY respondingToPostId
ORDER BY time, authorId),
'0999') AS responseOrdinal
FROM Post
the number of likes or replies I would put in here too. But I wonder if that is a good idea because it changes all the time! Making frequent UPDATEs will possibly roll over a lot of space in your Post table. This could be good or bad. How would we write those?
CREATE TABLE PostReaction AS
SELECT post.id AS postId,
reactionType,
count(1) AS reactionCount
FROM Post
INNER JOIN Reaction r ON(r.postId = post.id)
GROUP BY(post.id)
This here reminds us that you might want likes and dislikes (ratioing) or you might want to deal with different reaction types "like" vs. "haha" vs. "angry", etc. Let's say you just care about how many reactions overall you have:
CREATE TABLE PostReactionCount AS
SELECT post.id AS postId,
reactionType,
count(1) AS reactionCount
FROM Post
INNER JOIN Reaction r ON(r.postId = post.id)
GROUP BY(post.id)
and then every time you log a new reaction you'd have to update the reaction count. Say with an AFTER INSERT TRIGGER on Reaction. This being a trigger on INSERT into Reaction, then NEW is the Reaction being inserted.
UPDATE PostReactionCount prc
SET reactionCount = reactionCount + 1
WHERE prc.postId = NEW.postId;
or, if you put this reactionCount right into post, you can say
UPDATE Post
SET reactionCount = reactionCount + 1
WHERE post.id = NEW.postId;
Probably would decrement for retracted reactions.
Similar thing can be done for insert into Post to count replies if that is the ranking metric. Or some aggregate of replies, reactions, ratio-ing, whatever.
Now to display post in rank order of reactions:
SELECT *
FROM Post
ORDER BY reactionCount DESC
LIMIT 50
or
WITH RankedPostId AS (
SELECT postId,
FROM PostReactionCount
ORDER BY reactionCount DESC
LIMIT 50
)
SELECT *
FROM RankedPostId INNER JOIN Post ON(post.id = postId)
or something with RANK() and a where clause on the ranking column.
The trade-off is where you want the massive UPDATE activity to reside, with the Post or with a separate table? Heck, if you use a column-oriented database it doesn't matter. This is the point, optimization done prematurely boxes you in. SQL is if anything a language for concise specification of the logic of the data, and in E.F. Codd's original idea manifoldly yet never perfectly implemented.
Transitive Closure of Reply Tree
Now as for transitive closures, these are easy to whip up when you analyze data offline, but in the middle of massive transaction load, you would have to somehow track those with something like a trigger. And of course, inserting a row into an index is also a "trigger" action of sorts, only built-in to the RDBMs.
Let's say we want the transitive closure of comments.
CREATE TABLE INTO PostPlus AS
SELECT respondingToPostId AS ancestorId,
1 AS distance,
responseOrdinal::text AS path,
postId AS descendantId,
FROM Post;
The ::text betrays that I use PostgreSQL and I am fed up with the stupid VARCHAR(4000) limit of Oracle, let alone the stupid "2" in "VARCHAR2".
but I pushed it up already. Next to populate the PostPlus table once I always do this:
INSERT INTO PostPlus AS
SELECT a.ancestorId,
a.distance + d.distance,
a.path ||'.'|| d.path,
d.descendantId
FROM PostPlus a
INNER JOIN PostPlus d
ON(a.descendantId = d.ancestorId)
WHERE (a.ancestorId, d.descendantId) NOT IN (
SELECT ancestorId, descendantId FROM PostPlus);
You run this INSERT until nothing new is inserted and you are done (this converges very quickly because we are extending the paths on both ends). I don't use "CONNECT BY" or WITH "recursive". Because this is actually usually faster on any system I know (haven't played with IBM's for a long time). But I am all for recursive WITH if the RDBMS knew how to really optimize it in all situations, especially transaction oriented use.
Problem with transitive closure tables is, you need to keep them up to date. To keep this PostPlus up to date you would need to follow every INSERT into Post with several INSERTs into PostPlus, like do some TRIGGER where usually NEW is the new row just inserted, so I might do:
INSERT INTO PostPlus AS
SELECT NEW.respondingToPostId AS ancestorId,
1 AS distance,
NEW.responseOrdinal AS path,
new.postId AS descendantId
UNION ALL
SELECT p.ancestorId,
p.distance + 1,
p.path ||'.'||NEW.responseOrdinal
new.postId
FROM PostPlus p
WHERE p.descendantId = new.respondingToPostId;
So far so good. Now whether you actually need all this is a question only answered by the SELECT queries we have to write (declaratively, think of it as specification of the system we want to build, optimization may come later.)
Taking it to Task
So for Task 2 it's
WITH RankedDirectReply AS (
SELECT reactionCount,
postId AS replyId,
distance AS replyDistance,
path AS replyPath,
FROM PostPlus as reply
INNER JOIN PostReactionCount
ON(postId = descendantId)
WHERE ancestorId = :givenPostId
AND distance = 1
ORDER BY reactionCount DESC
LIMIT 50
), ReplyTree AS (
SELECT reactionCount,
replyId, distance, path FROM ReplyTree
UNION ALL
SELECT reactionCount,
descendantId,
directReply.distance + replyTree.distance,
directReply.path ||'.'|| replyTree.path
FROM RankedDirectReply directReply,
INNER JOIN PostPlus replyTree
ON(replyTree.ancestorId = replyId)
ORDER BY reactionCount, path
)
SELECT *
FROM ReplyTree
INNER JOIN Post reply
ON(reply.id = replyId)
ORDER BY reactionCount, path
Can this be optimized? Probably. But at least now we know what we're talking about.
Your Task 2 is just a little more annoying because it includes only one reply of a reply, also ranked. I won't write it out.
The question poses itself if the PostPlus table should be updated to have replies? Or if the first, second, third, whatever level of post id should also be kept in the PostPlus table so that you can quickly join them with the latest PostReactionCount to re-order the tree by what's most liked.
This is all not too hard. Just annoying to go over another special case with 3 levels now.
Now I know you asked for "practical experience" and all I did was essentially fill in your work you should have done, i.e., specifying the what in SQL so that the how can even be discussed with more than just slinging opinions and hand-waving.
Any "No-SQL solution" must give account how they deal with the inserts and updates. I suppose sacrificing a little bit on ACID properties can be entertained, especially for the response counts. But you could do that around an SQL database too. And what exactly would you speed up? You could allow the counts to lapse behind a bit. But if you want immediate update on the user's screen?
Is a relational database still a case for such a problem nowadays?- in a word, yes. If you want to try graph databases (I've no experience with these and am not saying they're unsuitable nor even that they're not as good as (or even not better than) a classic RDBMS), you could look at AgensGraph which is a PostgreSQL fork and/or the Apache AGE (A Graphics Extension) which is a PostgreSQL extension (better than a fork IMHO - many have tried, few have been chosen) using OpenCypher.