Database sharding and Rails

Question

What's the best way to deal with a sharded database in Rails? Should the sharding be handled at the application layer, the active record layer, the database driver layer, a proxy layer, or something else altogether? What are the pros and cons of each?

Answer 1

rails 6.1 provides ability to switch connection per database thus we can do the horizontal partitioning.

• Shards are declared in the three-tier config like this:

production:
  primary:
    database: my_primary_database
    adapter: mysql2
  primary_replica:
    database: my_primary_database
    adapter: mysql2
    replica: true
  primary_shard_one:
    database: my_primary_shard_one
    adapter: mysql2
  primary_shard_one_replica:
    database: my_primary_shard_one
    adapter: mysql2
    replica: true

• Models are then connected with the connects_to API via the shards key

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

  connects_to shards: {
    default: { writing: :primary, reading: :primary_replica },
    shard_one: { writing: :primary_shard_one, reading: :primary_shard_one_replica }
  }
end

• Then models can swap connections manually via the connected_to API. If using sharding, both a role and a shard must be passed:

ActiveRecord::Base.connected_to(role: :writing, shard: :shard_one) do
  @id = Person.create! # Creates a record in shard one
end

ActiveRecord::Base.connected_to(role: :writing, shard: :shard_one) do
  Person.find(@id) # Can't find record, doesn't exist because it was created
                   # in the default shard
end

reference:

• https://edgeguides.rubyonrails.org/active_record_multiple_databases.html#horizontal-sharding
• https://dev.to/ritikesh/multitenant-architecture-on-rails-6-1-27c7