Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ban 1+N in Django

suor.github.io

Post reply on HN
Print view View original

111–120 of 153 posts

Re: Ban 1+N in Django

#111

Post by ilyt »

zdragnar wrote: post #11
nickjj wrote: post #7

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…

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.

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

Once you have few million users then you can think about better solution.

You can fit a lot into server memory, and spilling out of RAM to NVMe isn't that bad either.

Re: Ban 1+N in Django

#112

Post by ilyt »

fiddlerwoaroof wrote: post #10
nickjj wrote: post #7

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…

The downside here is a potential thundering herd issue if you’re forced to clear the cache.

cache with grace period ("serve old record while new is updating") is good solution here

Re: Ban 1+N in Django

#113

Post by ilyt »

zzzeek wrote: post #36
binarymax wrote: post #16

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.

> Writing SQL is not that hard, and mapping the results to a type isn’t that hard either. But... Then you've written an ORM.

I wouldn't call just mapping sql result to struct as ORM.

I'd draw a line at generating queries that are more trivial than "get a record by field" as ORM. Below that is just syntatic sugar over raw SQL.

Re: Ban 1+N in Django

#114

Post by Izkata »

lmm wrote: post #61
Izkata wrote: post #60

Earlier quoted context omitted.

> an unindexed full table scan looks exactly the same as an indexed join, for example, whereas in a good ORM they will look different ORM queries compile down to SQL, so how would they look different?

Typescript compiles down to Javascript but an unchecked cast looks different from a known-safe assignment in Typescript even though they look the same in Javascript.

We're talking about something that exists at the query planner level, not a new feature introduced on top of that. Whether to use an index or a table scan isn't chosen by the user, it's chosen by the engine when the SQL is run. ORMs don't have any special hook to "look different" - if the database engine wants to do a full table scan, it'll do it whether the query came from manual SQL or an ORM, because they look the same to the database.

Re: Ban 1+N in Django

#115

Post by [deleted] »

aforwardslash wrote: post #74
zzzeek wrote: post #36

Earlier quoted context omitted.

> Writing SQL is not that hard, and mapping the results to a type isn’t that hard either. But... Then you've written an ORM.

Nitpick: no, you've written a pure object mapper, that doesnt care about schema relations. This has the practical advantage of being just a data container that can be clearly serialized/deserialized, instead of a model object with a transitive database connection dependency.

[deleted]

Re: Ban 1+N in Django

#116

Post by zzzeek »

aforwardslash wrote: post #74
zzzeek wrote: post #36

Earlier quoted context omitted.

> Writing SQL is not that hard, and mapping the results to a type isn’t that hard either. But... Then you've written an ORM.

Nitpick: no, you've written a pure object mapper, that doesnt care about schema relations. This has the practical advantage of being just a data container that can be clearly serialized/deserialized, instead of a model object with a transitive database connection dependency.

I don't know what serialization/deserialization has do with this. Does the object map to database rows and there's code that moves the data back and forth? That's an object relational mapper.

Re: Ban 1+N in Django

#117

Post by zzzeek »

cnity wrote: post #96
zzzeek wrote: post #36

Earlier quoted context omitted.

> Writing SQL is not that hard, and mapping the results to a type isn’t that hard either. But... Then you've written an ORM.

So many people say this, but I have to imagine that such people haven't actually really tried to write SQL in their services and avoid the temptation to introduce all sorts of abstractions. Because in my experience this doesn't happen. You just have to be OK with some duplication (which most engineers over-focus on to the point of serious detriment).

In my experience I did what you describe above for about ten years then I wrote SQLAlchemy, tired of writing the same code over and over again (and successive projects certainly gained more and more abstraction as the constant repetition became more obviously a huge waste of time and verbosity). So that's one example.

Re: Ban 1+N in Django

#118

Post by eurasiantiger »

textread wrote: post #98
cnity wrote: post #97

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…

Would GraphQL be a good starting point for such a language?

Yes, just transform it to an SQL statement.

Re: Ban 1+N in Django

#119

Post by orf »

cnity wrote: post #97

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…

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.

Re: Ban 1+N in Django

#120

Post by cnity »

orf wrote: post #119
cnity wrote: post #97

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…

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.

The point is that there is no "generic" solution. The abstraction of the ORM has created the illusion that you don't need to worry about this, the ORM will handle it. N+1 examples like iterating over all the publications from an author arise because the goal is to not have to concern yourself with the fundamentals of data fetching.
Post reply on HN