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