38 ms·

Ban 1+N in Django

175 points by Suor 4y ago

est 4y ago
This raises a question, are there any easy batching library in Python? It's like N+1 problem but for non-SQL. I have various RPCs, some of them accept multi actions with certain limits so how to write non-batch calls in code but execute them as a whole as needed?
davedx 4y ago
In our services I implemented a per request query counter that gently warns you if you exceed a max query count. Use case: identifying when it might make sense to use DataLoader. Note: doing this isn’t always worth it.
- fbdab103 4y ago
  I like this idea. I would just log the query count at the end of each request without any pre-defined limits. IFF you see performance drop, then you can always investigate and see if anything is triggering greater than expected calls. Nifty idea, and probably not too hard to implement.
gus_massa 4y ago
There is a problem with the URL. I think this is the correct one https://suor.github.io/blog/2023/03/26/ban-1-plus-n-in-django/ https://suor.github.io/blog/2023/03/26/ban-1-plus-n-in-djang...
- Suor 4y ago
  Thanks
- Suor 4y ago
  HN keeps automatically replacing the URL. It used to be a redirect before, but not anymore
  - gus_massa 4y ago
    HN has a feature that uses the canonical address in the webpage. Is your page configured correctly? Looking at the source: <link rel="canonical" href="http://hackflow.com/blog/2023/03/26/ban-1-plus-n-in-django"> Don't repost it again (for now). Try fixing the canonical link, and send an email to the mods hn@ycombinator.com with a short explanation and a link to this post https://news.ycombinator.com/item?id=35313565 https://news.ycombinator.com/item?id=35313565 to save them a few minutes searching. They may fix it using admin magic or ask you to repost again once the problem is fixed.
  - dang 4y ago
    As gus_massa pointed out, HN's software uses canonical URLs when it finds them. The canonical URL on the page you submitted was http://hackflow.com/blog/2023/03/26/ban-1-plus-n-in-django http://hackflow.com/blog/2023/03/26/ban-1-plus-n-in-django, so our software used that. I've fixed it above now.
- deleted 4y ago
  [deleted]
nickjj 4y ago
There is a case where having N+1 queries are beneficial. In Rails terms, it's when you perform Russian doll caching, but you can do this in any framework. The idea is you can cache a specific X thing which might make a query to an associated Y thing. A textbook N+1 query case (ie. a list of posts (X) that get the author's name (Y)). If you render the view without any cache with 10 things then you'd perform 20 queries but after the cache is warm you'd perform 0 queries. If item 5's Y gets updated then you only need to bust the cache for item 5 and query only item 5's Y association. Performing a preloaded query to get all X 10 things with their Y associated things could be an expensive query.
- fiddlerwoaroof 4y ago
  The downside here is a potential thundering herd issue if you’re forced to clear the cache.
  - ilyt 4y ago
    cache with grace period ("serve old record while new is updating") is good solution here
    - tatersolid 4y ago
      That doesn’t help on process/container/VM restarts.
- zdragnar 4y ago
  That's great if you can fit a lot of your database in your server's memory, but seems like a terrible headache once you get a decent number of users. Personally, I'd much rather have sane queries in the first place, but rails isn't really my cup of tea either, so take my opinion with a large pinch of salt if you do.
  - Kamq 4y ago
    > That's great if you can fit a lot of your database in your server's memory, but seems like a terrible headache once you get a decent number of users. You'd surely care about getting a significant chunk of your usage in server memory rather than what percentage of total data that is, no? To take the site we're on as an example, I'd be willing to bet the 30 things on the front page have one or two orders of magnitude more traffic than anything else (and probably a few more orders of magnitude more than the median post).
jonatron 4y ago
See also django-zen-queries https://github.com/dabapps/django-zen-queries https://github.com/dabapps/django-zen-queries , which can make it impossible for changes to a template to trigger queries.
- rlawson 4y ago
  Came here to post just that. Really like zen queries
- stavros 4y ago
  I like Zen queries, but sometimes you just have to make queries in templates (e.g. when you want to check the user on the request object), or sometimes it's just convenient and there's nothing wrong with it (e.g. when you want to check the user on the request object). Zen queries makes that use case impossible, sadly.
  - jonatron 4y ago
    I don't actually use Zen queries myself, but it does have a queries_disabled template tag, which would allow for more specific control.
lr4444lr 4y ago
I don't disagree with the author in principle, but I find once the data gets big enough where it makes a difference, I've already shifted to using ".values()" to avoid the overhead of model creation, and the KeyErrors that will throw if I leave the query lazy is tantamount to the solution he describes.
- Suor 4y ago
  I usually use `.values()` and `.values_list()` only when model creation is really slow. Usually many rows fetched.
- sgt 4y ago
  Same, and using raw queries too when needed. Identify hot spots and do those. Regular ORM is fine for the rest (mostly).
deleted 4y ago
[deleted]
spapas82 4y ago
This is very useful! I'm gonna start integrating it with my projects. However having a way to allow/not allow n+1 queries (like a context manager) would be much better. The thing is that there are times where n+1 isn't a big problem and fixing it would be a form of premature optimisation. I'd prefer to be in control and decide if I care about the n+1 query situation or not for some specific view.
- Suor 4y ago
  Should be easy enough to implement. You only need a context manager that adds 1 to some threadlocal flag on enter and subrracts on exit then check this flag in the monkey patch. Not sure how costly that will be though.
  - Suor 4y ago
    Here is an example of such thing https://github.com/Suor/django-cacheops/blob/8b3a79de29b2545179dcb591bf5d136c07ead4a2/cacheops/invalidation.py#L84-L101 https://github.com/Suor/django-cacheops/blob/8b3a79de29b2545...
- selcuka 4y ago
  It's not ideal, but you can simply log `DeferredAttribute.__get__` calls instead of raising an exception.
- tantalor 4y ago
  Just keep a list of exemptions in a file
- squeaky-clean 4y ago
  You could probably implement in in a context manager, have the `__enter__()` method execute the `_DA_get_original, DeferredAttribute.__get__ = DeferredAttribute.__get__, _DeferredAttribute_get` code and have the `__exit__` method undo that re-assignment. (Or maybe the reverse is better. Ban N+1 by default and the context manager `__enter__` puts back the original assignment, `__exit__` brings back the banned version).
- 4y ago
binarymax 4y ago
This is why I always advocated against ORMs. It’s so easy to fall into traps like this without even knowing it, and while you can work around it in some ORMs it is not obvious. Writing SQL is not that hard, and mapping the results to a type isn’t that hard either. So with an ORM you might end up saving several hours of work up front for lots of pain later.
- mattbillenstein 4y ago
  You're on the right side of the bell-curve meme my friend, but there are a lot more people in the thick-framework camp who spend their days getting lost in the complexity of ORMs and related tech...
  - capableweb 4y ago
    It's more like the world is not black and white, engineering problems don't have "The perfect solution, the rest is trash", but rather "this problem has multiple solutions, depending on context, some have these tradeoffs and the others have these". In this particular case, it might not be worth to trade speed of having to think about SQL for performance (today or tomorrow). Maybe you're building something that will just be used by 2-3 people, so 1+N isn't really a issue. Or whatever, the conclusion as always is: it depends.
    - mattbillenstein 4y ago
      I agree it depends, but my hot take is almost universally the time people like to say ORMs save them they end up paying back in spades debugging them. Learn SQL!
  - cellularmitosis 4y ago
    I really think it is more a matter of exposure and familiarity than bell-curve positioning. If any backend engineer with 1 year of ORM experience had spent that year instead becoming familiar with SQL, the speed bump would be practically nil.
- 4y ago
Dachande663 4y ago
Laravel has had a similar feature for a while[0]. It’s been useful to enable this in development but only warn in production to prevent breaking things unexpectedly. [0] https://laravel.com/docs/10.x/eloquent-relationships#preventing-lazy-loading https://laravel.com/docs/10.x/eloquent-relationships#prevent...
btown 4y ago
My Chaotic Good take on this: one could implement a qs.auto_fetch_deferred() that emits model instances with weak back-references to a WeakSet of all instances emitted, and on a deferred get on ANY instance, it prefetches that attribute onto ALL of the instances... so that it doesn't just complain, but actually fixes your 1+N issue. But here lies absolute madness...
- readertime 4y ago
  I agree; Django (and many ORMs) have taken the opinion that if you load N instances of a model, and then you load a related field on 1 of them, you likely only want the 1 related field. If however they assumed that loading a related field implies you likely want all related fields, perhaps there’d be fewer instances of foot-gunning.
- Suor 4y ago
  Actually there is such thing already https://pypi.org/project/django-auto-prefetch/ https://pypi.org/project/django-auto-prefetch/ Not a monkey patch though, so will only work on the models you inherit from this.
paulddraper 4y ago
In my experience, the far more pernicious way to get N+1 is serializers (Django REST)
- Rapzid 4y ago
  Huh, ran into same issue on a rails project; queries kicked off in inside serializes were a weed. We toyed with throwing errors if a query was made during serialization. On top of that, everything about serialization is terribly slow in Rails :|
- Suor 4y ago
  GraphQL makes it even more fun :)
- Maxion 4y ago
  Ugh yes, nested serializers in DRF are always fun. Almost makes you want to go noSQL.
- jorl17 4y ago
  A couple of years ago I wrote a set of utility classes (which inherit from Serializer and ViewSet) which "solve" this problem by inspecting serializers at the beginning of the request and figuring out what to pass to `select_related` and `prefetch_related` "automatically". It supports nested serializers, N-N, etc. Also lets you "help it" by saying "assume attribute X of a serializer accesses fields X,Y,Z of its object", for more "sophisticated" cases. It's a very messy piece of code but it has survived many projects since I first wrote it 6 years ago. The day I enabled it at a previous job, we reduced a page load from 20s to 800ms or so just with it.
Waterluvian 4y ago
On the topic of accidentally doing lots of extra queries, I love using Model.objects.raw to control exactly how it’s retrieving model data. I love how it keeps the results inside the Model realm but gives you careful control. I also like that if you access fields you didn’t ask for, it’ll go get them. But I wish it screamed louder when this happened. “Your logic works but we had to do extra queries. This might be an error!” So the featured article is incredibly valuable. This ought to be built-in. Django Silk has been critical for discovering these cases. They’re too easy to do. I love the middle ground of “ORM but you write the SQL.”
code_biologist 4y ago
On an unrelated note, Python folks should check out OP's library funcy [1]: "A collection of fancy functional tools focused on practicality. Inspired by clojure, underscore and my own abstractions." Thanks for the library Suor! I've used it happily for many years! [1] https://github.com/Suor/funcy https://github.com/Suor/funcy
gleb 4y ago
Elixir/Phoenix does this by default. It is a good default.
rowanseymour 4y ago
I would love to see this become something that can be toggled as a setting in core. The only time it's useful is opening up a shell to debug. The rest of the time, it's just making it really hard to find missing prefetches or `.only()` and `.defer()` calls that are too limited. It's never a good feature for a production site running at any kind of scale.
irjustin 4y ago
Rails has Bullet[0] to help identify and warn you against N+1 Does Django have anything active? Quick search revealed nplusone[1] but its been dead since 2018. [0] https://github.com/flyerhzm/bullet https://github.com/flyerhzm/bullet [1] https://github.com/jmcarp/nplusone https://github.com/jmcarp/nplusone
- squeaky-clean 4y ago
  The second, longer snippet from the OP article seems to do basically this. To modify it to be exactly like what bullet describes (warning you when in debug or test mode, instead of raising an exception, and have no effect in prod) you can replace line 31 with the logger.error() from line 33, and delete the else-case (lines 32 & 33)
  - irjustin 4y ago
    It's not a risk I'm willing to take nor do I believe other general Django engs should take. It's fine if it's a pet project or if you've got deep knowledge of Django's inner workings. This requires understanding the affects of monkey patching core __get__ on DeferredAttribute. When reading the code vs core Django, it doesn't faithfully reproduce the normal case missing _check_parent_chain[0]. I'm not sure if that code path is supposed to be left out or of it's simply missing? The documentation of the code snippet doesn't explicitly state either. Code snippets that affect the project as a whole combined with one layer removed is library code. It needs to come with strong specs. The worst case scenario is you get differing behavior in production and development. I 100% appreciate the spirit of the post, but Monkey Patching core Django files is not to be taken lightly in production code. [0] https://github.com/django/django/blob/main/django/db/models/query_utils.py#L164 https://github.com/django/django/blob/main/django/db/models/...
- Flimm 4y ago
  There is django-zen-queries[0], mentioned in another comment. [0] https://github.com/dabapps/django-zen-queries https://github.com/dabapps/django-zen-queries
  - 4y ago
s17n 4y ago
If you're going to do this, you may as well simply not use an ORM. Which is definitely the solution that I'd recommend; they are just not actually a good idea.
- Daishiman 4y ago
  The ORM provides a myriad other features, like adapters for every production database under the sun, query composition that is literally impossible in plain SQL, a reasonable interface to the admin and the ecosystem of Django apps, and above all: a logical interface that maps _business objects_ to their SQL tables. The ORM hate seems to come from people whose day to day interaction with data tables isn't mediated by seeing them as business entities where all the goodness of object-oriented programming actually shines.
  - aforwardslash 4y ago
    ... Or by people that actually understand the impedance mismatch between objects and data (quick django example - request data and models are different and not easily interchangeable). Or people that require good caching implementations. Or people that actually design database systems schema-first. Or peoplw that rely on advanced usage that isnt always easy to perform in orm's. The list goes on.
    - aforwardslash 4y ago
      Extending on impedance of objects and data, and validated request data being different from models, imagine this pseudo-code: function action_endpoint(): if request.is_valid(): data = request.to_data_object(data_object_class) self.service.update(data) return success() return request.errors() in this simple example, the internal data representation isn't using a full-blown object, but a "data object" (ex. a dataclass). There are no transitive database dependencies, it behaves just like a fancy dict. When including data_object_class, I'm not including the whole database driver. When passing this to other system components, this can be serialized & de-serialized because it has no intrinsic behavior implemented. As such, when using architectural patterns like three-tier design or hexagonal design, you can pass data between layers without any "external"(from a data perspective) dependency; this allows the frontend to be completely agnostic on where & how data is stored. In fact, in this example, self.service could be an RPC proxy object to another subsystem, in a different server. The advantage of this design becomes quite apparent when you need to decouple how data is stored from how data is processed - you start designing your application in a service-oriented approach, instead of a model-oriented approach. In fact, one could just create an endpoint processor that receives a table name, and infers the rest of the logic in the middle (the request validation, the data object, the glue service for database), that today can write to a database, and tomorrow just calls an api without rebuilding your application.
wood-porch 4y ago
I was working on a library to do this which I never got around to fully finishing but the idea is there: https://github.com/lime-green/django-orm-plus https://github.com/lime-green/django-orm-plus I think all I had left was polishing the auto add logic and testing with a real project
wruza 4y ago
I wonder why ORMs still(?) work as simple wrappers and never track access patterns. If you see that `in books` generator’s results experience accesses through a relationship, it’s pretty obvious to join it in advance after few misses and serve `book.author.full_name` from cache. Of course that would make ORM more complex, but why would you need one otherwise. A good database interface should make good guesses, probably with some hints that could be slapped over without changing naive code patterns. But instead we get 1000 different cool ways to create a table and select a row by id.
- nicoburns 4y ago
  Because people want their queries to have predictable performance.
  - marcosdumay 4y ago
    Interestingly, they never get it. Every layer of the stack up to the client's database connector is unpredictable. But yes, people want it. And will trade a lot of performance for a false promise of predictability.
- danielheath 4y ago
  I don't want my app to change performance-sensitive behavior at runtime, that's a debugging nightmare. I do want my framework to throw an error in development mode if I screw up the preloading.
  - wruza 4y ago
    Since this also requires the same heuristics, it could be just an option: orm.unroll(“auto|throw|none”). Would suit you, me and a commenter who wants hundreds of requests as they wrote.
- fud101 4y ago
  I dont know any of this but doesn't the OP give a solution similar to this https://github.com/Suor/django-cacheops https://github.com/Suor/django-cacheops
dmpayton 4y ago
Heh, I'm literally in the middle of optimizing some N+1 query endpoints in a Django application for work, made a bit more tricky because of DRF's serializer. I think a setting for lazy queries would be a good solution, with it enabled by default to ease the transition. It would be nice to have a couple options, though – allow, warn, and error. It would also be great to have a way to change the setting on the fly so that, e.g., the Django shell can automatically enable it for those quick debugging sessions.
bagels 4y ago
Usually it's the wrong pattern, but not always. If you have a very large dataset, it can be beneficial. You can make smaller transactions, smaller query results, and not fill up local memory. For offline backfills, or various reporting jobs this can be the difference between something that works, and something that doesn't.
- Suor 4y ago
  For that fetching in chunks is usually a way to go. I use something of these usually https://handy.readthedocs.io/en/latest/db.html#queryset_iterator https://handy.readthedocs.io/en/latest/db.html#queryset_iter...
the_black_hand 4y ago
sentry.io is pretty good at catching N+1 queries.
- Maxion 4y ago
  Sentry has a lot of GDPR problems, it's not easy to set it up so you don't leak PII in e.g. traces sent to it.
  - openplatypus 4y ago
    Correct. Sentry on web and Firebase on mobile app are often storing troves of Personal Data captured by developers. Neither is meant for PII/Personal Data processing and are huge compliance risks.
  - black3r 4y ago
    You can self-host the open-source version, it's dockerized and pretty easy to set up...
har777 4y ago
Self plug: Checkout https://github.com/har777/pellet https://github.com/har777/pellet to easily find and fix django N+1 issues. I usually add it to existing integration tests so that they raise exceptions on N+1. If test coverage is low then I would suggest sending the N+1 metrics to something like datadog. That way your users using the product will reveal all the N+1 issues on your monitoring solution. EDIT: I should add a screenshot to the README lol but the middleware will print each api being called with a nice table showing each query and the number of times it was called for the api.
- stavros 4y ago
  This is great! I also use https://pypi.org/project/django-zen-queries/ https://pypi.org/project/django-zen-queries/, but Pellet might be better (Zen queries doesn't let you run any queries, which sometimes doesn't work). Here's a screenshot for you: https://imgz.org/i8XkiK2R.png https://imgz.org/i8XkiK2R.png
  - har777 4y ago
    Yay! And thanks for the screenshot :D
openplatypus 4y ago
TIL: > With something so innocent as an attribute access making an SQL query, it’s much easier to miss it. I like Python as much as the next person, but this is highly irresponsible design decision. Starting to appreciate Scala's IO effects even more.
- creshal 4y ago
  This is not a Python problem, it's a design decision of Django's ORM specifically.
geewee 4y ago
Shameless plug - I ran into this while developing REST interfaces with Django and built django-auto-prefetching: https://github.com/GeeWee/django-auto-prefetching https://github.com/GeeWee/django-auto-prefetching It essentially travels your DRF serializer tree and builds an auto-prefetched query automatically without you needing to do any work. Back when I still worked actively on it, I wanted to monkey-patch models to track whether or not n+1 was happening, and if it was, automatically do pre-fetching, so instead of an n+1 problem you'd end up with just a "3-4 queries when it could've been 1" problem - which is much more palatable. Never got around to that part though.
dncornholio 4y ago
Lazyloading has actually no place in any Framework.
cnity 4y ago
If only there were some way to retrieve information from disparate tables in a single request. Almost like some way to "join" the tables together... you could design a simple declarative language specific for querying in such a way. A "query language" if you will. It would have a simple structure, a structured query language, that enables you to get this data in a performant way without making N requests! I might go and make this.
- textread 4y ago
  Would GraphQL be a good starting point for such a language?
  - bibanez 4y ago
    It was sarcasm, he was referring to SQL lol
    - boxed 4y ago
      select_related in Django does this. There's also prefetch_related in Django which is not something that is easily done in standard SQL.
      - deleted 4y ago
        [deleted]
  - eurasiantiger 4y ago
    Yes, just transform it to an SQL statement.
- orf 4y ago
  Whilst shallowly funny the sarcasm actually shows a lack of understanding of the problem. The problem can be described as this: given an arbitrary point in a program, how can you infer what data is required at a future point without executing code between those two points? I’d love to see you go make a solution to this.
boxed 4y ago
In iommi we scream at you in the console if you have N+1 issues. It's not as harsh as just banning it.

175 points by Suor 4y ago

est 4y ago

This raises a question, are there any easy batching library in Python? It's like N+1 problem but for non-SQL. I have various RPCs, some of them accept multi actions with certain limits so how to write non-batch calls in code but execute them as a whole as needed?

davedx 4y ago

In our services I implemented a per request query counter that gently warns you if you exceed a max query count. Use case: identifying when it might make sense to use DataLoader. Note: doing this isn’t always worth it.

fbdab103 4y ago
I like this idea. I would just log the query count at the end of each request without any pre-defined limits. IFF you see performance drop, then you can always investigate and see if anything is triggering greater than expected calls. Nifty idea, and probably not too hard to implement.

gus_massa 4y ago

There is a problem with the URL. I think this is the correct one https://suor.github.io/blog/2023/03/26/ban-1-plus-n-in-django/ https://suor.github.io/blog/2023/03/26/ban-1-plus-n-in-djang...

Suor 4y ago
Thanks
Suor 4y ago
HN keeps automatically replacing the URL. It used to be a redirect before, but not anymore
- gus_massa 4y ago
  HN has a feature that uses the canonical address in the webpage. Is your page configured correctly? Looking at the source: <link rel="canonical" href="http://hackflow.com/blog/2023/03/26/ban-1-plus-n-in-django"> Don't repost it again (for now). Try fixing the canonical link, and send an email to the mods hn@ycombinator.com with a short explanation and a link to this post https://news.ycombinator.com/item?id=35313565 https://news.ycombinator.com/item?id=35313565 to save them a few minutes searching. They may fix it using admin magic or ask you to repost again once the problem is fixed.
- dang 4y ago
  As gus_massa pointed out, HN's software uses canonical URLs when it finds them. The canonical URL on the page you submitted was http://hackflow.com/blog/2023/03/26/ban-1-plus-n-in-django http://hackflow.com/blog/2023/03/26/ban-1-plus-n-in-django, so our software used that. I've fixed it above now.
deleted 4y ago
[deleted]

nickjj 4y ago

There is a case where having N+1 queries are beneficial. In Rails terms, it's when you perform Russian doll caching, but you can do this in any framework. The idea is you can cache a specific X thing which might make a query to an associated Y thing. A textbook N+1 query case (ie. a list of posts (X) that get the author's name (Y)). If you render the view without any cache with 10 things then you'd perform 20 queries but after the cache is warm you'd perform 0 queries. If item 5's Y gets updated then you only need to bust the cache for item 5 and query only item 5's Y association. Performing a preloaded query to get all X 10 things with their Y associated things could be an expensive query.

fiddlerwoaroof 4y ago
The downside here is a potential thundering herd issue if you’re forced to clear the cache.
- ilyt 4y ago
  cache with grace period ("serve old record while new is updating") is good solution here
  - tatersolid 4y ago
    That doesn’t help on process/container/VM restarts.
zdragnar 4y ago
That's great if you can fit a lot of your database in your server's memory, but seems like a terrible headache once you get a decent number of users. Personally, I'd much rather have sane queries in the first place, but rails isn't really my cup of tea either, so take my opinion with a large pinch of salt if you do.
- Kamq 4y ago
  > That's great if you can fit a lot of your database in your server's memory, but seems like a terrible headache once you get a decent number of users. You'd surely care about getting a significant chunk of your usage in server memory rather than what percentage of total data that is, no? To take the site we're on as an example, I'd be willing to bet the 30 things on the front page have one or two orders of magnitude more traffic than anything else (and probably a few more orders of magnitude more than the median post).

jonatron 4y ago

See also django-zen-queries https://github.com/dabapps/django-zen-queries https://github.com/dabapps/django-zen-queries , which can make it impossible for changes to a template to trigger queries.

rlawson 4y ago
Came here to post just that. Really like zen queries
stavros 4y ago
I like Zen queries, but sometimes you just have to make queries in templates (e.g. when you want to check the user on the request object), or sometimes it's just convenient and there's nothing wrong with it (e.g. when you want to check the user on the request object). Zen queries makes that use case impossible, sadly.
- jonatron 4y ago
  I don't actually use Zen queries myself, but it does have a queries_disabled template tag, which would allow for more specific control.

lr4444lr 4y ago

I don't disagree with the author in principle, but I find once the data gets big enough where it makes a difference, I've already shifted to using ".values()" to avoid the overhead of model creation, and the KeyErrors that will throw if I leave the query lazy is tantamount to the solution he describes.

Suor 4y ago
I usually use `.values()` and `.values_list()` only when model creation is really slow. Usually many rows fetched.
sgt 4y ago
Same, and using raw queries too when needed. Identify hot spots and do those. Regular ORM is fine for the rest (mostly).

deleted 4y ago

[deleted]

spapas82 4y ago

This is very useful! I'm gonna start integrating it with my projects. However having a way to allow/not allow n+1 queries (like a context manager) would be much better. The thing is that there are times where n+1 isn't a big problem and fixing it would be a form of premature optimisation. I'd prefer to be in control and decide if I care about the n+1 query situation or not for some specific view.

Suor 4y ago
Should be easy enough to implement. You only need a context manager that adds 1 to some threadlocal flag on enter and subrracts on exit then check this flag in the monkey patch. Not sure how costly that will be though.
- Suor 4y ago
  Here is an example of such thing https://github.com/Suor/django-cacheops/blob/8b3a79de29b2545179dcb591bf5d136c07ead4a2/cacheops/invalidation.py#L84-L101 https://github.com/Suor/django-cacheops/blob/8b3a79de29b2545...
selcuka 4y ago
It's not ideal, but you can simply log `DeferredAttribute.__get__` calls instead of raising an exception.
tantalor 4y ago
Just keep a list of exemptions in a file
squeaky-clean 4y ago
You could probably implement in in a context manager, have the `__enter__()` method execute the `_DA_get_original, DeferredAttribute.__get__ = DeferredAttribute.__get__, _DeferredAttribute_get` code and have the `__exit__` method undo that re-assignment. (Or maybe the reverse is better. Ban N+1 by default and the context manager `__enter__` puts back the original assignment, `__exit__` brings back the banned version).
4y ago

binarymax 4y ago

This is why I always advocated against ORMs. It’s so easy to fall into traps like this without even knowing it, and while you can work around it in some ORMs it is not obvious. Writing SQL is not that hard, and mapping the results to a type isn’t that hard either. So with an ORM you might end up saving several hours of work up front for lots of pain later.

mattbillenstein 4y ago
You're on the right side of the bell-curve meme my friend, but there are a lot more people in the thick-framework camp who spend their days getting lost in the complexity of ORMs and related tech...
- capableweb 4y ago
  It's more like the world is not black and white, engineering problems don't have "The perfect solution, the rest is trash", but rather "this problem has multiple solutions, depending on context, some have these tradeoffs and the others have these". In this particular case, it might not be worth to trade speed of having to think about SQL for performance (today or tomorrow). Maybe you're building something that will just be used by 2-3 people, so 1+N isn't really a issue. Or whatever, the conclusion as always is: it depends.
  - mattbillenstein 4y ago
    I agree it depends, but my hot take is almost universally the time people like to say ORMs save them they end up paying back in spades debugging them. Learn SQL!
- cellularmitosis 4y ago
  I really think it is more a matter of exposure and familiarity than bell-curve positioning. If any backend engineer with 1 year of ORM experience had spent that year instead becoming familiar with SQL, the speed bump would be practically nil.
4y ago

Dachande663 4y ago

Laravel has had a similar feature for a while[0]. It’s been useful to enable this in development but only warn in production to prevent breaking things unexpectedly. [0] https://laravel.com/docs/10.x/eloquent-relationships#preventing-lazy-loading https://laravel.com/docs/10.x/eloquent-relationships#prevent...

btown 4y ago

My Chaotic Good take on this: one could implement a qs.auto_fetch_deferred() that emits model instances with weak back-references to a WeakSet of all instances emitted, and on a deferred get on ANY instance, it prefetches that attribute onto ALL of the instances... so that it doesn't just complain, but actually fixes your 1+N issue. But here lies absolute madness...

readertime 4y ago
I agree; Django (and many ORMs) have taken the opinion that if you load N instances of a model, and then you load a related field on 1 of them, you likely only want the 1 related field. If however they assumed that loading a related field implies you likely want all related fields, perhaps there’d be fewer instances of foot-gunning.
Suor 4y ago
Actually there is such thing already https://pypi.org/project/django-auto-prefetch/ https://pypi.org/project/django-auto-prefetch/ Not a monkey patch though, so will only work on the models you inherit from this.

paulddraper 4y ago

In my experience, the far more pernicious way to get N+1 is serializers (Django REST)

Rapzid 4y ago
Huh, ran into same issue on a rails project; queries kicked off in inside serializes were a weed. We toyed with throwing errors if a query was made during serialization. On top of that, everything about serialization is terribly slow in Rails :|
Suor 4y ago
GraphQL makes it even more fun :)
Maxion 4y ago
Ugh yes, nested serializers in DRF are always fun. Almost makes you want to go noSQL.
jorl17 4y ago
A couple of years ago I wrote a set of utility classes (which inherit from Serializer and ViewSet) which "solve" this problem by inspecting serializers at the beginning of the request and figuring out what to pass to `select_related` and `prefetch_related` "automatically". It supports nested serializers, N-N, etc. Also lets you "help it" by saying "assume attribute X of a serializer accesses fields X,Y,Z of its object", for more "sophisticated" cases. It's a very messy piece of code but it has survived many projects since I first wrote it 6 years ago. The day I enabled it at a previous job, we reduced a page load from 20s to 800ms or so just with it.

Waterluvian 4y ago

On the topic of accidentally doing lots of extra queries, I love using Model.objects.raw to control exactly how it’s retrieving model data. I love how it keeps the results inside the Model realm but gives you careful control. I also like that if you access fields you didn’t ask for, it’ll go get them. But I wish it screamed louder when this happened. “Your logic works but we had to do extra queries. This might be an error!” So the featured article is incredibly valuable. This ought to be built-in. Django Silk has been critical for discovering these cases. They’re too easy to do. I love the middle ground of “ORM but you write the SQL.”

code_biologist 4y ago

On an unrelated note, Python folks should check out OP's library funcy [1]: "A collection of fancy functional tools focused on practicality. Inspired by clojure, underscore and my own abstractions." Thanks for the library Suor! I've used it happily for many years! [1] https://github.com/Suor/funcy https://github.com/Suor/funcy

gleb 4y ago

Elixir/Phoenix does this by default. It is a good default.

rowanseymour 4y ago

I would love to see this become something that can be toggled as a setting in core. The only time it's useful is opening up a shell to debug. The rest of the time, it's just making it really hard to find missing prefetches or `.only()` and `.defer()` calls that are too limited. It's never a good feature for a production site running at any kind of scale.

irjustin 4y ago

Rails has Bullet[0] to help identify and warn you against N+1 Does Django have anything active? Quick search revealed nplusone[1] but its been dead since 2018. [0] https://github.com/flyerhzm/bullet https://github.com/flyerhzm/bullet [1] https://github.com/jmcarp/nplusone https://github.com/jmcarp/nplusone

squeaky-clean 4y ago
The second, longer snippet from the OP article seems to do basically this. To modify it to be exactly like what bullet describes (warning you when in debug or test mode, instead of raising an exception, and have no effect in prod) you can replace line 31 with the logger.error() from line 33, and delete the else-case (lines 32 & 33)
- irjustin 4y ago
  It's not a risk I'm willing to take nor do I believe other general Django engs should take. It's fine if it's a pet project or if you've got deep knowledge of Django's inner workings. This requires understanding the affects of monkey patching core __get__ on DeferredAttribute. When reading the code vs core Django, it doesn't faithfully reproduce the normal case missing _check_parent_chain[0]. I'm not sure if that code path is supposed to be left out or of it's simply missing? The documentation of the code snippet doesn't explicitly state either. Code snippets that affect the project as a whole combined with one layer removed is library code. It needs to come with strong specs. The worst case scenario is you get differing behavior in production and development. I 100% appreciate the spirit of the post, but Monkey Patching core Django files is not to be taken lightly in production code. [0] https://github.com/django/django/blob/main/django/db/models/query_utils.py#L164 https://github.com/django/django/blob/main/django/db/models/...
Flimm 4y ago
There is django-zen-queries[0], mentioned in another comment. [0] https://github.com/dabapps/django-zen-queries https://github.com/dabapps/django-zen-queries
- 4y ago

s17n 4y ago

If you're going to do this, you may as well simply not use an ORM. Which is definitely the solution that I'd recommend; they are just not actually a good idea.

Daishiman 4y ago
The ORM provides a myriad other features, like adapters for every production database under the sun, query composition that is literally impossible in plain SQL, a reasonable interface to the admin and the ecosystem of Django apps, and above all: a logical interface that maps _business objects_ to their SQL tables. The ORM hate seems to come from people whose day to day interaction with data tables isn't mediated by seeing them as business entities where all the goodness of object-oriented programming actually shines.
- aforwardslash 4y ago
  ... Or by people that actually understand the impedance mismatch between objects and data (quick django example - request data and models are different and not easily interchangeable). Or people that require good caching implementations. Or people that actually design database systems schema-first. Or peoplw that rely on advanced usage that isnt always easy to perform in orm's. The list goes on.
  - aforwardslash 4y ago
    Extending on impedance of objects and data, and validated request data being different from models, imagine this pseudo-code: function action_endpoint(): if request.is_valid(): data = request.to_data_object(data_object_class) self.service.update(data) return success() return request.errors() in this simple example, the internal data representation isn't using a full-blown object, but a "data object" (ex. a dataclass). There are no transitive database dependencies, it behaves just like a fancy dict. When including data_object_class, I'm not including the whole database driver. When passing this to other system components, this can be serialized & de-serialized because it has no intrinsic behavior implemented. As such, when using architectural patterns like three-tier design or hexagonal design, you can pass data between layers without any "external"(from a data perspective) dependency; this allows the frontend to be completely agnostic on where & how data is stored. In fact, in this example, self.service could be an RPC proxy object to another subsystem, in a different server. The advantage of this design becomes quite apparent when you need to decouple how data is stored from how data is processed - you start designing your application in a service-oriented approach, instead of a model-oriented approach. In fact, one could just create an endpoint processor that receives a table name, and infers the rest of the logic in the middle (the request validation, the data object, the glue service for database), that today can write to a database, and tomorrow just calls an api without rebuilding your application.

wood-porch 4y ago

I was working on a library to do this which I never got around to fully finishing but the idea is there: https://github.com/lime-green/django-orm-plus https://github.com/lime-green/django-orm-plus I think all I had left was polishing the auto add logic and testing with a real project

wruza 4y ago

I wonder why ORMs still(?) work as simple wrappers and never track access patterns. If you see that `in books` generator’s results experience accesses through a relationship, it’s pretty obvious to join it in advance after few misses and serve `book.author.full_name` from cache. Of course that would make ORM more complex, but why would you need one otherwise. A good database interface should make good guesses, probably with some hints that could be slapped over without changing naive code patterns. But instead we get 1000 different cool ways to create a table and select a row by id.

nicoburns 4y ago
Because people want their queries to have predictable performance.
- marcosdumay 4y ago
  Interestingly, they never get it. Every layer of the stack up to the client's database connector is unpredictable. But yes, people want it. And will trade a lot of performance for a false promise of predictability.
danielheath 4y ago
I don't want my app to change performance-sensitive behavior at runtime, that's a debugging nightmare. I do want my framework to throw an error in development mode if I screw up the preloading.
- wruza 4y ago
  Since this also requires the same heuristics, it could be just an option: orm.unroll(“auto|throw|none”). Would suit you, me and a commenter who wants hundreds of requests as they wrote.
fud101 4y ago
I dont know any of this but doesn't the OP give a solution similar to this https://github.com/Suor/django-cacheops https://github.com/Suor/django-cacheops

dmpayton 4y ago

Heh, I'm literally in the middle of optimizing some N+1 query endpoints in a Django application for work, made a bit more tricky because of DRF's serializer. I think a setting for lazy queries would be a good solution, with it enabled by default to ease the transition. It would be nice to have a couple options, though – allow, warn, and error. It would also be great to have a way to change the setting on the fly so that, e.g., the Django shell can automatically enable it for those quick debugging sessions.

bagels 4y ago

Usually it's the wrong pattern, but not always. If you have a very large dataset, it can be beneficial. You can make smaller transactions, smaller query results, and not fill up local memory. For offline backfills, or various reporting jobs this can be the difference between something that works, and something that doesn't.

Suor 4y ago
For that fetching in chunks is usually a way to go. I use something of these usually https://handy.readthedocs.io/en/latest/db.html#queryset_iterator https://handy.readthedocs.io/en/latest/db.html#queryset_iter...

the_black_hand 4y ago

sentry.io is pretty good at catching N+1 queries.

Maxion 4y ago
Sentry has a lot of GDPR problems, it's not easy to set it up so you don't leak PII in e.g. traces sent to it.
- openplatypus 4y ago
  Correct. Sentry on web and Firebase on mobile app are often storing troves of Personal Data captured by developers. Neither is meant for PII/Personal Data processing and are huge compliance risks.
- black3r 4y ago
  You can self-host the open-source version, it's dockerized and pretty easy to set up...

har777 4y ago

Self plug: Checkout https://github.com/har777/pellet https://github.com/har777/pellet to easily find and fix django N+1 issues. I usually add it to existing integration tests so that they raise exceptions on N+1. If test coverage is low then I would suggest sending the N+1 metrics to something like datadog. That way your users using the product will reveal all the N+1 issues on your monitoring solution. EDIT: I should add a screenshot to the README lol but the middleware will print each api being called with a nice table showing each query and the number of times it was called for the api.

stavros 4y ago
This is great! I also use https://pypi.org/project/django-zen-queries/ https://pypi.org/project/django-zen-queries/, but Pellet might be better (Zen queries doesn't let you run any queries, which sometimes doesn't work). Here's a screenshot for you: https://imgz.org/i8XkiK2R.png https://imgz.org/i8XkiK2R.png
- har777 4y ago
  Yay! And thanks for the screenshot :D

openplatypus 4y ago

TIL: > With something so innocent as an attribute access making an SQL query, it’s much easier to miss it. I like Python as much as the next person, but this is highly irresponsible design decision. Starting to appreciate Scala's IO effects even more.

creshal 4y ago
This is not a Python problem, it's a design decision of Django's ORM specifically.

geewee 4y ago

Shameless plug - I ran into this while developing REST interfaces with Django and built django-auto-prefetching: https://github.com/GeeWee/django-auto-prefetching https://github.com/GeeWee/django-auto-prefetching It essentially travels your DRF serializer tree and builds an auto-prefetched query automatically without you needing to do any work. Back when I still worked actively on it, I wanted to monkey-patch models to track whether or not n+1 was happening, and if it was, automatically do pre-fetching, so instead of an n+1 problem you'd end up with just a "3-4 queries when it could've been 1" problem - which is much more palatable. Never got around to that part though.

dncornholio 4y ago

Lazyloading has actually no place in any Framework.

cnity 4y ago

If only there were some way to retrieve information from disparate tables in a single request. Almost like some way to "join" the tables together... you could design a simple declarative language specific for querying in such a way. A "query language" if you will. It would have a simple structure, a structured query language, that enables you to get this data in a performant way without making N requests! I might go and make this.

textread 4y ago
Would GraphQL be a good starting point for such a language?
- bibanez 4y ago
  It was sarcasm, he was referring to SQL lol
  - boxed 4y ago
    select_related in Django does this. There's also prefetch_related in Django which is not something that is easily done in standard SQL.
    - deleted 4y ago
      [deleted]
- eurasiantiger 4y ago
  Yes, just transform it to an SQL statement.
orf 4y ago
Whilst shallowly funny the sarcasm actually shows a lack of understanding of the problem. The problem can be described as this: given an arbitrary point in a program, how can you infer what data is required at a future point without executing code between those two points? I’d love to see you go make a solution to this.

boxed 4y ago

In iommi we scream at you in the console if you have N+1 issues. It's not as harsh as just banning it.