An efficient way to architecht a YouTube like comments section in DB
Quick explanation of the comments‑section bottleneck
I’m rewriting our prototype (React + json‑server) in Next.js with Convex and hit a data‑model problem around threaded comments.
Current model
- A Post keeps the IDs of its top‑level comments.
- Every Comment keeps the IDs of its direct replies (so replies are just more
Comment docs).Why it’s breaking down
To show one post I now:
1. Read the post → get the list of top‑level comment IDs.
2. Paginate that list.
3. For each comment, recursively fetch every reply to build a tree.
On a post with 10 comments where one branch is 13 levels deep this becomes dozens of round‑trips plus a lot of in‑route JSON stitching. Works with json‑server but will hammer a real DB in production.
What I’m after
A data‑model / query pattern that lets me fetch a comment tree (or at least the first N levels) in one cheap call instead of a cascade of recursive fetches.
Example in production (might take a minute to load, because json cold start): https://sheru.vercel.app/posts/1
Take a look at the network tab in dev inspector, and the sheer amount of API roud trips happening for a single comments section chain assembly.
Any and all suggestion apreciated.
