3 ms·
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
by spion 3y ago
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
- RadiozRadioz 3y agoStill 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.
- spion 3y agoThe 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/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/j...)