Understand Rails async database queries by reimplementing them in 51 lines of simple Ruby

Posted Oct 17, 2023 Updated Oct 18, 2023

By Radan Skorić 10 min read

Psst, listen here: this post is really about leaky abstractions and programmer mental models but I’m hiding it very cunningly. Don’t tell anyone.

Rails 7.1. added async variants of many convenience querying methods. This is wonderful, as Rails developers we are bound to reap nearly free performance benefits from using it in the correct way. Why are those last words italic? For most applications the database is the main shared point of failure between different HTTP requests. If one of your app processes doesn’t play nice with the database it can affect the whole application in a bad way.

Good abstractions are an amazing time saver as long as we don’t push them to their breaking point. The best way I know of how to avoid that is to have a good mental model of how it actually works. A great way to do that is to peel away layers from the abstraction and look at just the core mechanism making it work. With the new understanding we can go back to our abstraction with more confidence.

To do that I’ve read the original PRs that added the async loading functionality and used the debugger to dig down to the actual raw happy path logic and summarised my findings here.

So let’s peel away the layers from ActiveRecord async queries!

Peeling away the layers

If you want to skip the high level description and get straight to the 51 lines of code you can jump ahead or have a look at the accompanying gist¹.

So what happens when you call load_async on a simple query object?

From query object we extract the underlying Arel object and after some processing hand it over to the low level select_all method on the connection object. The query object is no longer needed.
The Arel object, together with parameter bindings, is compiled just like it would be with a regular query into a raw sql string. Arel object is no longer needed.
We’re now deep in the database connection object and the raw sql and prepared bindings are passed to the private select method which finally creates a promise like object. In Rails codebase it’s called a “future result”. In our most common case it’s an instance of ActiveRecord::FutureResult::SelectAll. Like many promise object implementations it is essentially a small state machine tracking the state of the execution.
The future result object is scheduled for execution on a thread pool. In particular it uses the ThreadPoolExecutor from the excellent concurrent_ruby gem. The worker thread will checkout a connection from the shared db connection pool to run the query.
The future result object is then returned to the application code.

The actual code weaves through 9 layers of method calls but the above covers all the important parts of the happy path.

How ActiveRecord::FutureResult works?

When the future result object is accessed (for example in a view for rendering, by calling result or to_a on it) one of 3 things will happen:

If the query was already executed in the background, the future result object will have the values and will just return them immediately.
If the query is currently being executed, the current thread will wait for it to finish and return the result. Implementation detail: it does this by entering the same mutex as the one that the executing thread will take.
If the query has not yet started executing, it will execute it synchronously and return the result.

Assembling just the happy path

The plan is to recreate the happy path in as few lines of code as possible. Of course we’ll be ignoring a lot of useful functionality for the sake of learning but we’ll cover that later.

The code

Below code is true to how ActiveRecord works just stripped of everything except the happy path. It’s 51 lines excluding comments and blank lines:

  
class AsyncQueryLoader
  # A very very stripped down version of ActiveRecord::FutureResult.
  # Mainly, all the error handling is removed. Who needs that, right?
  class FutureResult
    # Everything except the db pool is just pass through to the low level execution method.
    def initialize(database_connection_pool, *args, **kwargs)
      @pool = database_connection_pool
      @mutex = Mutex.new
      @pending = true

      @args = args
      @kwargs = kwargs
    end

    def execute_or_skip
      return unless @pending

      @pool.with_connection do |connection|
        # If someone already has the mutex they're executing the query so we
        # don't need to do anything.
        return unless @mutex.try_lock
        begin
          # Check again if it is pending in case it changed while we were
          # entering the mutex.
          execute_query(connection) if @pending
        ensure
          @mutex.unlock
        end
      end
    end

    def result
      if @pending
        # If query is currently being executed the executing thread will hold the mutex.
        # So the way we actually wait for execution to finish is by
        # waiting to enter the mutex.
        @mutex.synchronize do
          # Check again if it is pending in case it changed while we were
          # entering the mutex.
          execute_query(@pool.connection) if @pending
        end
      end
      @result
    end

    private

    def execute_query(connection)
      @result = connection.internal_exec_query(*@args, **@kwargs)
    ensure
      @pending = false
    end
  end

  def initialize
    # These are the default settings that Rails will use.
    @async_executor = Concurrent::ThreadPoolExecutor.new(
      min_threads: 0,
      max_threads: 4, # The default value if you don't set global_executor_concurrency
      max_queue: 16, # Rails sets the queue at 4 x max_threads.
      fallback_policy: :caller_runs # If queue is full, run it right away
    )
  end

  def run_async(query)
    connection = ActiveRecord::Base.connection
    sql, binds, _preparable = connection.send(:to_sql_and_binds, query.arel)
    future_result = FutureResult.new(connection.pool, sql, "Name", binds, prepare: false)
    @async_executor.post { future_result.execute_or_skip }
    future_result
  end
end

Setup for testing the code

I am testing it within a single file, outside a normal Rails application to isolate just the async loading. For the database I’m using Postgres. Normally I’d use SQLite for an easier setup but for load_async we need to be talking to an external process that does the actual work so we can actually parallelise on the IO. Also, Postgres conveniently has the pg_sleep function which we can use to simulate a slow query.

We’ll start by setting up a model class with a slow query scope:

  
class User < ActiveRecord::Base
  scope :slow, -> {
    where("SELECT true FROM pg_sleep(1)").limit(10)
  }
end

query = User.slow

Note that at this point, due to lazy loading nothing has actually happened, we just constructed a query object. If we called query.load it would load it synchronously. For async loading we’d instead call query.load_async but to test our simplified code we can replace it with:

  
async_loader = AsyncQueryLoader.new
result = async_loader.run_async(User.slow)

If you want to test it yourself, check out the gist¹.

The parts that we ignored

Of course, this is not the full functionality of the async loading implementation. As is often the case with production code, the majority of the code is error handling and special cases:

Checking if async is enabled and executing synchronously if it isn’t.
Checking if we are in a transaction and executing synchronously if we are. Since the async execution is done on a different thread by a different db connection running it async would mean it’s also running outside the transaction.
Exiting early on some special conditions where the code can conclude there will be no results returned, like an empty query.
Handling eager loading.
Sanitising sql against injection attacks.
Handling prepared SQL statements.
Skipping query cache in case of a SELECT ... FOR UPDATE query.
Executing synchronously when concurrent connections are not supported at all, e.g. with in an memory SQLite db.
Wrapping the result into an active record instance.

The learnings

Pitfalls

Now that we’ve laid the happy path bare some key pitfalls become much clearer:

If we indiscriminately use async loading everywhere we can end up starving the database connection pool causing it to be empty when another request tries to check out a thread. In a high traffic scenario this could end up causing long delays on the requests, high queueing on the app server and eventually making the application unusable. Make sure that you have enough database connections.
In the case of a threaded web server, by default, the thread pool executor will be shared among the request handling threads. If we didn’t configure the concurrency correctly we could end up having very unreliable performance. Imagine that we have two requests come in one right after the other and both want to schedule 4 heavy queries for async loading. You could see the first request taking up all 4 worker threads on the async executor, forcing the other request into synchronous execution. This could result in the same endpoint varying strangely in execution time. You can control this via the global_executor_concurrency configuration setting (defaults to 4 as in the above code).

Sweet spot

It’s now also clearer what are the best places to use the async loading:

Ideally you have a single query that takes up a large chunk of a particular controller action and you can async just that one.
If you do need to use it more often on the same controller action, it is best if the action doesn’t see too much traffic. For example, a rarely hit but heavy action is a good place.
If you do need it on a higher traffic action and it is important to keep latency low on it, make sure to increase the global_executor_concurrency and to adjust maximum database connections so it can keep up.

Another useful way to think about it is as a better alternative to fetching additional content with a subsequent AJAX request. The additional AJAX request would have used another DB connection. Using it right away through async loading is simpler and faster.

How many database connections do I now need?

Until now in most cases when estimating how many database connections you needed you just had to worry about number of your background job worker threads and your total number of web serving threads. Now you need to also account for the threads that will be running the async queries. The formula, assuming you’re using the default setup with a global executor, is now:

  
needed_db_connections =
  web_processes * number_of_threads_per_web_process
  + web_processes * global_executor_concurrency
  + background_processes * number_of_threads_per_background_process
  + background_processes * global_executor_concurrency

Make sure that your database can open as many database connections as you need.

Monitoring

Key infrastructure metrics to monitor after deploying a change making use of async loading are:

Count of active database connections. This one is critical and you want to see it stay below the maximum at all times.
The p75 and p90 on the endpoints using async loading. If you watch just the average you might see it go down and miss that outliers became worse.

Conclusion

Async loading is an execellent addition to the Rails toolkit. Now that you understand how it works you can use it confidently.

If it leaks, you’ll know where you really need to plug it.