7 ms·

> The ORM model is the best I've ever worked with and any other always feels clunky with sharp edges to cut you when you hold it wrong. Idk. I have to grant th

> The ORM model is the best I've ever worked with and any other always feels clunky with sharp edges to cut you when you hold it wrong. Idk. I have to grant that Django ORM likes to make your life easy, but lazy loading on property calls is a dark pit full of shap punji sticks. Just overlook one instance where this is happening in a loop and say goodbye to performance and hello to timeouts left and right...

eYrKEC2 3y ago
ORM's always abstract away details, but you monitor for slow queries and slow endpoints and then just fix the issues when they crop up.
Alex3917 3y ago
> Just overlook one instance where this is happening in a loop and say goodbye to performance and hello to timeouts left and right... FWIW they do give you assertNumQueries in the testing tools, which makes it relatively easy to catch this as long as you have tests.
belorn 3y ago
There does seem to be a natural conflict between large data with hierarchical structure and the generally flat lists and dictionaries of Python, that quickly leads to poor performance. It usually only takes a few foreign keys to create an exponential number of queries. But I have no idea if there are database interfaces that make this problem simplistic. In my experience with Django, anything but the most simplistic page will be so noticeable slow that one has to go through the queries and use things like select related. Occasionally it is also better to just grab everything into memory and do operations in Python, rather than force the data manipulation to be done as a single database query. It is a good tool for its purpose, but it is no replacement for SQL knowledge when working with complex relational databases.
- h4kor 3y ago
  Yes you do have to work on your queries to keep them fast with growing complexity. Django also has a usable intermediate API to construct your queries, instead of writing raw SQL. Nice feature to have if you don't want to commit to a specific database yet. And as already written above, a slow but correct page is preferable to a wrong page because you ORM is omitting related data.
har777 3y ago
Simple middleware can warn you about lazy loading/N+1 queries. Most of the time people just forget it happens. Try using: https://github.com/har777/pellet https://github.com/har777/pellet Disclaimer: I built it :p You can easily see N+1 queries on the console itself or write a callback function to track such issues on your monitoring stack of choice on production.
- dinkleberg 3y ago
  That looks handy, thanks for sharing! I used to use some other n+1 logger, but yours actual shows the query which is more useful.
- fbdab103 3y ago
  Have a pitch on what differentiates this from django-toolbar? Just the focus on query count monitoring?
  - har777 3y ago
    Yeah the query count monitoring is the main focus as N+1 queries are super common in Django. I don't really have a pitch but here is why this was made: 1. we had a production DRF app with ~1000 endpoints but the react app consuming it was dead slow because the api's had slowed down massively. 2. we knew N+1 was a big problem but the codebase was large and we didn't even know where to start. 3. we enabled this middleware on production and added a small callback function to write endpoint=>query_count, endpoint=>query_time metrics to datadog. 4. once this was done it was quite trivial to find the hot endpoints sorted by num of queries and total time spent on queries. 5. pick the most hot endpoint with large number of queries, enable debug mode locally, fix N+1 code, add assertNumQueries assertions to integration tests to make sure this doesn't happen again and push to prod. 6. monitor production metrics dashboard just to double check. 7. rinse and repeat. For me this ability to continuously run on prod -> find issues and send to your monitoring stack -> alert -> fix locally workflow is the main selling point. Or of course you can just have it running locally on debug mode and check your console before pushing your changes but sometimes its just hard to expect that from every single engineer at your company. Then again your local data might not cause an issue so production N+1 monitoring is always nice.
fbdab103 3y ago
Non-performant code is going to eventually slip into any codebase. The trick is to monitor for when performance falls to an unacceptable level. Not toss all ORMs because some minority of generated queries are problematic. If maximum performance, 100% of the time was the end-goal, I would not be writing Python.
- DarkNova6 3y ago
  The python team I joined grew their codebase from a thin data access layer into a full blown application. Staff was full of data guys and had no idea about application engineering, but lots of opinions. The client was another company who intended the product as heart of their digital transformation. Guess who had to refactor this mess and steer away from catastrophy. I’m haunted to this day.
  - fbdab103 3y ago
    That general story can happen with any tech stack. POC whipped together without good architecture. Having proven itself, usage increases until the application starts to burst at the seams. Program must be redesigned, avoiding performance gotchas. I still think Django + the ORM give you a lot of runway before performance should be a concern.
    - DarkNova6 3y ago
      Idk. The python stack strikes as particularly troublesome if you don’t know exactly what you’re doing. There are no guardrails for less experienced devs that prevents you from making basic mistakes. I also realized mid project that the default rest framework doesn’t support good api model generation from OpenApi or vice versa. And most devs didn’t understand why using untyped dicts everywhere was bad. In all transparency, I am openly biased for static typing and have a background in java, c# and Swift. So what we ended up with looked similar to a statically typed language but without the tooling or performance.
      - rattray 3y ago
h4kor 3y ago
I'd rather have a slow page because of lazy loading, than a wrong page because the related objects are not loaded (i.e. typeorm)
greenie_beans 3y ago
lol been refactoring something like this
robertlagrant 3y ago
I sort of said this elsewhere. Others are saying "that's just what happens" and "well, Python?" but it doesn't just happen, and slow database queries are nothing to do with the application language. The problem is Django ORM, like Ruby on Rails, uses the Active Record pattern. Alternate ORMs, such as SQLAlchemy or Hibernate, uses Data Mapper pattern, which avoids the pitfalls of Active Record.
- pmontra 3y ago
  We can have 1+N queries in any language even without an ORM. Maybe an ORM facilitates mistakes because it hides joins but lack of experience is lack of experience. Some naive bad pseudocode: for book in select * from books: author = select * from authors where id == book.id print book.title author.name In the real world that nested select could be hidden many levels down in method or function calls or even in code run from the templating language. Example: Django templatetags can query the database from the HTML template and any framework or non framework I worked with can do that too.
  - robertlagrant 3y ago
    I'm not criticising ORMs, just how active record works.
    - chris12321 3y ago
      Well it's trivial in ActiveRecord to avoid n+1s by preloading associations. As with any technology, if you use it wrong and inefficently then things will be incorrect and slow.
      - robertlagrant 3y ago
        Here's an example I remember: if I want to update a field in a million records in my database, I can't just send the update command to the database to run. Instead, an ActiveRecord ORM will try and load all the records into the application, and for each record object make the change in memory, and then persist the record. If I'm remembering correctly, that is a fundamentally poor approach. Instead of telling the database to do some work, the database is doing more work, and the application is doing work. One mitigation is to batch the work[0]. Another is to special-case updates[1], which bypasses all the ActiveRecord pre/post-save logic. In either case you aren't holding the tool wrong. The tool is wrong. [0] https://apidock.com/rails/ActiveRecord/Batches/find_each https://apidock.com/rails/ActiveRecord/Batches/find_each - you'll note that the batches are "subject to race conditions" - i.e. each batch is its own transaction! And you're still loading the records into your application pointlessly. You're just limiting how many do it at once. [1] https://docs.djangoproject.com/en/dev/topics/db/queries/#updating-multiple-objects-at-once https://docs.djangoproject.com/en/dev/topics/db/queries/#upd... - note: > Be aware that the update() method is converted directly to an SQL statement. It is a bulk operation for direct updates. It doesn’t run any save() methods on your models, or emit the pre_save or post_save signals (which are a consequence of calling save()), or honor the auto_now field option. If you want to save every item in a QuerySet and make sure that the save() method is called on each instance, you don’t need any special function to handle that. Loop over them and call save()
gen220 3y ago
I think Django's ORM is a product of its time, when limitations about the expressibility of the "active record" concept were not fully understood. I like to describe it as Django's ORM will satisfy 80% of your needs immediately, 90% if you invest and sweat, 95% if you're quite knowledgeable in the underlying SQL. But there are still some rather common query shapes that are inexpressible or terribly awkward with Django's ORM. SQLAlchemy on the other hand never tries to hide its complexity (although to be fair they've become much better at communicating it in 2.0). On Day 1 you'll know maybe 20% of what you need to. You might not even have a working application until the end of week 1. But at the end of, idk, month 6? You're a wizard. The long-term value ceiling of SQLAlchemy/Alembic is higher than Django's, but Django compensates for it with their comparatively richer plugin ecosystem, so it's not so easy to compare the two.

eYrKEC2 3y ago

ORM's always abstract away details, but you monitor for slow queries and slow endpoints and then just fix the issues when they crop up.

Alex3917 3y ago

> Just overlook one instance where this is happening in a loop and say goodbye to performance and hello to timeouts left and right... FWIW they do give you assertNumQueries in the testing tools, which makes it relatively easy to catch this as long as you have tests.

belorn 3y ago

There does seem to be a natural conflict between large data with hierarchical structure and the generally flat lists and dictionaries of Python, that quickly leads to poor performance. It usually only takes a few foreign keys to create an exponential number of queries. But I have no idea if there are database interfaces that make this problem simplistic. In my experience with Django, anything but the most simplistic page will be so noticeable slow that one has to go through the queries and use things like select related. Occasionally it is also better to just grab everything into memory and do operations in Python, rather than force the data manipulation to be done as a single database query. It is a good tool for its purpose, but it is no replacement for SQL knowledge when working with complex relational databases.

h4kor 3y ago
Yes you do have to work on your queries to keep them fast with growing complexity. Django also has a usable intermediate API to construct your queries, instead of writing raw SQL. Nice feature to have if you don't want to commit to a specific database yet. And as already written above, a slow but correct page is preferable to a wrong page because you ORM is omitting related data.

har777 3y ago

Simple middleware can warn you about lazy loading/N+1 queries. Most of the time people just forget it happens. Try using: https://github.com/har777/pellet https://github.com/har777/pellet Disclaimer: I built it :p You can easily see N+1 queries on the console itself or write a callback function to track such issues on your monitoring stack of choice on production.

dinkleberg 3y ago
That looks handy, thanks for sharing! I used to use some other n+1 logger, but yours actual shows the query which is more useful.
fbdab103 3y ago
Have a pitch on what differentiates this from django-toolbar? Just the focus on query count monitoring?
- har777 3y ago
  Yeah the query count monitoring is the main focus as N+1 queries are super common in Django. I don't really have a pitch but here is why this was made: 1. we had a production DRF app with ~1000 endpoints but the react app consuming it was dead slow because the api's had slowed down massively. 2. we knew N+1 was a big problem but the codebase was large and we didn't even know where to start. 3. we enabled this middleware on production and added a small callback function to write endpoint=>query_count, endpoint=>query_time metrics to datadog. 4. once this was done it was quite trivial to find the hot endpoints sorted by num of queries and total time spent on queries. 5. pick the most hot endpoint with large number of queries, enable debug mode locally, fix N+1 code, add assertNumQueries assertions to integration tests to make sure this doesn't happen again and push to prod. 6. monitor production metrics dashboard just to double check. 7. rinse and repeat. For me this ability to continuously run on prod -> find issues and send to your monitoring stack -> alert -> fix locally workflow is the main selling point. Or of course you can just have it running locally on debug mode and check your console before pushing your changes but sometimes its just hard to expect that from every single engineer at your company. Then again your local data might not cause an issue so production N+1 monitoring is always nice.

fbdab103 3y ago

Non-performant code is going to eventually slip into any codebase. The trick is to monitor for when performance falls to an unacceptable level. Not toss all ORMs because some minority of generated queries are problematic. If maximum performance, 100% of the time was the end-goal, I would not be writing Python.

DarkNova6 3y ago
The python team I joined grew their codebase from a thin data access layer into a full blown application. Staff was full of data guys and had no idea about application engineering, but lots of opinions. The client was another company who intended the product as heart of their digital transformation. Guess who had to refactor this mess and steer away from catastrophy. I’m haunted to this day.
- fbdab103 3y ago
  That general story can happen with any tech stack. POC whipped together without good architecture. Having proven itself, usage increases until the application starts to burst at the seams. Program must be redesigned, avoiding performance gotchas. I still think Django + the ORM give you a lot of runway before performance should be a concern.
  - DarkNova6 3y ago
    Idk. The python stack strikes as particularly troublesome if you don’t know exactly what you’re doing. There are no guardrails for less experienced devs that prevents you from making basic mistakes. I also realized mid project that the default rest framework doesn’t support good api model generation from OpenApi or vice versa. And most devs didn’t understand why using untyped dicts everywhere was bad. In all transparency, I am openly biased for static typing and have a background in java, c# and Swift. So what we ended up with looked similar to a statically typed language but without the tooling or performance.
    - rattray 3y ago

h4kor 3y ago

I'd rather have a slow page because of lazy loading, than a wrong page because the related objects are not loaded (i.e. typeorm)

greenie_beans 3y ago

lol been refactoring something like this

robertlagrant 3y ago

I sort of said this elsewhere. Others are saying "that's just what happens" and "well, Python?" but it doesn't just happen, and slow database queries are nothing to do with the application language. The problem is Django ORM, like Ruby on Rails, uses the Active Record pattern. Alternate ORMs, such as SQLAlchemy or Hibernate, uses Data Mapper pattern, which avoids the pitfalls of Active Record.

pmontra 3y ago
We can have 1+N queries in any language even without an ORM. Maybe an ORM facilitates mistakes because it hides joins but lack of experience is lack of experience. Some naive bad pseudocode: for book in select * from books: author = select * from authors where id == book.id print book.title author.name In the real world that nested select could be hidden many levels down in method or function calls or even in code run from the templating language. Example: Django templatetags can query the database from the HTML template and any framework or non framework I worked with can do that too.
- robertlagrant 3y ago
  I'm not criticising ORMs, just how active record works.
  - chris12321 3y ago
    Well it's trivial in ActiveRecord to avoid n+1s by preloading associations. As with any technology, if you use it wrong and inefficently then things will be incorrect and slow.
    - robertlagrant 3y ago
      Here's an example I remember: if I want to update a field in a million records in my database, I can't just send the update command to the database to run. Instead, an ActiveRecord ORM will try and load all the records into the application, and for each record object make the change in memory, and then persist the record. If I'm remembering correctly, that is a fundamentally poor approach. Instead of telling the database to do some work, the database is doing more work, and the application is doing work. One mitigation is to batch the work[0]. Another is to special-case updates[1], which bypasses all the ActiveRecord pre/post-save logic. In either case you aren't holding the tool wrong. The tool is wrong. [0] https://apidock.com/rails/ActiveRecord/Batches/find_each https://apidock.com/rails/ActiveRecord/Batches/find_each - you'll note that the batches are "subject to race conditions" - i.e. each batch is its own transaction! And you're still loading the records into your application pointlessly. You're just limiting how many do it at once. [1] https://docs.djangoproject.com/en/dev/topics/db/queries/#updating-multiple-objects-at-once https://docs.djangoproject.com/en/dev/topics/db/queries/#upd... - note: > Be aware that the update() method is converted directly to an SQL statement. It is a bulk operation for direct updates. It doesn’t run any save() methods on your models, or emit the pre_save or post_save signals (which are a consequence of calling save()), or honor the auto_now field option. If you want to save every item in a QuerySet and make sure that the save() method is called on each instance, you don’t need any special function to handle that. Loop over them and call save()

gen220 3y ago

I think Django's ORM is a product of its time, when limitations about the expressibility of the "active record" concept were not fully understood. I like to describe it as Django's ORM will satisfy 80% of your needs immediately, 90% if you invest and sweat, 95% if you're quite knowledgeable in the underlying SQL. But there are still some rather common query shapes that are inexpressible or terribly awkward with Django's ORM. SQLAlchemy on the other hand never tries to hide its complexity (although to be fair they've become much better at communicating it in 2.0). On Day 1 you'll know maybe 20% of what you need to. You might not even have a working application until the end of week 1. But at the end of, idk, month 6? You're a wizard. The long-term value ceiling of SQLAlchemy/Alembic is higher than Django's, but Django compensates for it with their comparatively richer plugin ecosystem, so it's not so easy to compare the two.