[ActiveRecord][Multiple Databases] Route queries automatically

rubyonrails-core
1

Ruby on Rails 6 supports multiple databases (Multiple Databases with Active Record — Ruby on Rails Guides) and provides an automatic connection switching which allows the application to switch from the primary to replica or replica to primary based on the HTTP verb.

There is another option to automatically routing queries to replicas based on the type of query, in general any SELECT statements will execute against replica(s), anything else will go to the primary database.

There is any plan/desire to route queries using this approach in rails core?

2 Likes
2

Not at this point.

The approach you suggesting is dangerous due to replication lag and very hard to explain to developers.

You could for example write to the database using INSERT and later when doing a SELECT expecting the data is already on the replica you find out it is not.

3 Likes
3

Thanks @rafaelfranca - It is important to notice that using HTTP verbs to automatically routing queries is also dangerous and have the same implications that you mention, rails resolves that problem setting a session after a write to the primary database to ensure a subsequent GET or HEAD request is send to the primary database within a delay window (2 seconds by default).

The difference between the current and the proposed approach is that in some cases GET request also perform some write operations forcing devs to write this kind of blocks around all these operations:

  def show
    @blog = Blog.find(params[:id])

    ActiveRecord::Base.connected_to(role: :writing) do 
       @blog.update(last_seen_at: Time.now)
    end
  end

The proposed approach would avoid these blocks as the automatically switching is determinated depending the database operation, an approach to avoid reading from the replica database before the change is reflected would be also possible to implement using this approach. Although I’m sure this is not something easy to implement and the current rails 6 also works properly, thanks again to let me know that this is not in the current plans.

Finally and if someone is interested looks like Makara has already implemented the proposed approach (GitHub - instacart/makara: A Read-Write Proxy for Connections; Also provides an ActiveRecord adapter.).

1 Like