10 ms·

Ban 1+N in Django

175 points by Suor 3y ago

s17n 3y 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 3y 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 3y 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 3y 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.
    - rtpg 3y ago
      Django's ORM is much better than most, to the point where Django considers that "I can't model this query with the ORM" to be a bug. There are of course some mismatches, but it's pretty hard to have a query that is not at all modelable, and Django's ORM is ... fairly predictable (I have some gripes about how obvious or not joins are but it's subjective).
      - aforwardslash 3y ago
        There is a subtle difference between "can't be done" or "I'll spend an afternoon digging through documentations, code & examples to implement this". Quick obvious example - use views for data retrieval (that may contain more fields than the actual model) and tables for data insertion.
        rtpg 3y ago
        In that case for the SQL, you would be pulling in data from one table, putting it into another. in the ORM I would similarly model it by having an abstract model to hold the general shape of the table, and then one read-only model and another for writing. With some helper functions to pull the data from one or the other. Though if you are working with views and not materialized views, performance wise "use .annotate when fetching data" will be roughly as performant as using a view. Views just end up getting swapped out when the planner deals with the query (in Postgres at least). Of course I get this cuz I spent a lot of time with the ORM. I think it's easy for people to think "there must be a magic option somewhere in the ORM to do this", but sometimes there's no magic. The counterpoint, though, is you have Python behind all of this so stuff like "share schema definitions between classes" is easy and straightforward in my experience (including on projects with like...80+ models, some with way too many fields, so I get your pain). It might not be the exact API you want though
        aforwardslash 3y ago
        > In that case for the SQL, you would be pulling in data from one table, putting it into another In django ORM lingo, that's declaring 2 models, assuming they're even in the same namespace or app. plus the request you use to fill them. If you don't find this awkward, its on you, not me :) > Views just end up getting swapped out when the planner deals with the query (in Postgres at least) Huh? do you actually know how views work? You don't even have an easy way (non-sql) of declaring views in Django. And apparently, you also seem to doesn't seem to grasp implementation details regarding views and materialized views - views are "server-side cached queries", but materialized views are physical pre-computed tables. Also, in PostgreSQL (as well as many other databases) a view can actually hold data from several tables, including remote tables. The whole "this is a RDBMS system and we shall abide by it" went through the window the moment I can use a SQLish database to query eg. CSV files and/or import them as local "tables". > Of course I get this cuz I spent a lot of time with the ORM Don't get me wrong, but it seems you're not spending enough time. Let me enlighten you using a personal anecdote: a project management solution where you log in your tasks during the day, the hours and it would keep track of the collaborator's project allocation during the project execution time. Each collaborator (from less than 100) would introduce from 1 to probably 20 tasks per day, on the projects they were allocated. Reports were generated - per project, daily, monthly, etc- you get the point. Obviously, those reports were computed using python and the ORM - so at some point, getting eg. a company-wide report with a couple of projects for a year would trigger some specific conditions that made the report take more than 10 minutes to generate. A dataset I could tabulate in excel (couple of hundred thousand lines). Half of the time was actually spent on allocating objects in memory for stuff that had 1 field read. Of course the report routine reused the existing code functions for specific computations, that increased 10-fold the execution and memory allocation. The 20-line sql query that replaced hundreds of python lines executed in less than a hundred milliseconds. I could blame the ORM, but instead I blame the application design that follows ORM constraints. Someone detached from a data source would tell you "the parent API needs to provide that" - instead, you use what you have at hand. Just because it works, it doesn't mean its good. > o stuff like "share schema definitions between classes" is easy and straightforward That is actually the shit-show design I always want to prevent. Classes should not share schema definitions, but data formats (data objects). In the specific context of Django, Models are a piss-poor data object representation (because it is a quite decent Model implementation, mind you), and the whole apps scaffolding is just... nuts. The notion of apps are self-contained units of functionality, but they quickly become a cesspit of cross-referencing imports. Rule of thumb in most languages, if different "modules" of the application need to share a schema definition, you're doing it wrong. But heyyy, Python.
    - Daishiman 3y ago
      The list goes on an on and yet in practice these problems are solvable and the impedance is just really not a big deal. There is only one feature missing in the ORM which is composite primary key. For everything else all those things have clear and simple solutions. "Impedance mismatch" is just a thought-terminating cliché. High-level languages have impedance mismatch with binary code; reactive components have impedance mismatch with state, relational tables have impedance mismatch with hierarchical data. Yet we find solutions and workarounds and the severity of these problems is generally overrated outside of purely theoretical contexts.
      - s17n 3y ago
        ORMs are just not performant unless you reason about all the code at the level of "what queries are going to be generated and when", which makes the ORM an unhelpful layer of obfuscation over the layer of abstraction that you're actually reasoning at. This is quite different from high level vs assembly where you can easily go your whole life without ever learning assembly language or how a compiler works. Or to put it another way, the difference between the two situations is that an ORM API is not a higher level language than SQL. Transpiling between two languages of comparable expressiveness (SQL is actually more expressive but no need to go there) adds an extra source of problems without gaining you much.
        Daishiman 3y ago
        > ORMs are just not performant unless you reason about all the code at the level of "what queries are going to be generated and when", which makes the ORM an unhelpful layer of obfuscation over the layer of abstraction that you're actually reasoning at. You're assuming I use the ORM to not reason about SQL or not think about performance. This isn't true; first of all because even if you write SQL, SQL performance is not immediately obvious for any but the simplest of indexed queries. In no storage system do you ever get away from reasoning about this. Second because SQL is actually a mediocre abstraction layer over your data storage. You can't really compose SQL queries; in an ORM taking a base Query object and adding a bunch of various `filter()` statements automatically does the right thing. Basic queries are much shorter visually; ORMs deal with the abstraction of table and column renames that mean rewriting all your SQL in other systems. I feel like you're just trotting out "reasons" out of a blog post from people whose priorities aren't the ones that people like us who write CRUD systems day in and day out do. Again, you're talking about theoretical disadvantages which I have only really encountered about a half dozen times in over a decade of using Django even in performance-sensitive areas. Rewriting one ORM query out of a hundred is not a problem, especially if I had to rewrite the SQL in the first place.
gus_massa 3y 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 3y ago
  Thanks
- Suor 3y ago
  HN keeps automatically replacing the URL. It used to be a redirect before, but not anymore
  - gus_massa 3y 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 3y 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 3y ago
  [deleted]
nickjj 3y 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 3y ago
  The downside here is a potential thundering herd issue if you’re forced to clear the cache.
  - ilyt 3y ago
    cache with grace period ("serve old record while new is updating") is good solution here
    - tatersolid 3y ago
      That doesn’t help on process/container/VM restarts.
- zdragnar 3y 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 3y 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 3y 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 3y ago
  Came here to post just that. Really like zen queries
- stavros 3y 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 3y 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 3y 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 3y ago
  I usually use `.values()` and `.values_list()` only when model creation is really slow. Usually many rows fetched.
- sgt 3y ago
  Same, and using raw queries too when needed. Identify hot spots and do those. Regular ORM is fine for the rest (mostly).
deleted 3y ago
[deleted]
spapas82 3y 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.
- selcuka 3y ago
  It's not ideal, but you can simply log `DeferredAttribute.__get__` calls instead of raising an exception.
- tantalor 3y ago
  Just keep a list of exemptions in a file
- Suor 3y 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 3y 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...
- squeaky-clean 3y 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).
- 3y ago
Dachande663 3y 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...
binarymax 3y 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 3y 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...
  - cellularmitosis 3y 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.
  - capableweb 3y 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 3y 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!
- 3y ago
btown 3y 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 3y 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 3y 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.
Waterluvian 3y 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 3y 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 3y ago
Elixir/Phoenix does this by default. It is a good default.
rowanseymour 3y 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 3y 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 3y 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 3y 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 3y 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
  - 3y ago
paulddraper 3y ago
In my experience, the far more pernicious way to get N+1 is serializers (Django REST)
- Rapzid 3y 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 3y ago
  GraphQL makes it even more fun :)
- Maxion 3y ago
  Ugh yes, nested serializers in DRF are always fun. Almost makes you want to go noSQL.
- jorl17 3y 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.
wood-porch 3y 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
est 3y 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?
wruza 3y 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 3y ago
  Because people want their queries to have predictable performance.
  - marcosdumay 3y 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 3y 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 3y 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 3y 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 3y 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 3y 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 3y 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 3y ago
sentry.io is pretty good at catching N+1 queries.
- Maxion 3y 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 3y 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 3y ago
    You can self-host the open-source version, it's dockerized and pretty easy to set up...
har777 3y 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 3y 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 3y ago
    Yay! And thanks for the screenshot :D
davedx 3y 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 3y 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.
openplatypus 3y 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 3y ago
  This is not a Python problem, it's a design decision of Django's ORM specifically.
geewee 3y 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 3y ago
Lazyloading has actually no place in any Framework.
cnity 3y 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 3y ago
  Would GraphQL be a good starting point for such a language?
  - bibanez 3y ago
    It was sarcasm, he was referring to SQL lol
    - boxed 3y 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 3y ago
        [deleted]
  - eurasiantiger 3y ago
    Yes, just transform it to an SQL statement.
- orf 3y 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 3y 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 3y ago

s17n 3y 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 3y 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 3y 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 3y 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.
  - rtpg 3y ago
    Django's ORM is much better than most, to the point where Django considers that "I can't model this query with the ORM" to be a bug. There are of course some mismatches, but it's pretty hard to have a query that is not at all modelable, and Django's ORM is ... fairly predictable (I have some gripes about how obvious or not joins are but it's subjective).
    - aforwardslash 3y ago
      There is a subtle difference between "can't be done" or "I'll spend an afternoon digging through documentations, code & examples to implement this". Quick obvious example - use views for data retrieval (that may contain more fields than the actual model) and tables for data insertion.
      - rtpg 3y ago
        In that case for the SQL, you would be pulling in data from one table, putting it into another. in the ORM I would similarly model it by having an abstract model to hold the general shape of the table, and then one read-only model and another for writing. With some helper functions to pull the data from one or the other. Though if you are working with views and not materialized views, performance wise "use .annotate when fetching data" will be roughly as performant as using a view. Views just end up getting swapped out when the planner deals with the query (in Postgres at least). Of course I get this cuz I spent a lot of time with the ORM. I think it's easy for people to think "there must be a magic option somewhere in the ORM to do this", but sometimes there's no magic. The counterpoint, though, is you have Python behind all of this so stuff like "share schema definitions between classes" is easy and straightforward in my experience (including on projects with like...80+ models, some with way too many fields, so I get your pain). It might not be the exact API you want though
        aforwardslash 3y ago
        > In that case for the SQL, you would be pulling in data from one table, putting it into another In django ORM lingo, that's declaring 2 models, assuming they're even in the same namespace or app. plus the request you use to fill them. If you don't find this awkward, its on you, not me :) > Views just end up getting swapped out when the planner deals with the query (in Postgres at least) Huh? do you actually know how views work? You don't even have an easy way (non-sql) of declaring views in Django. And apparently, you also seem to doesn't seem to grasp implementation details regarding views and materialized views - views are "server-side cached queries", but materialized views are physical pre-computed tables. Also, in PostgreSQL (as well as many other databases) a view can actually hold data from several tables, including remote tables. The whole "this is a RDBMS system and we shall abide by it" went through the window the moment I can use a SQLish database to query eg. CSV files and/or import them as local "tables". > Of course I get this cuz I spent a lot of time with the ORM Don't get me wrong, but it seems you're not spending enough time. Let me enlighten you using a personal anecdote: a project management solution where you log in your tasks during the day, the hours and it would keep track of the collaborator's project allocation during the project execution time. Each collaborator (from less than 100) would introduce from 1 to probably 20 tasks per day, on the projects they were allocated. Reports were generated - per project, daily, monthly, etc- you get the point. Obviously, those reports were computed using python and the ORM - so at some point, getting eg. a company-wide report with a couple of projects for a year would trigger some specific conditions that made the report take more than 10 minutes to generate. A dataset I could tabulate in excel (couple of hundred thousand lines). Half of the time was actually spent on allocating objects in memory for stuff that had 1 field read. Of course the report routine reused the existing code functions for specific computations, that increased 10-fold the execution and memory allocation. The 20-line sql query that replaced hundreds of python lines executed in less than a hundred milliseconds. I could blame the ORM, but instead I blame the application design that follows ORM constraints. Someone detached from a data source would tell you "the parent API needs to provide that" - instead, you use what you have at hand. Just because it works, it doesn't mean its good. > o stuff like "share schema definitions between classes" is easy and straightforward That is actually the shit-show design I always want to prevent. Classes should not share schema definitions, but data formats (data objects). In the specific context of Django, Models are a piss-poor data object representation (because it is a quite decent Model implementation, mind you), and the whole apps scaffolding is just... nuts. The notion of apps are self-contained units of functionality, but they quickly become a cesspit of cross-referencing imports. Rule of thumb in most languages, if different "modules" of the application need to share a schema definition, you're doing it wrong. But heyyy, Python.
  - Daishiman 3y ago
    The list goes on an on and yet in practice these problems are solvable and the impedance is just really not a big deal. There is only one feature missing in the ORM which is composite primary key. For everything else all those things have clear and simple solutions. "Impedance mismatch" is just a thought-terminating cliché. High-level languages have impedance mismatch with binary code; reactive components have impedance mismatch with state, relational tables have impedance mismatch with hierarchical data. Yet we find solutions and workarounds and the severity of these problems is generally overrated outside of purely theoretical contexts.
    - s17n 3y ago
      ORMs are just not performant unless you reason about all the code at the level of "what queries are going to be generated and when", which makes the ORM an unhelpful layer of obfuscation over the layer of abstraction that you're actually reasoning at. This is quite different from high level vs assembly where you can easily go your whole life without ever learning assembly language or how a compiler works. Or to put it another way, the difference between the two situations is that an ORM API is not a higher level language than SQL. Transpiling between two languages of comparable expressiveness (SQL is actually more expressive but no need to go there) adds an extra source of problems without gaining you much.
      - Daishiman 3y ago
        > ORMs are just not performant unless you reason about all the code at the level of "what queries are going to be generated and when", which makes the ORM an unhelpful layer of obfuscation over the layer of abstraction that you're actually reasoning at. You're assuming I use the ORM to not reason about SQL or not think about performance. This isn't true; first of all because even if you write SQL, SQL performance is not immediately obvious for any but the simplest of indexed queries. In no storage system do you ever get away from reasoning about this. Second because SQL is actually a mediocre abstraction layer over your data storage. You can't really compose SQL queries; in an ORM taking a base Query object and adding a bunch of various `filter()` statements automatically does the right thing. Basic queries are much shorter visually; ORMs deal with the abstraction of table and column renames that mean rewriting all your SQL in other systems. I feel like you're just trotting out "reasons" out of a blog post from people whose priorities aren't the ones that people like us who write CRUD systems day in and day out do. Again, you're talking about theoretical disadvantages which I have only really encountered about a half dozen times in over a decade of using Django even in performance-sensitive areas. Rewriting one ORM query out of a hundred is not a problem, especially if I had to rewrite the SQL in the first place.

gus_massa 3y 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 3y ago
Thanks
Suor 3y ago
HN keeps automatically replacing the URL. It used to be a redirect before, but not anymore
- gus_massa 3y 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 3y 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 3y ago
[deleted]

nickjj 3y 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 3y ago
The downside here is a potential thundering herd issue if you’re forced to clear the cache.
- ilyt 3y ago
  cache with grace period ("serve old record while new is updating") is good solution here
  - tatersolid 3y ago
    That doesn’t help on process/container/VM restarts.
zdragnar 3y 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 3y 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 3y 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 3y ago
Came here to post just that. Really like zen queries
stavros 3y 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 3y 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 3y 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 3y ago
I usually use `.values()` and `.values_list()` only when model creation is really slow. Usually many rows fetched.
sgt 3y ago
Same, and using raw queries too when needed. Identify hot spots and do those. Regular ORM is fine for the rest (mostly).

deleted 3y ago

[deleted]

spapas82 3y 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.

selcuka 3y ago
It's not ideal, but you can simply log `DeferredAttribute.__get__` calls instead of raising an exception.
tantalor 3y ago
Just keep a list of exemptions in a file
Suor 3y 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 3y 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...
squeaky-clean 3y 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).
3y ago

Dachande663 3y 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...

binarymax 3y 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 3y 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...
- cellularmitosis 3y 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.
- capableweb 3y 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 3y 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!
3y ago

btown 3y 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 3y 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 3y 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.

Waterluvian 3y 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 3y 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 3y ago

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

rowanseymour 3y 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 3y 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 3y 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 3y 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 3y 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
- 3y ago

paulddraper 3y ago

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

Rapzid 3y 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 3y ago
GraphQL makes it even more fun :)
Maxion 3y ago
Ugh yes, nested serializers in DRF are always fun. Almost makes you want to go noSQL.
jorl17 3y 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.

wood-porch 3y 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

est 3y 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?

wruza 3y 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 3y ago
Because people want their queries to have predictable performance.
- marcosdumay 3y 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 3y 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 3y 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 3y 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 3y 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 3y 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 3y 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 3y ago

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

Maxion 3y 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 3y 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 3y ago
  You can self-host the open-source version, it's dockerized and pretty easy to set up...

har777 3y 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 3y 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 3y ago
  Yay! And thanks for the screenshot :D

davedx 3y 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 3y 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.

openplatypus 3y 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 3y ago
This is not a Python problem, it's a design decision of Django's ORM specifically.

geewee 3y 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 3y ago

Lazyloading has actually no place in any Framework.

cnity 3y 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 3y ago
Would GraphQL be a good starting point for such a language?
- bibanez 3y ago
  It was sarcasm, he was referring to SQL lol
  - boxed 3y 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 3y ago
      [deleted]
- eurasiantiger 3y ago
  Yes, just transform it to an SQL statement.
orf 3y 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 3y 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.