Source code and results are available, for JS driver and for native driver. First I populated the database with two tables, posts and comments, where each comment has a related post. I generated 10000 posts, each with 100 comments. I tested these queries.
My insights from the benchmark are:
- Doing a traditional JOIN without aggregation is really slow. I attribute this to the fact that traditional join repeats all column values in the main table. So for 100 comments, related post’s row is repeated 100 times. Doing aggregation of comments into an array seems to really improve things. MongoDB’s idea of embedding is really powerful and you can see benefits even here.
- Subquery is faster than JOIN. A surprise because the word is that subqueries can be at best as fast as joins, but sometimes they will be worse. Here, there are better.
- It seems that the best approach is to use subqueries to get related documents, aggregate them into an array, and convert the array to JSON. Or, you can be lazy and just simply always convert the whole result (with aggregated arrays) to JSON.
Conclusion. If you are using PostgreSQL fields which can be reasonably converted to JSON and you are using node.js, it seems you should simply always convert results to JSON before sending them over to the client. If you want to fetch related documents, do not do JOIN but do a subquery and aggregate results into an array. If you are always converting results to JSON, then this is it. If not, then at least convert that aggregated array to JSON.