Auto-connection switching between read and write database

application_record.rb

# app/models/application_record.rb

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

  # When true, the database automatically switches connections
  @@auto_switch_connection = true

  # returns the name of the read replica if one is defined in database.yml, otherwise returns :primary
  def self.reading_db
    db_configs = ApplicationRecord.configurations.configs_for(env_name: Rails.env, include_replicas: true)
    db_configs.find(&:replica?)&.name&.to_sym || :primary
  end

  connects_to database: { 
    writing: :primary,
    reading: ApplicationRecord.reading_db
  }

  before_save     :switch_to_primary_database
  before_destroy  :switch_to_primary_database
  after_commit    :switch_to_read_replica
  after_rollback  :switch_to_read_replica


  # These cannot be private if we want to call them
  # from inside a sidekiq worker or rails console.
  # private

  def switch_to_primary_database(switch_connection=nil)
    ApplicationRecord.switch_to_primary_database(switch_connection)
  end

  def self.switch_to_primary_database(switch_connection=nil)
    unless switch_connection.nil?
      self.auto_switch_connection = switch_connection
      ApplicationRecord.switch_to :writing
      return
    end

    ApplicationRecord.switch_to(:writing) if ApplicationRecord.auto_switch_connection?
  end


  def switch_to_read_replica(switch_connection=nil)
    ApplicationRecord.switch_to_read_replica(switch_connection)
  end

  def self.switch_to_read_replica(switch_connection=nil)
    unless switch_connection.nil?
      self.auto_switch_connection = switch_connection
      ApplicationRecord.switch_to :reading
      return
    end

    ApplicationRecord.switch_to(:reading) if ApplicationRecord.auto_switch_connection?
  end

  def self.switch_to(role)
    unless [:reading, :writing].include? role
      raise BadParameterError, "database rple is incorrect: #{role}"
    end
    
    ActiveRecord::Base.default_role = role
  end


  def self.auto_switch_connection?
    !!@@auto_switch_connection
  end

  def self.auto_switch_connection=(a_boolean)
    @@auto_switch_connection = a_boolean
  end

  def self.db_role
    ActiveRecord::Base.default_role
  end

  def self.db_name
    ActiveRecord::Base.connection.current_database
  end
end

These benchmarks were done in my local development environment via the Rails console with messaging between the primary and replica db turned off. The local db manager, PostgreSQL, is serving both databases.

The first benchmark is reading 400K rows and randomly changing 50 of those rows (write-after-read)

label   auto    fixed     Calc %
cstime  0.0     0.0       NaN
cutime  0.0     0.0       NaN
stime   0.3172  0.30151   105.20585
utime   5.15606 4.96281   103.89407
real    5.88201 5.63259   104.4282
total   5.47327 5.26432   103.9692

This next benchmark is 8000 reads with a modification to the row after each read.

label    auto      fixed    Calc %
cstime   0.0       0.0          NaN
cutime   0.0       0.0          NaN
stime    0.98369   1.11073   88.56299
utime   12.10756  11.85002  102.17332
real    16.74612  17.05444   98.19215
total   13.09125  12.96075  101.00692

When Calc % is < 100 that means the auto connection switching is faster than maintaining a fixed connection to the writing database.

Subtract 100 from Calc % to get the overhead burden of auto switching the db connection. When the burden is < 0, that means that the auto switching of the database connection if faster than maintaining a fixed connection.

The only mod to config/application.rb is to set the initial default_role to :reading.

  class Application < Rails::Application
    # Initialize configuration defaults for originally generated Rails version.
    config.load_defaults 6.1

    config.active_record.default_role = :reading

    # ...
  end

I feel like I'm treating the connection as if there is only one and not a pool of concurrent connections. For example the auto_switch_connection is defined as a class variable. As such it impacts all sub-classes of the ApplicationRecord.

The cakk backs are really instance related. Those instances being of sub-classes of ApplicationRecord.

So maybe `auto_switch_connection should be an instance variable (or a class variable on the sub-class) so that querys to one table can be on the reading role while queries to another table could be on the writing role.

When implementing this and adding the read only line in my application:
config.active_record.default_role = :reading
I am unable to run migrations. Any idea how to get migrations to run with a writer role?