Hacker News Reader

Comment by frfl | Hacker News Reader

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.

View on HN

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.

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.

Yes, the sqlalchemy docs. Dead serious.

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.

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.

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

Here's all the database 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/d...

(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/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/

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?

Given that one says Products.objects.all(), which one do you think it is?

Good on you for correctly determining the difference, though!

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.

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).

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.

Product.objects.filter(color="red")

This is the efficient one, filtering results upstream, via SQL, before they are returned to the client.

Correct. I'm not familiar with that ORM, but the first should produce SQL like "SELECT * FROM products WHERE color = 'red'" and only load the records you're interested in, while the second loads everything and throws away probably most.

It's Django (python) if you weren't sure of the name and wanted a look.

The table naming convention from the model name is a bit different, but impossible to know from just that code snippet. Also Django likes to explicitly list every field instead of *

> Also Django likes to explicitly list every field instead of *

Tbf this is a good pattern. It’s rare that you _need_ SELECT *, and if you do, you may not in six months after DDL has added more columns to the table.

Your intuition is correct. Product.objects.filter() will create an optimized query to issue to the database. Meanwhile, Products.objects.all() will retrieve all the records and leaves you with the task of filtering them in-memory.

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

Modern Ruby on Rails makes this really easy with Strict Loading.

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.

Reply on news.ycombinator.com