4 ms·

Can anyone share good resources or codebases that have high quality web backend with optimized queries and data structures (db schema, backend internal data str

by frfl 3y ago

Can anyone share good resources or codebases that have high quality web backend with optimized queries and data structures (db schema, backend internal data structures, overall backend architecture)? I feel like a lot of code you see in articles or modern companies if often just following the popular architecture/paradigm du jour. As opposed to what this article highlights in terms of making backends return all the data you need in the minimal number of queries. I feel like I understand what's being described here, but I've never actually had the chance to work on anything non-trivial that does it like this, most places I've worked have gone no-SQL or used an orm or were not more than very thin wrappers around some CRUD queries.

stavros 3y ago
There's nothing in the ORM that prevents you from doing what the article describes; hell, the article describes doing it with the ORM, and, double hell, the ORM does the right thing by default, at least as far as avoiding N queries goes. The solution is to know how a database works, instead of thinking that Product.objects.filter(color="red") and [product for product in Product.objects.all() if product.color = "red"] are exactly the same thing.
- frfl 3y ago
  I understand that. I was asking more about if anyone knows any open source codebases that are high quality, non-trivial and well designed, that one should read and study to better see how others have written good code and systems.
  - baq 3y ago
    Yes, the sqlalchemy docs. Dead serious.
    - frfl 3y ago
      That's somewhat surprising, but then again, maybe not. Thanks, I'll take note of that. For anyone else, I also remember PostgresSQL manual/docs being very high quality, so that might be worth reading just to better grok SQL/relational DB topics.
      - cess11 3y ago
        If you're digging into sqlalchemy and enjoying it, you would probably enjoy the source of Ecto, the kinda-sorta-but-not-really ORM that's commonly used with Elixir.
  - js2 3y ago
    Sentry is built on-top of Django. It's a good example of a real-world non-trivial code base that has had to adapt as it grew up and scaled up. I haven't looked at the code in a few years, but I used to run it on-premise and found it fairly approachable and well-written, but with just the sorts of hacks you'd expect to see in a software code base that had evolved over time. https://github.com/getsentry/sentry https://github.com/getsentry/sentry Here's all the database models: https://github.com/getsentry/sentry/tree/master/src/sentry/models https://github.com/getsentry/sentry/tree/master/src/sentry/m... Here's an example of hacks in the form of massive caching to improve performance: https://github.com/getsentry/sentry/blob/master/src/sentry/db/models/manager/base.py https://github.com/getsentry/sentry/blob/master/src/sentry/d...
    - ultrasaurus 3y ago
      (Disclaimer: Former Sentry employee) There's a ton of DB passion at Sentry but most importantly for anyone this deep in the thread, Sentry also detects and helps fix common DB mistakes whether it's you or the ORM: https://docs.sentry.io/product/issues/issue-details/performance-issues/n-one-queries/ https://docs.sentry.io/product/issues/issue-details/performa... If you're at the “why it’s slow” runbook, Sentry turns the first 2 steps into 1 click: https://docs.sentry.io/product/performance/queries/ https://docs.sentry.io/product/performance/queries/
- happytoexplain 3y ago
  As a database novice, what is the difference? I assume one of the two is wastefully fetching all products and then filtering, while the other is omitting the filtered results as part of the fetch, and is therefore more efficient? If so, which is which?
  - sgarland 3y ago
    Given that one says Products.objects.all(), which one do you think it is? Good on you for correctly determining the difference, though!
    - happytoexplain 3y ago
      Ah, I see - since I'm not familiar with this language/API, I couldn't really infer what was happening behind the scenes. E.g. '.all()' could be constructing a request, not necessarily executing, and then the 'if' might modify that request intelligently.
      - stavros 3y ago
        You're right, this is Python/Django, where you don't get these semantics (Product is the database model, and it doesn't have access to the "if" to use in the filter). It was more of a general example, though, of "loop vs filtering in the SELECT", so the language doesn't matter much (well, apart from effectively making my point, which it perhaps failed at).
      - Izkata 3y ago
        In this ORM, both versions construct without executing until the results are accessed. The list comprehension looping over the results is an access, the "if" happens after that. The one exception to the obvious "results are accessed" intuition is slices (such as [:3] to mean first 3 elements), which thanks to how python implemented them lets the ORM use them to further modify the query by adding LIMIT instead of executing it.
  - kileywm 3y ago
    Product.objects.filter(color="red") This is the efficient one, filtering results upstream, via SQL, before they are returned to the client.
brightball 3y ago
Modern Ruby on Rails makes this really easy with Strict Loading. https://til.hashrocket.com/posts/l4t5xok8hg-activerecord-strict-loading-in-rails https://til.hashrocket.com/posts/l4t5xok8hg-activerecord-str... One of the biggest perks with the association loads is that it avoids joins by default, which can become a scaling barrier as you grow. Instead, it will capture the associated ids, find all of the related records in a single query and then transparently handle everything for you behind the scenes. Without any optimizations, you'll get essentially 1 query per loaded association rather than the N+1 "one per association per row". You can always tune things to utilize joins, subqueries and optimize specific cases later but this is a sensible setting that will avoid the vast majority of issues.
ryanjshaw 3y ago
In Microsoft land there's a sample reference architecture for .NET web apps: https://github.com/dotnet/eShop https://github.com/dotnet/eShop

by frfl 3y ago

stavros 3y ago

There's nothing in the ORM that prevents you from doing what the article describes; hell, the article describes doing it with the ORM, and, double hell, the ORM does the right thing by default, at least as far as avoiding N queries goes. The solution is to know how a database works, instead of thinking that Product.objects.filter(color="red") and [product for product in Product.objects.all() if product.color = "red"] are exactly the same thing.

frfl 3y ago
I understand that. I was asking more about if anyone knows any open source codebases that are high quality, non-trivial and well designed, that one should read and study to better see how others have written good code and systems.
- baq 3y ago
  Yes, the sqlalchemy docs. Dead serious.
  - frfl 3y ago
    That's somewhat surprising, but then again, maybe not. Thanks, I'll take note of that. For anyone else, I also remember PostgresSQL manual/docs being very high quality, so that might be worth reading just to better grok SQL/relational DB topics.
    - cess11 3y ago
      If you're digging into sqlalchemy and enjoying it, you would probably enjoy the source of Ecto, the kinda-sorta-but-not-really ORM that's commonly used with Elixir.
- js2 3y ago
  Sentry is built on-top of Django. It's a good example of a real-world non-trivial code base that has had to adapt as it grew up and scaled up. I haven't looked at the code in a few years, but I used to run it on-premise and found it fairly approachable and well-written, but with just the sorts of hacks you'd expect to see in a software code base that had evolved over time. https://github.com/getsentry/sentry https://github.com/getsentry/sentry Here's all the database models: https://github.com/getsentry/sentry/tree/master/src/sentry/models https://github.com/getsentry/sentry/tree/master/src/sentry/m... Here's an example of hacks in the form of massive caching to improve performance: https://github.com/getsentry/sentry/blob/master/src/sentry/db/models/manager/base.py https://github.com/getsentry/sentry/blob/master/src/sentry/d...
  - ultrasaurus 3y ago
    (Disclaimer: Former Sentry employee) There's a ton of DB passion at Sentry but most importantly for anyone this deep in the thread, Sentry also detects and helps fix common DB mistakes whether it's you or the ORM: https://docs.sentry.io/product/issues/issue-details/performance-issues/n-one-queries/ https://docs.sentry.io/product/issues/issue-details/performa... If you're at the “why it’s slow” runbook, Sentry turns the first 2 steps into 1 click: https://docs.sentry.io/product/performance/queries/ https://docs.sentry.io/product/performance/queries/
happytoexplain 3y ago
As a database novice, what is the difference? I assume one of the two is wastefully fetching all products and then filtering, while the other is omitting the filtered results as part of the fetch, and is therefore more efficient? If so, which is which?
- sgarland 3y ago
  Given that one says Products.objects.all(), which one do you think it is? Good on you for correctly determining the difference, though!
  - happytoexplain 3y ago
    Ah, I see - since I'm not familiar with this language/API, I couldn't really infer what was happening behind the scenes. E.g. '.all()' could be constructing a request, not necessarily executing, and then the 'if' might modify that request intelligently.
    - stavros 3y ago
      You're right, this is Python/Django, where you don't get these semantics (Product is the database model, and it doesn't have access to the "if" to use in the filter). It was more of a general example, though, of "loop vs filtering in the SELECT", so the language doesn't matter much (well, apart from effectively making my point, which it perhaps failed at).
    - Izkata 3y ago
      In this ORM, both versions construct without executing until the results are accessed. The list comprehension looping over the results is an access, the "if" happens after that. The one exception to the obvious "results are accessed" intuition is slices (such as [:3] to mean first 3 elements), which thanks to how python implemented them lets the ORM use them to further modify the query by adding LIMIT instead of executing it.
- kileywm 3y ago
  Product.objects.filter(color="red") This is the efficient one, filtering results upstream, via SQL, before they are returned to the client.

brightball 3y ago

Modern Ruby on Rails makes this really easy with Strict Loading. https://til.hashrocket.com/posts/l4t5xok8hg-activerecord-strict-loading-in-rails https://til.hashrocket.com/posts/l4t5xok8hg-activerecord-str... One of the biggest perks with the association loads is that it avoids joins by default, which can become a scaling barrier as you grow. Instead, it will capture the associated ids, find all of the related records in a single query and then transparently handle everything for you behind the scenes. Without any optimizations, you'll get essentially 1 query per loaded association rather than the N+1 "one per association per row". You can always tune things to utilize joins, subqueries and optimize specific cases later but this is a sensible setting that will avoid the vast majority of issues.

ryanjshaw 3y ago

In Microsoft land there's a sample reference architecture for .NET web apps: https://github.com/dotnet/eShop https://github.com/dotnet/eShop