Optimizing System Performance by Implementing a Dual Database Setup in Rails

Our system runs scheduled background services once a week, which, although not affecting user-facing functionalities, took approximately 5–6 hours to complete.

The side effect of these operations was a notable strain on our system’s reporting capabilities.

Our admin team, responsible for generating multiple reports, experienced significant delays.

These reports usually take between 5–10 seconds per record. However, on the days   when Our system runs scheduled background services. these  report requests frequently timed out after 30 seconds.

A temporary solution involved caching report fields. This reduced the frequency of timeouts as the first few attempts, allowing fields to be cached in stages.

This approach, while reducing timeouts, also caused confusion among admins due to occasional report timeouts, raising questions about system bugs or limitations.

Optimizing report queries wasn’t a viable option. As The numerous legacy reports were producing accurate results and were used solely by the internal admin team once a week.

Investing time in optimization could be time-consuming, and we aim to avoid altering the already accurate report logic

The Solution:

Implementing a Replica DB

To address this challenge, we decided to integrate a Replica DB. This setup would ensure synchronized operations between two databases:

• The first database is specifically configured for handling reading queries.
• The second database is exclusively focused on managing writing operations

For guidance on this implementation, we referenced the following articles:

• Rails 6.1 Pre-database connection switching
• Rails 6 Multiple database support

Given that we were operating on Rails 6, we couldn’t leverage model-level DB replica setups. As a result, we had to configure the entire application for dual database operations.

Configuration Steps:

1. Update database.yml:

We revised this file to distinguish between the primary and read replica configurations. Since migrations in our replica db and syncing of data with primary DB were managed by Heroku, we set database_tasks: false.

# config/database.yml

production:
  primary:
    <<: *default
    url: <%= ENV['DATABASE_URL'] %>
  primary_replica:
    <<: *default
    url: <%= ENV['DATABASE_REPLICA_URL'] %>
    database_tasks: false
    replica: true

2. Setting up a Follower DB in Heroku:

We utilized Heroku’s follower DB functionality, designating it as our replica DB.

3. Automatic Connection Switching:

In config/application.rb, we activated automatic connection switching as follows:

# config/application.rb

config.active_record.database_selector = { delay: 2.seconds }
config.active_record.database_resolver = ActiveRecord::Middleware::DatabaseSelector::Resolver
config.active_record.database_resolver_context = ActiveRecord::Middleware::DatabaseSelector::Resolver::Session

4. Adjusting the ApplicationRecord:

Given our Modular Monolith Architecture, as described in The Modular Monolith: Rails Architecture, we opted for an initializer rather than updating the ApplicationRecord files in each engine:

# config/initializers/database_connection.rb

ActiveRecord::Base.connects_to database: { writing: :primary, reading: :primary_replica }

5. Challenges & Adjustments:

Post-implementation, our tests in the testing environment flagged multiple ActiveRecord::ReadOnlyError Write query attempted while in readonly mode: errors. These arose from instances using the GET method but attempting record updates. To address this:

• We either transitioned these code blocks to POST/PUT requests
• Or encapsulated them within a writer role:

ActiveRecord::Base.connected_to(role: :writing) do
  # code using the writer role
end

Additionally, our use of the Devise gem presented complications. Devise occasionally performs writes inside GET requests. To counteract this, we added the following to our all Devise user models:

# models/user.rb

def update_tracked_fields!(request)
  User.connected_to(role: :writing) do
    super
  end
end

def update_tracked_fields(request)
  User.connected_to(role: :writing) do
    super
  end
end

def remember_me!
  User.connected_to(role: :writing) do
    super
  end
end

def forget_me!
  User.connected_to(role: :writing) do
    super
  end
end

Conclusion:

Our implementation of the dual database setup was a success. By redistributing the workload, we ensured that reports were generated promptly on the first attempt. We are currently in the process of analyzing and visually representing the performance boost this implementation has provided.