6 ms·

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

• fiddlerwoaroof 4y ago
  The downside here is a potential thundering herd issue if you’re forced to clear the cache.
  • ilyt 4y ago
    cache with grace period ("serve old record while new is updating") is good solution here
    • tatersolid 4y ago
      That doesn’t help on process/container/VM restarts.
• zdragnar 4y 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 4y 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).
    • zdragnar 4y ago
      That seems much more specialized than what I'd imagined based on the prior description. In this example, would rails only cache models that fit certain query parameters? Or is it a configurable LRU? How does the in-memory cache work when you have multiple puma workers? Or does this mechanism rely on something more esoteric? Given that this technique is part of solving the performance problems of N+1, I'm assuming things like votes and comments are included, and the high degree of write volume would imply that all of the caches need to stay up to date- at least with a fairly high degree of consistency.
  • ilyt 4y 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. 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.
• tveita 4y ago
  You'd ideally want to do something like dataloader, where you look up your N Xs in a single cache query, and then do a single database lookup for the (N-C) Xs that weren't in cache. You can then either eagerly load the Ys with the Xs like you said, or do a secondary cache lookup for every Y, and potentially another single database query for the Ys not in cache. Unfortunately this pattern gets really hairy if you're not using promises and an event loop. https://www.npmjs.com/package/dataloader https://www.npmjs.com/package/dataloader
  • stephen 4y ago
    +1. The JS event loop auto-monad-izing Promises into Haxl [1]-esqe trees of implicitly-batched loads has been a big win for us building on JavaScript/TypeScript. If I had to move to another language, I'd really want to find a "powered by the event loop / dataloader" framework, i.e. Vert.x for Java. Also, per dataloader, a shameless plug for our ORM that has dataloader de-N+1-ing built natively into all object graph traversals: https://joist-orm.io/docs/goals/avoiding-n-plus-1s https://joist-orm.io/docs/goals/avoiding-n-plus-1s [1]: https://github.com/facebook/Haxl https://github.com/facebook/Haxl
    • arjvik 4y ago
      > The JS event loop auto-monad-izing Promises into Haxl [1]-esqe trees of implicitly-batched loads can you explain what this means?
  • eurasiantiger 4y ago
    So, in other words, you want GraphQL.
    • akx 4y ago
      A dataloader sort of pattern tends to be on the implementing end of GraphQL.
      • eurasiantiger 4y ago
• mattbillenstein 4y ago
  Now you have like three problems instead of one - N+1 queries in the cold-cache case is slow, cache invalidation when something changes, and much more overall complexity...
  • jayd16 4y ago
    These are just the problems with any cache. And still, caches are quite useful.
  • nickjj 4y ago
    > cache invalidation when something changes, and much more overall complexity With Rails this style of caching is built into the framework, it's not too bad. It can be applied to other frameworks too because most of the "magic" is how the cache key is composed. Lots of frameworks have the bits and pieces to pull it off. Here's a snippet from the Rails docs on caching[0]: <% @products.each do |product| %> <% cache product do %> <%= render product %> <% end %> <% end %> That will automatically produce cache keys that look like this: views/products/index:bea67108094918eeba42cd4a6e786901/products/1 The key is scoped to the view responsible for rendering the snippet of HTML and each product. If any attribute of that product changes that would result in different HTML being produced then the cache will be busted automatically by Rails because that hash in the middle of the cache key will be different. You can also "help" Rails when it comes to associations by adding a `touch: true` argument to your relationship once at the model level so Rails knows to bust the cache if the association's attributes change. Like everything with caching, it's not 100% bullet proof and perfect but if you know the rules of how the framework works you can solve 95% of your caching needs without much headache. For the other 5% you have options but you can keep them on a need to know basis and look them up when you encounter issues (you may never need them in the end). [0]: https://guides.rubyonrails.org/caching_with_rails.html#fragment-caching https://guides.rubyonrails.org/caching_with_rails.html#fragm...
    • Suor 4y ago
      cacheops can do the same, but it never came to my mind to actually go this way. I would rather write some special code to fetch with `id__in=[...]` and then cache individually.
• ilyt 4y ago
  Right. Or just let the DB do the caching. If you're not making million user app or have hundreds of gigabytes to query it will be faster than anything done in Ruby code anyway. > 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. So instead of "do one query to the database, zero if cache is hot, one query again if you invalidate it", you do 20 queries, zero if cache is hot (but 20 cache queries), then 1 if you invalidate it. That's nice method when single entry query is very expensive but in most cases it will be living in DB cache already. It is only a win if DB is the bottleneck which by far is rarer case nowadays than RTT. Also you could just query all 20 records at once but write it to cache as separate records, then have write-thru logic for updating records. Best of both at cost of some complexity. You can also just... cache the resulting page instead of data-level caching, and not engage app code in most requests in the first place. Partial caching with ESI is also possible although pretty involved.
  • nickjj 4y ago
    > zero if cache is hot (but 20 cache queries) Rails lets you fetch_multi which means it would be 1 cache query if you used this feature. > It is only a win if DB is the bottleneck View rendering is pretty slow in Rails, rendering a view ends up being a real bottleneck. That's partly why caching that at the view layer is beneficial. If Rails were able to cache templates as well as other frameworks then I would agree that in a lot of cases you wouldn't need to worry about it and could preload most things.
• JeremyNT 4y ago
  > 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. The technique you describe makes sense, but how often is that preload query actually that expensive? Usually if I see an expensive query in Rails, it's because either 1) the indexes are missing for the joins or 2) you're instantiating extra full-blown objects when only some tiny amount of data of a primitive type is required. This kind of stuff is a good opportunity to just write the query directly that gets the data you really do need, rather than relying on the ORM and its overhead. If the problem is "maybe the user will want to see this, maybe they won't" then in many cases the easiest win is to lazy load the more detailed view with a new query that is only fired on some user interaction.
  • Suor 4y ago
    Writing things directly is tightly coupling code. Sometimes quite distant portions of it, i.e. database access and presentation logic. With ORM and some smarter lazy technics or introspection at least you can untie it, with hand written SQL there is no way. And the reason is strings are poorly composable. Unless you use some query generator, but then we are back to ORM-like something.
    • JeremyNT 4y ago
      Of course, it's not a one-size fits all solution. I agree that ORMs are incredibly helpful and using them is almost always the right place to start for this sort of thing because they are very flexible and take away the cognitive load. But we're talking about performance here... if/when you start to have issues, it is time to take a look and see where you can do better. Rails' ActiveRecord gives you tools to help build queries whether or not you materialize them as full blown objects. If you need to do some specific data extraction but want to avoid a bunch of needless instantiation, you can still leverage the ORM to help build the queries (and avoid writing raw SQL as strings in your codebase) which is a very helpful halfway point.