sbuttgereit

sbuttgereit

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.

2
Post #11
sodapopcan

sodapopcan

Ya, that’s the crux of it. You have to benchmark. Although I mostly don’t worry about it I’m just doing a couple of queries in a page.

In a particularly egregious case at my old job, there was a very hairy query with I think 9 joins. Converting all of those into separate queries took the total time down from over 30 seconds to under 1 second. Now, most of that 30 seconds was on network and Elixir, not in the db. But the db was returning a massive denormalize table which had to be sent over the network then Elixir had to load into memory and reduce.

But ya again, there is no rule of thumb. To dig into the case of Posts+Replies more, if you are going to be paginating replies in a LiveView, then it’s wasteful to preload them. If your only way of loading replies is through get_post this means you have to refetch the post each time you fetch a new page of replies. Not to mention you’ve now complicated your getter even further by having to add a :replies_page option.

2
Post #12
adw632

adw632

It should be possible with Postgres json functions to return data in a nested shape rather than fanned out with data repitition. Functions like json_build_object(), json_agg() and row_to_json() stand out.

I haven’t looked into it, but may be something to explore and I just googled and found a 2015 post that does this for avoiding 1+N queries.

Postgres documentation here:

0
Post #13
D4no0

D4no0

It is, however you are also shifting a part of your functionality and business-logic to the database. Improvement, debugging and overall knowledge required to understand what is going on becomes higher.

0
Post #14
sbuttgereit

sbuttgereit

It very much is possible and I’ve used it with success. About 10 years ago, we wanted to take structured data pretty much directly from the database and send it to the web client to render; the data was quite complex. So we had an admittedly overcomplicated query which would build this single, large JSON object when the source data changed (not too frequently) and cached it in a column (there was other memory caching as well, but the database was done at that point). Overall it worked well. And over time it worked better as this was when the JSON handling features were just starting to be added.

0
Post #15
dimitarvp

dimitarvp

You’re not wrong but from one scale and on it is very much not only worth it but also required. I’ve had a CTO step in to measure 5 different ways of slicing data from the SQL database and finally choose the one that was indeed closest to the DB because we were suffering loss of customers due to pages needing 5+ seconds to load. He made sure to thoroughly document his SQL + Ecto acrobatics, to the point the comments were 4x bigger than the query itself but IMO it was very worth it; our response times dropped to 0.6s - 0.9s only because of that (and we then reduced them further with other optimizations).

2
Post #16
adw632

adw632

Pushing data query concerns to the DB layer is almost always the best bet. I prefer systems that use stored procedures and views to simplify the app, over ones where the app cobbles the query together. Using stored procedures is also far more secure and not susceptible to SQL injection attacks.

In this instance I would argue that representing query response as json is nothinh to do with business logic, it is about efficient data transfer as there is no business domain logic involved here, any time you have a join you represent the result as json, any right side row is represented as json and aggregated. We are dealing with on the wire data representation, (not business domain logic) just like Phoenix does with Liveview.

1
Post #17
D4no0

D4no0

I’ve heard about this, even though I think that in some way this is an anti-pattern, as you will slowly start moving more and more logic to stored procedures. When performance is critical and there is no other way, I totally understand that. You are also limited in development and debugging tools compared to a full-fledged language (at least this is what it felt to me), not to mention that there is specific knowledge involved about potential dangers and limitations.

Correct me if I’m wrong, however in a modern library like Ecto, how you would do a SQL injection? IMO injections are just a sign of bad software design, where data is not clearly separated from execution.

3
Post #18
dimitarvp

dimitarvp

Nah, I wouldn’t go so far. It’s not sustainable to have one SQL wizard per team, and they become a bottleneck too (and often command insane salaries).

Elixir is not amazing when it comes to having to chew through thousands of records per second coming from the database, but it’s also doing much better compared to every other dynamic language, except JS, which is in the same or slightly better ballpark of performance.

Throw in the mix languages like Golang and Rust – f.ex. for microservices – and processing of stuff coming from the DB is almost a non-issue.

1
Post #19
adw632

adw632

Ecto uses parameterized queries so is very safe unless one is careless using fragments.

In mixed environments this is still an issue, and I tend to be conservative and think in multiple layers of defense when it comes to these things. It is my pessimism from 30+ years of working in infosec and cyber security.

Where I typically focus use of stored procedures for specific use cases, which are typically complex updates and complex queries such as RCTE’s, or where it is an important thing to get right, where many tables may be involved but I want a logical action or where I want to insulate the application from complex schema or db schema churn.

For example I use this graph querying in Postgres approach which makes you wonder why things like Neo4j ever had a reason to exist. But I don’t want application layer solving the graph querying logic so it makes sense to be in the database.

1
Post #20