Hacker News Reader

Comment by estebarb | Hacker News Reader

The joins should be done inside the DB, not in an external server. Any SQL DB should be able to do that.

If this is not done carefully, it can also be slow in some situations, even with the right indeces in place. Lets say we have the following slightly contrieved case:

organizaiton(id, name, description) -> users(id, name) -> posts(id, content) -> comments(id, userId, content)

We want to get one organization with all its users. Lets imagine our org has 100 users, each of which has 100 posts on average, each of which has 100 comments on average. In a naive, flat join, we'll be repeating the organization description column 1 million times, and each post's content about 100 times on average.

This can still be problematic without joins, as sending 1 million comment IDs in a query to the DB isn't going to work, so even the multiple queries version will need to do better than that

Still not a reason to not leverage the database. Use something like Postgres' ARRAY_AGG; reduces the duplicates, faster and better than rolling your own JOINs in your app.

The drizzle example I linked to is a realistic example into what would actually be involved in using JSON aggregation in the datbase in PG. Its painful to write manually.

I wish postgres had FOR JSON (https://learn.microsoft.com/en-us/sql/relational-databases/j...)

That this has to be said is scary.

Reply on news.ycombinator.com