4 ms·

Solving the 1+N Query Problem

59 points by wheatBread 1mo ago

quibono 1mo ago
Nice, and I understand why using `getAuthorNames` solves the N+1 here. But... isn't this solving the problem by removing most of what makes it an issue in the first place? I imagine most people use ORMs for the SQL <-> native class data sync capability. And this assumes one would run the Acadia query instead. FWIW I'm not trying to be negative, it's just my general impression is that these N+1 usually occur because people _want_ direct object access and _want_ to write loops, and _want_ to access fields and have the underlying SQL be sorted by the ORM.
- cnity 1mo ago
  This is my experience too. The solution is to train people to stop wanting to solve data query problems in the application layer.
- lobofta 1mo ago
  As far as I understand Acadia gives you Acadia <-> Native class data sync, only just Haskell and Elm at the moment unfortunately. I'd be willing to rewrite queries in some other language that transpiles to SQL if it allows me to do all the queries I want and gives me full compile time type support for db access in return. The policies look interesting too by the way, but they don't solve a major IMO.
  - ryanrasti 1mo ago
    > I'd be willing to rewrite queries in some other language that transpiles to SQL if it allows me to do all the queries I want and gives me full compile time type support for db access in return. Full typed coverage for db is what I'm doing in Typegres [1] -- including all dialect built-in functions/operators. And regarding policy, instead of RLS it's all based on ocap: reachability is permission. So: `api.user.posts()` automatically injects a `where` clause on the `users` table and it's composable wherever a SQL set expression is allowed: `api.user.posts().join(...).groupBy(...)`. Since we're building up a SQL expression tree, we avoid the N+1 problem entirely. [1] https://typegres.com/ https://typegres.com/
- hobofan 1mo ago
  Yes, this does essentially nothing to solve the 1+N query problem. If your solution was to to stuff everything into one query, this was already possible with SQL!
red_admiral 1mo ago
This is what you get when you let your ORM loose on the database without understanding JOINs. Especially, the bit where something like 'book.author.name' that looks like a simple field dereference actually is a method call on an ORM proxy object (book), via python's __getattr__ or similar, that fires off a new query if the data you want is not loaded yet. Some ORMs let you specify the extent of the data that you want, like Hibernate has its own Hibernate Query Language. At some point you are better off just writing SQL yourself, though. Even without join problems, if you ask an ORM to get the person with user id 123 and all you want is their name, the ORM cannot know that unless to tell it, and so you end up with a 'SELECT *' type query.
- cnity 1mo ago
  ORMs are great. They make the easy queries remain easy and the harder queries impossible.
- tehlike 1mo ago
  This is true but not super true in case of linq and related providers like efcore. Even nhibernate linq would do this.
- seki285 1mo ago
  You should write a raw SQL query to grab just a user's name only when there's a need for that.
- ddorian43 1mo ago
  Or set lazy loadin to "raise" in the relationships and get exceptions if you dont explicitly join.
  - red_admiral 1mo ago
    Is usually the right answer!
- BeetleB 1mo ago
  Dealing with Django, they give quite a few ways to query data. For the small internal web site we ran, I've yet to encounter an N+1 situation that I didn't find an alternative Django API that I should have been using. Not saying you never need bare SQL on Django sites, but the Django ORM does have some sophisticated APIs to prevent this problem. > Even without join problems, if you ask an ORM to get the person with user id 123 and all you want is their name, the ORM cannot know that unless to tell it So ... tell it! You can specify in a query to fetch only certain data, and not the whole object.
mrkeen 1mo ago
Compare with the prior art of N+1 queries of 2014: https://github.com/facebook/Haxl/blob/main/example/sql/readme.md https://github.com/facebook/Haxl/blob/main/example/sql/readm...
jbverschoor 1mo ago
Wasn't this 'solved' by Hibernate ages ago?
- _1tan 1mo ago
  Heavy Hibernate user here, Hibernate does not make it impossible to write N+1 queries afaik - unless there are some tips we're missing in our team? We just regularly check our slow queries dashboard, fix it (or well let an LLM work on it) and then move on.
  - jbverschoor 1mo ago
    I recall hibernate, it maybe rails, as those are my most used tech, that it can detect n+1, and then just query the whole relation. Detecting is easy if you’re iterating over a proxy collection
kstrauser 1mo ago
Side note: I strongly prefer referring to this as the "1+N problem" as the author did here. I didn't understand what people were grousing about when they talked about "N+1". N+1: You're already doing N queries. Is adding 1 more that big of a deal? 1+N: This should have been 1 query, but somehow you blew it up into that one plus N more. I'd seen that query antipattern plenty of times and knew what it was bad, but didn't realize that's what people meant by "N+1", which I thought must mean something different.
- libria 1mo ago
  You're not the only one. I never stopped to delve into what this N+1 problem was b/c I assumed it was never an issue for me. All these years and this is the 1st time I've finally understood what they were saying. However, after going back and forth with LLM on it just now, I feel like "1+N" is just a coding mistake, not a perplexing multi-faceted, engineering problem to be solved. Experience or a slow application would teach you to find a better way to get that info and then you move on.
  - ambicapter 1mo ago
    > not a perplexing multi-faceted, engineering problem to be solved It's a common mistake, not a deep, interesting one.
  - rspeele 1mo ago
    It is just a coding mistake, except that fixing that mistake leaves you with clunkier abstractions. If you have Foos, and users have permissions that control what they can do to a Foo, you'd like to have a function `GetPermissions : (UserId, FooId) -> Async<Permissions>`. If users can frob Foos you'd like to have a `FrobFoo : (FooId) -> Async<void>` function. But as soon as you let users select multiple Foos, or god forbid, an entire folder containing Foos, and bulk-frob them now you have to write `FrobFoos : (List<FooId>) -> Async<void>`. And to avoid the implementation of that causing another 1+N checking permissions, you also need `GetPermissionsBulk : (UserId, List<FooId> -> Async<Dictionary<FooId, Permissions>>`. The singular forms of those functions, to avoid duplication, now become wrappers over the bulk forms. The logic becomes harder to trace in the rewritten, bulk forms of the functions, but they are efficient. Next the customer hits you with a request like "let's have a smart-frob function that works on all the selected foos. For foos that are red, it frobs them, if they are blue, it fizzles them". Now you have to bulk-load to select the redness or blueness of all your Foos, build two separate lists, red and blue, then call your bulk-frob and bulk-fizzle functions accordingly on the two lists. Again the machinery to turn the requirement into a batch-shaped thing is not a lot, but it does kind of obscure the original business requirement. At various times in the life of the project you will have a feature that starts as a "always done on one Foo" thing because it's triggered by a button on the detail screen. Then somebody will possibly come along and want to do it in bulk later and you have to rewrite the implementation. Unless you have very strict code review that everything MUST be written in batch-style taking a list of IDs up to the API layer. I wrote a library[1] many years ago to solve this problem and allow the straightforward, non-batch versions of the functions to be automatically batchable. The idea is kind of like what React did for frontend dev: React was not faster than mutating the page with jQuery soup, but it was much faster than replacing the entire DOM on every render, and it let you write your code as if that was what you were doing. That was a very simple mental model and much less buggy than jQuery soup. The idea of my library was basically borrowed from other functional languages with a resumption monad, meaning that instead of an opaque async task to go do a thing, you have a "plan" which could either be a. done or b. waiting on some errand that requires firing off a query. If you have a list of plans like from a loop, you could step all of them to the next errand they are waiting on, then fire those off in a batch. So plans could be composed linearly or "batch-style" depending on your preference[2]. What makes it very powerful is the combination with an F# type provider that could analyze your SQL and automatically determine a caching profile for each query. It knows what tables the query reads from, what tables it writes to, whether it uses any impure functions like random(), etc. So within one transaction, it wouldn't re-run the same pure query again, it would pull the results from a local cache -- except if another command issued in that transaction updates those tables, the cache is automatically invalidated. This solves the other code smell that starts to accumulate as you try to write efficient database code in a complex app -- keeping materialized objects loaded in memory and passing them around to other functions so they don't have to re-query for them. Anyway, it was a little too weird to catch on, and I was a little too burnt out to maintain it. [1]https://github.com/fsprojects/Rezoom.SQL https://github.com/fsprojects/Rezoom.SQL [2]https://fsprojects.github.io/Rezoom.SQL/doc/Rezoom/README.html https://fsprojects.github.io/Rezoom.SQL/doc/Rezoom/README.ht...
vilterp 1mo ago
> [Datalog] is a subset of Prolog that lacks recursion Datalog does allow for recursion — a common example is graph reachability: reachable(a, b) :- edge(a, b). reachable(a, c) :- edge(a, b), reachable(b, c). (Evan mentioned implementing kCFA, which would require recursion like this...) 'Base datalog' guarantees termination by requiring all input relations to be finite. Notably this means that it doesn't have numerical operations like addition or multiplication, since `plus(a, b)` or `times(a, b)` would be infinite relations. More practical Datalog engines like Souffle (https://souffle-lang.github.io/ https://souffle-lang.github.io/) have numerical operations but don't guarantee termination. Recursive queries are not needed by most applications, but maybe Acadia could allow them (compiling to recursive CTEs) by proving that recursion only goes through finite relations.
- cryptonector 1mo ago
  If you can do Peano numbers... Guaranteed termination isn't really if you give me enough rope to implement the Ackermann function.
  - debugnik 1mo ago
    Pure Datalog can't express peano up to infinity, its terms can't be functors as in Prolog. At best you could hardcode a successor relation up to a limit.
gigatexal 1mo ago
just write sql smh it's so easy to get proper queries and then the mapping from a list of tuples to your object is easy
koliber 1mo ago
This is largely a solved problem. New generations of programmers are simply rediscovering it. The solution: - be aware of it - add DB query monitoring via your favorite APM tool - review the APM tool regularly - when you see an N+1 issue apply one of the normal solutions. Follow this and N+1 query issues will disappear soon enough. If you can’t do this it means you chose an immature framework or tech stack and my advice is to consider starting from scratch. Otherwise you will need to re-live the mistakes many people have already solved before you which feels adventurous but is painful and dumb.
- 1-more 1mo ago
  I used to work somewhere that was really good at testing. We had `assert_no_n_plus_ones` in our rspec controller tests where we'd load a page of things with one thing on it and again with many things on it and assert that the number of DB queries was the same between both. The thing is, as a developer I want every dumb mistake I could make appear as a squiggle in my editor. The hierarchy for programming error reporting is something like: lawsuit, social media post, ticket filed by support, bug found by QA, failing browser driver regression test, failing controller test, failing UI unit test, failing linter bug (elm-review is incredible for lint with auto fixes), failing compile which is caught by my editor. Only the last two might not require me to write any code, and only the last one might not require me to even write any configuration. If the error is caught anywhere past QA, that's good and cool, no disagreement there. But if it's found before I could ever have to assert it's not there, I feel so much more secure that I haven't introduced it.
  - koliber 1mo ago
    I really like how you phrase this and completely agree. Issues should be caught as early as possible in the dev process and what you describe is probably as good as it gets. I tend to look at such problems from an organizational perspective and a good APM is a fail safe that compensates for other failures.
    - 1-more 1mo ago
      > a good APM is a fail safe that compensates for other failures. Absolutely. The ideal organization would adopt a tool to make squiggles when you do something silly AND have an APM that treats slow pages as bugs AND have a culture of triaging bugs among the teams and having all engineers take a ownership of how the app is working. Swiss cheese model. When I worked at the place with assert_no_n_plus_ones it was the closest thing to that ideal organization.
wood_spirit 1mo ago
Of course mainstream ORMs leave a lot of perf on the table. For example, I once patched the ORM in a struggling php web app that i had to help. I started as a logger profiler thingy but then had the crazy idea of being a trace optimiser. By recognising the call sites from previous visits I could spot the 1+N and select * etc and actually transcode that into better sql in the next run etc. Shockingly it made a massive difference and I was surprised that normal ORMs aren’t doing that kind of thing.
- stephen 1mo ago
  > trace optimizer/better sql in the next run Ah wow! I admittedly already linked this PR in another reply, but I'm trying similar things here: https://github.com/joist-orm/joist-orm/pull/1967 https://github.com/joist-orm/joist-orm/pull/1967 Neat to hear you had success with it before; did you have to handle "the optimization was inaccurate [a novel codepath asked for a column we didn't return], so fallback to `select *`"? And do that without failing the overall request? This "fallback and implicit retry" is what I'm doing atm, and just assuming is the only way of handling the "novel codepath was hit this time" problem, but lmk if I'm missing something.
deleted 1mo ago
[deleted]
hmnxr1e 1mo ago
Non-identity principle. A=A
WilcoKruijer 1mo ago
I really believe that every engineer writing queries (even SQL) should read the FoundationDB data modeling guide [0]. It really gives an appreciation of what smart choice of primary key can do to query efficiency. With some de-normalization, joins aren’t even needed for performance. Postgres has supported query pipelining for a long time. In my opinion, most queries should be written in such a way that sequential queries don’t have any data dependencies on the previous query at all. This speeds up applications by huge amounts. [0] https://apple.github.io/foundationdb/data-modeling.html https://apple.github.io/foundationdb/data-modeling.html
- NorthSouthNorth 1mo ago
  lol I remember a senior dev absolutely shitting on me for suggesting de-normalization to improve a JOIN query that was absolutely way too slow (on a table that was reseeded on deploys at that). I left it at that but to this day I maintain that it was the right move.
stephen 1mo ago
Afaict their solution is "here's a prolog-ish query DSL that safely translates FP-ish code to joins". That seems fine, but imo 1+Ns usually happen when you interleave business logic with database loads--like you have business logic that "really wants to be in a loop" b/c it's "not easily expressed in SQL" logic. So, the author's ~3 lines of "a loop with zero business logic" is not that convincing, and seems like a premature claim to "solving 1+Ns"? Like maybe if you can express truly generic business logic, and somehow that is translated into "evaled on the database-side" SQL? (Disclaimer, I work on an ORM that does let you interleave business logic & database loads, and still avoids 1+Ns: https://joist-orm.io/goals/avoiding-n-plus-1s/ https://joist-orm.io/goals/avoiding-n-plus-1s/)

59 points by wheatBread 1mo ago

Nice, and I understand why using `getAuthorNames` solves the N+1 here. But... isn't this solving the problem by removing most of what makes it an issue in the first place? I imagine most people use ORMs for the SQL <-> native class data sync capability. And this assumes one would run the Acadia query instead. FWIW I'm not trying to be negative, it's just my general impression is that these N+1 usually occur because people _want_ direct object access and _want_ to write loops, and _want_ to access fields and have the underlying SQL be sorted by the ORM.

cnity 1mo ago
This is my experience too. The solution is to train people to stop wanting to solve data query problems in the application layer.
lobofta 1mo ago
As far as I understand Acadia gives you Acadia <-> Native class data sync, only just Haskell and Elm at the moment unfortunately. I'd be willing to rewrite queries in some other language that transpiles to SQL if it allows me to do all the queries I want and gives me full compile time type support for db access in return. The policies look interesting too by the way, but they don't solve a major IMO.
- ryanrasti 1mo ago
  > I'd be willing to rewrite queries in some other language that transpiles to SQL if it allows me to do all the queries I want and gives me full compile time type support for db access in return. Full typed coverage for db is what I'm doing in Typegres [1] -- including all dialect built-in functions/operators. And regarding policy, instead of RLS it's all based on ocap: reachability is permission. So: `api.user.posts()` automatically injects a `where` clause on the `users` table and it's composable wherever a SQL set expression is allowed: `api.user.posts().join(...).groupBy(...)`. Since we're building up a SQL expression tree, we avoid the N+1 problem entirely. [1] https://typegres.com/ https://typegres.com/
hobofan 1mo ago
Yes, this does essentially nothing to solve the 1+N query problem. If your solution was to to stuff everything into one query, this was already possible with SQL!

red_admiral 1mo ago

This is what you get when you let your ORM loose on the database without understanding JOINs. Especially, the bit where something like 'book.author.name' that looks like a simple field dereference actually is a method call on an ORM proxy object (book), via python's __getattr__ or similar, that fires off a new query if the data you want is not loaded yet. Some ORMs let you specify the extent of the data that you want, like Hibernate has its own Hibernate Query Language. At some point you are better off just writing SQL yourself, though. Even without join problems, if you ask an ORM to get the person with user id 123 and all you want is their name, the ORM cannot know that unless to tell it, and so you end up with a 'SELECT *' type query.

cnity 1mo ago
ORMs are great. They make the easy queries remain easy and the harder queries impossible.
tehlike 1mo ago
This is true but not super true in case of linq and related providers like efcore. Even nhibernate linq would do this.
seki285 1mo ago
You should write a raw SQL query to grab just a user's name only when there's a need for that.
ddorian43 1mo ago
Or set lazy loadin to "raise" in the relationships and get exceptions if you dont explicitly join.
- red_admiral 1mo ago
  Is usually the right answer!
BeetleB 1mo ago
Dealing with Django, they give quite a few ways to query data. For the small internal web site we ran, I've yet to encounter an N+1 situation that I didn't find an alternative Django API that I should have been using. Not saying you never need bare SQL on Django sites, but the Django ORM does have some sophisticated APIs to prevent this problem. > Even without join problems, if you ask an ORM to get the person with user id 123 and all you want is their name, the ORM cannot know that unless to tell it So ... tell it! You can specify in a query to fetch only certain data, and not the whole object.

mrkeen 1mo ago

Compare with the prior art of N+1 queries of 2014: https://github.com/facebook/Haxl/blob/main/example/sql/readme.md https://github.com/facebook/Haxl/blob/main/example/sql/readm...

jbverschoor 1mo ago

Wasn't this 'solved' by Hibernate ages ago?

_1tan 1mo ago
Heavy Hibernate user here, Hibernate does not make it impossible to write N+1 queries afaik - unless there are some tips we're missing in our team? We just regularly check our slow queries dashboard, fix it (or well let an LLM work on it) and then move on.
- jbverschoor 1mo ago
  I recall hibernate, it maybe rails, as those are my most used tech, that it can detect n+1, and then just query the whole relation. Detecting is easy if you’re iterating over a proxy collection

kstrauser 1mo ago

Side note: I strongly prefer referring to this as the "1+N problem" as the author did here. I didn't understand what people were grousing about when they talked about "N+1". N+1: You're already doing N queries. Is adding 1 more that big of a deal? 1+N: This should have been 1 query, but somehow you blew it up into that one plus N more. I'd seen that query antipattern plenty of times and knew what it was bad, but didn't realize that's what people meant by "N+1", which I thought must mean something different.

libria 1mo ago
You're not the only one. I never stopped to delve into what this N+1 problem was b/c I assumed it was never an issue for me. All these years and this is the 1st time I've finally understood what they were saying. However, after going back and forth with LLM on it just now, I feel like "1+N" is just a coding mistake, not a perplexing multi-faceted, engineering problem to be solved. Experience or a slow application would teach you to find a better way to get that info and then you move on.
- ambicapter 1mo ago
  > not a perplexing multi-faceted, engineering problem to be solved It's a common mistake, not a deep, interesting one.
- rspeele 1mo ago
  It is just a coding mistake, except that fixing that mistake leaves you with clunkier abstractions. If you have Foos, and users have permissions that control what they can do to a Foo, you'd like to have a function `GetPermissions : (UserId, FooId) -> Async<Permissions>`. If users can frob Foos you'd like to have a `FrobFoo : (FooId) -> Async<void>` function. But as soon as you let users select multiple Foos, or god forbid, an entire folder containing Foos, and bulk-frob them now you have to write `FrobFoos : (List<FooId>) -> Async<void>`. And to avoid the implementation of that causing another 1+N checking permissions, you also need `GetPermissionsBulk : (UserId, List<FooId> -> Async<Dictionary<FooId, Permissions>>`. The singular forms of those functions, to avoid duplication, now become wrappers over the bulk forms. The logic becomes harder to trace in the rewritten, bulk forms of the functions, but they are efficient. Next the customer hits you with a request like "let's have a smart-frob function that works on all the selected foos. For foos that are red, it frobs them, if they are blue, it fizzles them". Now you have to bulk-load to select the redness or blueness of all your Foos, build two separate lists, red and blue, then call your bulk-frob and bulk-fizzle functions accordingly on the two lists. Again the machinery to turn the requirement into a batch-shaped thing is not a lot, but it does kind of obscure the original business requirement. At various times in the life of the project you will have a feature that starts as a "always done on one Foo" thing because it's triggered by a button on the detail screen. Then somebody will possibly come along and want to do it in bulk later and you have to rewrite the implementation. Unless you have very strict code review that everything MUST be written in batch-style taking a list of IDs up to the API layer. I wrote a library[1] many years ago to solve this problem and allow the straightforward, non-batch versions of the functions to be automatically batchable. The idea is kind of like what React did for frontend dev: React was not faster than mutating the page with jQuery soup, but it was much faster than replacing the entire DOM on every render, and it let you write your code as if that was what you were doing. That was a very simple mental model and much less buggy than jQuery soup. The idea of my library was basically borrowed from other functional languages with a resumption monad, meaning that instead of an opaque async task to go do a thing, you have a "plan" which could either be a. done or b. waiting on some errand that requires firing off a query. If you have a list of plans like from a loop, you could step all of them to the next errand they are waiting on, then fire those off in a batch. So plans could be composed linearly or "batch-style" depending on your preference[2]. What makes it very powerful is the combination with an F# type provider that could analyze your SQL and automatically determine a caching profile for each query. It knows what tables the query reads from, what tables it writes to, whether it uses any impure functions like random(), etc. So within one transaction, it wouldn't re-run the same pure query again, it would pull the results from a local cache -- except if another command issued in that transaction updates those tables, the cache is automatically invalidated. This solves the other code smell that starts to accumulate as you try to write efficient database code in a complex app -- keeping materialized objects loaded in memory and passing them around to other functions so they don't have to re-query for them. Anyway, it was a little too weird to catch on, and I was a little too burnt out to maintain it. [1]https://github.com/fsprojects/Rezoom.SQL https://github.com/fsprojects/Rezoom.SQL [2]https://fsprojects.github.io/Rezoom.SQL/doc/Rezoom/README.html https://fsprojects.github.io/Rezoom.SQL/doc/Rezoom/README.ht...

vilterp 1mo ago

> [Datalog] is a subset of Prolog that lacks recursion Datalog does allow for recursion — a common example is graph reachability: reachable(a, b) :- edge(a, b). reachable(a, c) :- edge(a, b), reachable(b, c). (Evan mentioned implementing kCFA, which would require recursion like this...) 'Base datalog' guarantees termination by requiring all input relations to be finite. Notably this means that it doesn't have numerical operations like addition or multiplication, since `plus(a, b)` or `times(a, b)` would be infinite relations. More practical Datalog engines like Souffle (https://souffle-lang.github.io/ https://souffle-lang.github.io/) have numerical operations but don't guarantee termination. Recursive queries are not needed by most applications, but maybe Acadia could allow them (compiling to recursive CTEs) by proving that recursion only goes through finite relations.

cryptonector 1mo ago
If you can do Peano numbers... Guaranteed termination isn't really if you give me enough rope to implement the Ackermann function.
- debugnik 1mo ago
  Pure Datalog can't express peano up to infinity, its terms can't be functors as in Prolog. At best you could hardcode a successor relation up to a limit.

gigatexal 1mo ago

just write sql smh it's so easy to get proper queries and then the mapping from a list of tuples to your object is easy

koliber 1mo ago

This is largely a solved problem. New generations of programmers are simply rediscovering it. The solution: - be aware of it - add DB query monitoring via your favorite APM tool - review the APM tool regularly - when you see an N+1 issue apply one of the normal solutions. Follow this and N+1 query issues will disappear soon enough. If you can’t do this it means you chose an immature framework or tech stack and my advice is to consider starting from scratch. Otherwise you will need to re-live the mistakes many people have already solved before you which feels adventurous but is painful and dumb.

1-more 1mo ago
I used to work somewhere that was really good at testing. We had `assert_no_n_plus_ones` in our rspec controller tests where we'd load a page of things with one thing on it and again with many things on it and assert that the number of DB queries was the same between both. The thing is, as a developer I want every dumb mistake I could make appear as a squiggle in my editor. The hierarchy for programming error reporting is something like: lawsuit, social media post, ticket filed by support, bug found by QA, failing browser driver regression test, failing controller test, failing UI unit test, failing linter bug (elm-review is incredible for lint with auto fixes), failing compile which is caught by my editor. Only the last two might not require me to write any code, and only the last one might not require me to even write any configuration. If the error is caught anywhere past QA, that's good and cool, no disagreement there. But if it's found before I could ever have to assert it's not there, I feel so much more secure that I haven't introduced it.
- koliber 1mo ago
  I really like how you phrase this and completely agree. Issues should be caught as early as possible in the dev process and what you describe is probably as good as it gets. I tend to look at such problems from an organizational perspective and a good APM is a fail safe that compensates for other failures.
  - 1-more 1mo ago
    > a good APM is a fail safe that compensates for other failures. Absolutely. The ideal organization would adopt a tool to make squiggles when you do something silly AND have an APM that treats slow pages as bugs AND have a culture of triaging bugs among the teams and having all engineers take a ownership of how the app is working. Swiss cheese model. When I worked at the place with assert_no_n_plus_ones it was the closest thing to that ideal organization.

wood_spirit 1mo ago

Of course mainstream ORMs leave a lot of perf on the table. For example, I once patched the ORM in a struggling php web app that i had to help. I started as a logger profiler thingy but then had the crazy idea of being a trace optimiser. By recognising the call sites from previous visits I could spot the 1+N and select * etc and actually transcode that into better sql in the next run etc. Shockingly it made a massive difference and I was surprised that normal ORMs aren’t doing that kind of thing.

stephen 1mo ago
> trace optimizer/better sql in the next run Ah wow! I admittedly already linked this PR in another reply, but I'm trying similar things here: https://github.com/joist-orm/joist-orm/pull/1967 https://github.com/joist-orm/joist-orm/pull/1967 Neat to hear you had success with it before; did you have to handle "the optimization was inaccurate [a novel codepath asked for a column we didn't return], so fallback to `select *`"? And do that without failing the overall request? This "fallback and implicit retry" is what I'm doing atm, and just assuming is the only way of handling the "novel codepath was hit this time" problem, but lmk if I'm missing something.

deleted 1mo ago

[deleted]

hmnxr1e 1mo ago

Non-identity principle. A=A

WilcoKruijer 1mo ago

I really believe that every engineer writing queries (even SQL) should read the FoundationDB data modeling guide [0]. It really gives an appreciation of what smart choice of primary key can do to query efficiency. With some de-normalization, joins aren’t even needed for performance. Postgres has supported query pipelining for a long time. In my opinion, most queries should be written in such a way that sequential queries don’t have any data dependencies on the previous query at all. This speeds up applications by huge amounts. [0] https://apple.github.io/foundationdb/data-modeling.html https://apple.github.io/foundationdb/data-modeling.html

NorthSouthNorth 1mo ago
lol I remember a senior dev absolutely shitting on me for suggesting de-normalization to improve a JOIN query that was absolutely way too slow (on a table that was reseeded on deploys at that). I left it at that but to this day I maintain that it was the right move.

stephen 1mo ago

Afaict their solution is "here's a prolog-ish query DSL that safely translates FP-ish code to joins". That seems fine, but imo 1+Ns usually happen when you interleave business logic with database loads--like you have business logic that "really wants to be in a loop" b/c it's "not easily expressed in SQL" logic. So, the author's ~3 lines of "a loop with zero business logic" is not that convincing, and seems like a premature claim to "solving 1+Ns"? Like maybe if you can express truly generic business logic, and somehow that is translated into "evaled on the database-side" SQL? (Disclaimer, I work on an ORM that does let you interleave business logic & database loads, and still avoids 1+Ns: https://joist-orm.io/goals/avoiding-n-plus-1s/ https://joist-orm.io/goals/avoiding-n-plus-1s/)