Share
Embed
Using Multiple Databases with Active Record
As your application grows in usage, there are different things you might need to consider when scaling your database. In this talk, we will briefly introduce some of the reasons why having multiple databases makes sense and how Rails is helping us set our applications so they can read from those. In order to demonstrate that, we’ll show how we are using and extending the support for multiple databases provided by Rails.
Julia López
October 12, 2023
Transcript
82 slides
-
Using Multiple Databases with Active Record Julia López – Rails
World 2023 A real life use case -
Julia López From Barcelona ☀ ❤ Rails since 2011 Refactoring
& Upgrades 🙌 👾 @yukideluxe @[email protected] -
https://en.wikipedia.org/wiki/Database_server “A database server is a server which uses a
database application that provides database services to other computer programs […], as de f ined by the client-server model" -
High Availability How to make your app operate continuously and
with minimal to zero downtime ✨ ✨ -
🥵 🦄 🦄 🦄 🦄 🦄 X 1000 ☠ ❌
❌ ❌ ❌ ❌ -
📝 😌 🦄 🦄 🦄 🦄 🦄 X 1000 ﹦
😌 Primary Replica Replication Copies data ☠ One location Another location ❌ ❌ FAILOVER -
📝 🥵 🦄 🦄 🦄 🦄 🦄 X 1000 Primary
Another location -
Users Shard 1 Users Shard 2 Users Shard 3 🦄
⁉ company_id == 1 -
Security & Compliance The data must be in most secure
di ff erent location -
Setup at Harvest Simpli f ied version Primary Replica Replication
~ 1s lag 🧡 Writes & Reads Only Reads -
Features - Multiple writer databases and one replica each -
Manual connection switching - Automatic swapping between writer and replica - Horizontal Sharding and automatic shard switching (since Rails 6.1) - Extra rails tasks Highlights -
NO - features - Load balancing of replicas - Replication
on replicas - Migrations on replicas What won’t do for you -
How we did it before? Custom implementations or gems like
seamless_database_pool -
Con f iguration simpli f ied… production_default: &production_default adapter: mysql2
encoding: utf8mb4 collation: utf8mb4_unicode_520_ci database: <%= ENV['database'] %> username: <%= ENV['username'] %> password: <%= ENV['password'] %> pool: <%= Sidekiq.server? ? Sidekiq.options[:concurrency] + 2 : 2 %> reconnect: true strict: true production: &production primary: <<: *production_default host: <%= ENV['primary_host'] %> port: <%= ENV['primary_port'] %> replica: <<: *production_default host: <%= ENV['replica_host'] %> port: <%= ENV['replica_port'] %> replica: true -
Con f iguration simpli f ied… production_default: &production_default adapter: mysql2
encoding: utf8mb4 collation: utf8mb4_unicode_520_ci database: <%= ENV['database'] %> username: <%= ENV['username'] %> password: <%= ENV['password'] %> pool: <%= Sidekiq.server? ? Sidekiq.options[:concurrency] + 2 : 2 %> reconnect: true strict: true production: &production primary: <<: *production_default host: <%= ENV['primary_host'] %> port: <%= ENV['primary_port'] %> replica: <<: *production_default host: <%= ENV['replica_host'] %> port: <%= ENV['replica_port'] %> replica: true -
Con f iguration simpli f ied… production_default: &production_default adapter: mysql2
encoding: utf8mb4 collation: utf8mb4_unicode_520_ci database: <%= ENV['database'] %> username: <%= ENV['username'] %> password: <%= ENV['password'] %> pool: <%= Sidekiq.server? ? Sidekiq.options[:concurrency] + 2 : 2 %> reconnect: true strict: true production: &production primary: <<: *production_default host: <%= ENV['primary_host'] %> port: <%= ENV['primary_port'] %> replica: <<: *production_default host: <%= ENV['replica_host'] %> port: <%= ENV['replica_port'] %> replica: true 🐣 -
Con f iguration simpli f ied… production_default: &production_default adapter: mysql2
encoding: utf8mb4 collation: utf8mb4_unicode_520_ci database: <%= ENV['database'] %> username: <%= ENV['username'] %> password: <%= ENV['password'] %> pool: <%= Sidekiq.server? ? Sidekiq.options[:concurrency] + 2 : 2 %> reconnect: true strict: true production: &production primary: <<: *production_default host: <%= ENV['primary_host'] %> port: <%= ENV['primary_port'] %> replica: <<: *production_default host: <%= ENV['replica_host'] %> port: <%= ENV['replica_port'] %> replica: true -
Con f iguration simpli f ied… production_default: &production_default adapter: mysql2
encoding: utf8mb4 # collation: utf8mb4_unicode_520_ci database: <%= ENV['database'] %> username: <%= ENV['username'] %> password: <%= ENV['password'] %> pool: <%= Sidekiq.server? ? Sidekiq.options[:concurrency] + 2 : 2 %> reconnect: true strict: true production: &production primary: <<: *production_default host: <%= ENV['primary_host'] %> port: <%= ENV['primary_port'] %> replica: <<: *production_default host: <%= ENV['replica_host'] %> port: <%= ENV['replica_port'] %> replica: true -
Con f iguration simpli f ied… class ApplicationRecord < ActiveRecord::Base
self.abstract_class = true connects_to database: { writing: :primary, reading: :replica } end -
# Global ActiveRecord::Base.connected_to(role: :reading, prevent_writes: true) do # All code
in this block will be connected to the reading role end # Granular ApplicationRecord.connected_to(role: :reading, prevent_writes: true) do # Only models that inherit from ApplicationRecord will be connected to the reading role end -
prevent_writes: true Error: ProjectsControllerTest#test_POST_/projects_create: ActiveRecord::ReadOnlyError: Write query attempted while in
readonly mode: INSERT INTO `projects` (...) VALUES (...) app/controllers/projects_controller.rb:22:in `block in create' app/controllers/projects_controller.rb:19:in `create' test/controllers/projects_controller_test.rb:100:in `block (2 levels) in <class:ProjectsControllerTest>' test/controllers/projects_controller_test.rb:99:in `block in <class:ProjectsControllerTest>' -
post :search, on: :collection “The HTTP protocol does not place
any a priori limit on the length of a URI.” -
Setup at Harvest Simpli f ied version Primary Replica Replication
~ 1s lag 🧡 Writes & Reads Only Reads -
# POST /posts def create @post = Post.new(post_params) if @post.save
redirect_to @post, notice: 'Post created.' else render :new end end -
# your_app/config/initializers/multi_db.rb Rails.application.configure do 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 end -
# ActiveRecord::Middleware::DatabaseSelector # activerecord/lib/active_record/middleware/database_selector.rb def call(env) request = ActionDispatch::Request.new(env) select_database(request)
do @app.call(env) end end def select_database(request, &blk) ... response = if resolver.reading_request?(request) resolver.read(&blk) else resolver.write(&blk) end ... end -
# ActiveRecord::Middleware::DatabaseSelector # activerecord/lib/active_record/middleware/database_selector.rb def call(env) request = ActionDispatch::Request.new(env) select_database(request)
do @app.call(env) end end def select_database(request, &blk) ... response = if resolver.reading_request?(request) resolver.read(&blk) else resolver.write(&blk) end ... end -
# ActiveRecord::Middleware::DatabaseSelector # activerecord/lib/active_record/middleware/database_selector.rb def call(env) request = ActionDispatch::Request.new(env) select_database(request)
do @app.call(env) end end def select_database(request, &blk) ... response = if resolver.reading_request?(request) resolver.read(&blk) else resolver.write(&blk) end ... end -
# ActiveRecord::Middleware::DatabaseSelector # activerecord/lib/active_record/middleware/database_selector.rb def call(env) request = ActionDispatch::Request.new(env) select_database(request)
do @app.call(env) end end def select_database(request, &blk) ... response = if resolver.reading_request?(request) resolver.read(&blk) else resolver.write(&blk) end ... end ↯ -
# ActiveRecord::Middleware::DatabaseSelector # activerecord/lib/active_record/middleware/database_selector.rb def call(env) request = ActionDispatch::Request.new(env) select_database(request)
do @app.call(env) end end def select_database(request, &blk) ... response = if resolver.reading_request?(request) resolver.read(&blk) else resolver.write(&blk) end ... end ↯ -
# ActiveRecord::Middleware::DatabaseSelector # activerecord/lib/active_record/middleware/database_selector.rb def call(env) request = ActionDispatch::Request.new(env) select_database(request)
do @app.call(env) end end def select_database(request, &blk) ... response = if resolver.reading_request?(request) resolver.read(&blk) else resolver.write(&blk) end ... end ↯ -
# your_app/config/initializers/multi_db.rb Rails.application.configure do 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 end -
# your_app/config/initializers/multi_db.rb Rails.application.configure do 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 end -
# ActiveRecord::Middleware::DatabaseSelector::Resolver # activerecord/lib/active_record/middleware/database_selector/resolver.rb def reading_request?(request) request.get? || request.head? end
def read(&blk) if read_from_primary? read_from_primary(&blk) else read_from_replica(&blk) end end def read_from_primary? !time_since_last_write_ok? end def time_since_last_write_ok? Time.now - context.last_write_timestamp >= send_to_replica_delay end def write(&blk) write_to_primary(&blk) end -
# ActiveRecord::Middleware::DatabaseSelector::Resolver # activerecord/lib/active_record/middleware/database_selector/resolver.rb def reading_request?(request) request.get? || request.head? end
def read(&blk) if read_from_primary? read_from_primary(&blk) else read_from_replica(&blk) end end def read_from_primary? !time_since_last_write_ok? end def time_since_last_write_ok? Time.now - context.last_write_timestamp >= send_to_replica_delay end def write(&blk) write_to_primary(&blk) end -
# ActiveRecord::Middleware::DatabaseSelector::Resolver # activerecord/lib/active_record/middleware/database_selector/resolver.rb def reading_request?(request) request.get? || request.head? end
def read(&blk) if read_from_primary? read_from_primary(&blk) else read_from_replica(&blk) end end def read_from_primary? !time_since_last_write_ok? end def time_since_last_write_ok? Time.now - context.last_write_timestamp >= send_to_replica_delay end def write(&blk) write_to_primary(&blk) end -
# ActiveRecord::Middleware::DatabaseSelector::Resolver # activerecord/lib/active_record/middleware/database_selector/resolver.rb def reading_request?(request) request.get? || request.head? end
def read(&blk) if read_from_primary? read_from_primary(&blk) else read_from_replica(&blk) end end def read_from_primary? !time_since_last_write_ok? end def time_since_last_write_ok? Time.now - context.last_write_timestamp >= send_to_replica_delay end def write(&blk) write_to_primary(&blk) end ↯ -
# ActiveRecord::Middleware::DatabaseSelector::Resolver # activerecord/lib/active_record/middleware/database_selector/resolver.rb def reading_request?(request) request.get? || request.head? end
def read(&blk) if read_from_primary? read_from_primary(&blk) else read_from_replica(&blk) end end def read_from_primary? !time_since_last_write_ok? end def time_since_last_write_ok? Time.now - context.last_write_timestamp >= send_to_replica_delay end def write(&blk) write_to_primary(&blk) end -
# ActiveRecord::Middleware::DatabaseSelector::Resolver # activerecord/lib/active_record/middleware/database_selector/resolver.rb private def read_from_primary(&blk) ActiveRecord::Base.connected_to(role: ActiveRecord.writing_role, prevent_writes:
true) do instrumenter.instrument("database_selector.active_record.read_from_primary", &blk) end end def read_from_replica(&blk) ActiveRecord::Base.connected_to(role: ActiveRecord.reading_role, prevent_writes: true) do instrumenter.instrument("database_selector.active_record.read_from_replica", &blk) end end def write_to_primary ActiveRecord::Base.connected_to(role: ActiveRecord.writing_role, prevent_writes: false) do instrumenter.instrument("database_selector.active_record.wrote_to_primary") do yield ensure context.update_last_write_timestamp end end end -
# your_app/config/initializers/multi_db.rb Rails.application.configure do 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 end -
# ActiveRecord::Middleware::DatabaseSelector::Resolver::Session # activerecord/lib/active_record/middleware/database_selector/ resolver/session.rb def last_write_timestamp self.class.convert_timestamp_to_time(session[:last_write]) end def
update_last_write_timestamp session[:last_write] = self.class.convert_time_to_timestamp(Time.now) end -
# ActiveRecord::Middleware::DatabaseSelector::Resolver::Session # activerecord/lib/active_record/middleware/database_selector/ resolver/session.rb def last_write_timestamp self.class.convert_timestamp_to_time(session[:last_write]) end def
update_last_write_timestamp session[:last_write] = self.class.convert_time_to_timestamp(Time.now) end ↯ ↯ -
config.active_record.database_resolver_context = ForecastDatabaseResolver::RedisContext def last_write_timestamp self.class.convert_timestamp_to_time( @redis_client.get(redis_key).to_i ) end def
update_last_write_timestamp @redis_client.set( redis_key, self.class.convert_time_to_timestamp(Time.now), ex: 2 ) end def redis_key # account_id comes from the request header “last-write-in-db-#{account_id}" end -
config.active_record.database_resolver_context = ForecastDatabaseResolver::RedisContext def last_write_timestamp self.class.convert_timestamp_to_time( @redis_client.get(redis_key).to_i ) end def
update_last_write_timestamp @redis_client.set( redis_key, self.class.convert_time_to_timestamp(Time.now), ex: 2 ) end def redis_key # account_id comes from the request header “last-write-in-db-#{account_id}" end -
config.active_record.database_resolver_context = ForecastDatabaseResolver::RedisContext def last_write_timestamp self.class.convert_timestamp_to_time( @redis_client.get(redis_key).to_i ) end def
update_last_write_timestamp @redis_client.set( redis_key, self.class.convert_time_to_timestamp(Time.now), ex: 2 ) end def redis_key # account_id comes from the request header “last-write-in-db-#{account_id}" end -
config.active_record.database_resolver_context = ForecastDatabaseResolver::RedisContext def last_write_timestamp self.class.convert_timestamp_to_time( @redis_client.get(redis_key).to_i ) end def
update_last_write_timestamp @redis_client.set( redis_key, self.class.convert_time_to_timestamp(Time.now), ex: 2 ) end def redis_key # account_id comes from the request header “last-write-in-db-#{account_id}" end -
config.active_record.database_resolver_context = HarvestIdDatabaseResolver::HybridContext def last_write_timestamp # returns true for API
requests based on the request path return Time.now if always_use_writer? self.class.convert_timestamp_to_time( session[:last_write].to_i ) end def update_last_write_timestamp return if always_use_writer? session[:last_write] = self.class.convert_time_to_timestamp(Time.now) end -
config.active_record.database_resolver_context = HarvestIdDatabaseResolver::HybridContext def last_write_timestamp # returns true for API
requests based on the request path return Time.now if always_use_writer? self.class.convert_timestamp_to_time( session[:last_write].to_i ) end def update_last_write_timestamp return if always_use_writer? session[:last_write] = self.class.convert_time_to_timestamp(Time.now) end -
config.active_record.database_resolver_context = HarvestIdDatabaseResolver::HybridContext def last_write_timestamp # returns true for API
requests based on the request path return Time.now if always_use_writer? self.class.convert_timestamp_to_time( session[:last_write].to_i ) end def update_last_write_timestamp return if always_use_writer? session[:last_write] = self.class.convert_time_to_timestamp(Time.now) end -
module HarvestDatabaseSelector extend ActiveSupport::Concern included do around_action :select_database end class_methods
do def use_writer_db(*actions) @use_writer_db ||= actions end def always_use_replica_db(*actions) @always_use_replica_db ||= actions end def use_writer?(action) (@use_writer_db || []).include?(action.to_sym) || (@use_writer_db || []).include?(:all) end def use_replica?(action) (@always_use_replica_db || []).include?(action.to_sym) || (@always_use_replica_db || []).include?(:all) end end def select_database(&blk) if self.class.use_writer?(action_name) write(&blk) elsif self.class.use_replica?(action_name) read_from_replica(&blk) elsif reading_request? read(&blk) else write(&blk) end end # A lot of copy & paste from the default resolver + few tweaks end -
module HarvestDatabaseSelector extend ActiveSupport::Concern included do around_action :select_database end class_methods
do def use_writer_db(*actions) @use_writer_db ||= actions end def always_use_replica_db(*actions) @always_use_replica_db ||= actions end def use_writer?(action) (@use_writer_db || []).include?(action.to_sym) || (@use_writer_db || []).include?(:all) end def use_replica?(action) (@always_use_replica_db || []).include?(action.to_sym) || (@always_use_replica_db || []).include?(:all) end end def select_database(&blk) if self.class.use_writer?(action_name) write(&blk) elsif self.class.use_replica?(action_name) read_from_replica(&blk) elsif reading_request? read(&blk) else write(&blk) end end # A lot of copy & paste from the default resolver + few tweaks end -
class_methods do def use_writer_db(*actions) @use_writer_db ||= actions end def always_use_replica_db(*actions)
@always_use_replica_db ||= actions end def use_writer?(action) (@use_writer_db || []).include?(action.to_sym) || (@use_writer_db || []).include?(:all) end def use_replica?(action) (@always_use_replica_db || []).include?(action.to_sym) || (@always_use_replica_db || []).include?(:all) end end -
class ReportsController < ApplicationController always_use_replica_db :all ... end class IntegrationOauthController
< ApplicationController use_writer_db :new, :callback ... end -
module HarvestDatabaseSelector extend ActiveSupport::Concern included do around_action :select_database end class_methods
do def use_writer_db(*actions) @use_writer_db ||= actions end def always_use_replica_db(*actions) @always_use_replica_db ||= actions end def use_writer?(action) (@use_writer_db || []).include?(action.to_sym) || (@use_writer_db || []).include?(:all) end def use_replica?(action) (@always_use_replica_db || []).include?(action.to_sym) || (@always_use_replica_db || []).include?(:all) end end def select_database(&blk) if self.class.use_writer?(action_name) write(&blk) elsif self.class.use_replica?(action_name) read_from_replica(&blk) elsif reading_request? read(&blk) else write(&blk) end end # A lot of copy & paste from the default resolver + few tweaks end -
module HarvestDatabaseSelector extend ActiveSupport::Concern included do around_action :select_database end def
select_database(&blk) if self.class.use_writer?(action_name) write(&blk) elsif self.class.use_replica?(action_name) read_from_replica(&blk) elsif reading_request? read(&blk) else write(&blk) end end # A lot of copy & paste from the default resolver + few tweaks end