Async and concurrent SQL Queries and async view rendering

There is something I always wondered. Why are we not starting SQL Queries ahead of time in an async way in the controller so that once we reach the controller, we might already have some results available? In my world view, the SQL Query is mostly blocking IO which might could be run concurrently and might be started ahead of time. Of course, this would mess up the idea of one database connection per request and many more concepts like the lazy queries.

# Controller
def index
  @independent_query_slow = Async do Product.all end
  @independent_query_fast = Async do User.all end 
end

# View
<%= @independent_query_slow.each do |as_usual| %>
  # no lazy query happens here, but the query result was might loaded already
<% end %>
<%= @independent_query_fast.each do |as_usual| %>
  # no lazy query happens here, but the query result was probably loaded already
<% end %>
<%= @independent_query_slow.where(id: 1234).each do |as_usual| %>
  # lazy as usual but has to wait till the query result is available?
<% end %>

In a similar fashion, couldn’t the view be rendered in an async way too? Again, this would break many assumptions but could be dealt with a clear syntax around the blocks.

# Controller
def index
  @independent_query_slow = Product.all
  @independent_query_fast = User.all
end

# View
<%= @independent_query_slow.some_scope.each do |as_usual| %>
  # wait till the query result is available and then start rendering 
<% end.async %>
<%= @independent_query_fast.some_scope.each do |as_usual| %>
   # wait till the query result is available and then start rendering
   # The output will be likely be rendered ahead of the above slower block.
<% end.async %>
<% request.wait # ????? %>
# Or wait at the end of the view till all async renders have finished

The case of the async view rendering could be isolated using something like partials:

# app/views/users/index.html
<% User.all.each do |user| %>
  <%= render "user", async: true, user: user %>
<% end %>

# app/views/users/_user.html
<%= image_tag user.avatar # Nicely async loaded from S3 %>
<% user.products.each do |product |%>
     # The database connection pool be like 🤯 or 
     # hopefully has a limit of max X concurrent Queries
     # per Request and then starts blocking again.
<% end %>

Sorry that I can’t describe my idea better, but I see all this blocking IO and wonder if we couldn’t speed up the response times of each request by having an opt-in for async/concurrency on certain occasions.

2 Likes

It’s a great idea and should be possible to implement using the db gem and falcon web server.

https://rubygems.org/gems/db

SQL server interfaces already support sending the query and enumerating the results separately.

1 Like

I’ve been using that type of patterns on Elixir apps successfully (it is very well equipped for that type of stuff), and I’ve seen it mentioned by a few in the Ruby world (although I never implemented that for Rails app at this point).

One tricky point is the lifecycle of those async queries:

  • What will happen to async queries if your main request is interrupted (e.g. an error of some sort) - the impact can be different if these are read-only queries, or queries bringing changes to the database too. Slightly long-running queries can stack up & create troubles.
  • What will happen to them if your main request finishes, but does not for some reason properly “wait” for the async queries.

While some tooling can help (e.g. futures in concurrent-ruby), it can get tricky fast :slight_smile:.

It is especially tricky in Ruby because AFAIK you still cannot safely kill Ruby threads (article), so this means that you will have to bake some kind of short-term interruptibility (via short safe timeouts, or via another home-baked mechanism e.g. partial checkpoints in multi-step queries).

Another important point is the impact on your connection pool (you could see contention there & meet time-outs due to other queries trying to grab many connections from the pool at once).

So - it is an interesting idea, it will be easy to implement on a small-scale system, but having a robust implementation for this in Ruby will be a fair bit of work.

I’m curious to know if anyone already implemented general-purpose tooling to help that, though, happy to discover anything helpful in that area!