This is true in the broader context. When considering just the database, the naïve rule of thumb is to ask your question once if you can: ideally, this means a single query which gives the database query planner the most information it can act on to most efficiently retrieve the information you want (so, from the Ecto perspective, do JOINs as needed). But as you point out, more broadly, this isn’t always the most efficient. from the application point of view.
The bigger trade-off we’re dealing with, end-to-end, is really between bandwidth and latency. A JOIN query, like a common header/detail query that includes both header data and detail data, will duplicate the header data coming from the database… and in application we only need the header data once. So our bandwidth gets consumed transferring duplicated data which we’re just going to discard at the application. BUT… when we’re doing a preload, without JOINs, we’re producing multiple queries in the application: this means multiple round trips from the application to the database, the database often is increasing its planning time because it’s initializing and planning multiple queries (PostgreSQL anyway), and we may lose the benefits of the query planner being able to more efficiently retrieve the complete dataset. These issues all contribute to increased latency in getting results you can use.
Ultimately there is an inflection point where the latency penalties from running multiple queries become smaller than the time it takes to transfer extraneous data from a single JOINed query. Where that point sits depends heavily on the exact query and the specific environmental conditions (mostly network, but DB server capability as well) where the application is running. I don’t have a great one-size-fits-all rule of thumb beyond just saying understand the principals just discussed (and maybe a little more), start with the method that feels most comfortable to code in the application, and address performance issues case by case when they make themselves known. For myself, I’ll have hand-wavy, intuition for where the breakpoint is and code accordingly; I expect I’m only marginally better than the just do it one way and deal with issues approach. If you’re going to err, err on the side of preloading since I’d expect the latencies to be small over more kinds of queries.
