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.

Transcript

1. Using Multiple Databases with Active Record Julia López – Rails World 2023 A real life use case
2. Julia López From Barcelona ☀ ❤ Rails since 2011 Refactoring & Upgrades 🙌 👾 @yukideluxe @yukideluxe@ruby.social
4. What can you expect? From this talk
5. Let’s start! 🎉
6. Multiple Databases? What? When? How?
7. What? What does it mean?
8. 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"
9. When? When is it needed?
10. High Availability How to make your app operate continuously and with minimal to zero downtime ✨ ✨
11. 🥵 🦄 🦄 🦄 🦄 🦄 X 1000 ☠ ❌ ❌ ❌ ❌ ❌
12. 📝 😌 🦄 🦄 🦄 🦄 🦄 X 1000 ﹦ 😌 Primary Replica Replication Copies data ☠ One location Another location ❌ ❌ FAILOVER
13. 📝 🥵 🦄 🦄 🦄 🦄 🦄 X 1000 Primary Another location
14. Sharding Splitting your data across multiple databases that share the same schema
15. Users Shard 1 Users Shard 2 Users Shard 3 🦄 ⁉ company_id == 1
16. Security & Compliance The data must be in most secure di ff erent location
17. [Insert any other reason here] Be mindful of the complication this brings
18. How? When it comes to having highly available databases
19. Setup at Harvest Real-life version dramatization
20. Setup at Harvest Simpli f ied version Primary Replica Replication ~ 1s lag 🧡 Writes & Reads Only Reads
21. 📜 A bit of history…
22. Introduced in Rails 6.0 Released ~ August 2019 Harvest upgrade ~ June 2020
23. 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
24. NO - features - Load balancing of replicas - Replication on replicas - Migrations on replicas What won’t do for you
25. How we did it before? Custom implementations or gems like seamless_database_pool
26. Show 👏 me 👏 the 👏 code!
27. 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
28. 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
29. 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 🐣
30. 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
32. 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
33. Con f iguration simpli f ied… class ApplicationRecord < ActiveRecord::Base self.abstract_class = true connects_to database: { writing: :primary, reading: :replica } end
34. Manual connection switching and reducing the load of your primary database
35. # 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
36. 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>'
37. Automatic Role Switching and reducing the load of your primary database 🫶
38. https://guides.rubyonrails.org/active_record_multiple_databases.html “is relatively primitive and deliberately doesn't do a whole lot”
39. GET and HEAD requests => Replica Other requests => Writer
40. Nobody* “All GET endpoints in my app are just doing SELECTS”
41. post :search, on: :collection “The HTTP protocol does not place any a priori limit on the length of a URI.”
42. “Read your own write”
43. Setup at Harvest Simpli f ied version Primary Replica Replication ~ 1s lag 🧡 Writes & Reads Only Reads
44. # POST /posts def create @post = Post.new(post_params) if @post.save redirect_to @post, notice: 'Post created.' else render :new end end
45. https://guides.rubyonrails.org/active_record_multiple_databases.html “is relatively primitive and deliberately doesn't do a whole lot”
46. But it is very extensible!
47. # 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
48. # 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
49. # 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
50. # 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
51. # 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 ↯
52. # 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 ↯
53. # 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 ↯
54. # 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
55. # 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
56. # 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
57. # 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
58. # 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
59. # 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 ↯
60. # 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
61. # 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
62. # 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
63. # 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
64. # 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 ↯ ↯
65. All the possibilities!
66. 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
67. 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
68. 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
69. 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
70. 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
71. 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
72. 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
73. What about old Harvest?
74. 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
75. 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
76. 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
77. class ReportsController < ApplicationController always_use_replica_db :all ... end class IntegrationOauthController < ApplicationController use_writer_db :new, :callback ... end
78. 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
79. 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
80. After deploy…
81. 🥳
82. THANK YOU ☺ Any questions?