<p>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?</p>

[{'answer_id': 44164, 'author': 'Hank Gay', 'author_id': 4203, 'author_profile': 'https://Stackoverflow.com/users/4203', 'pm_score': 0, 'selected': False, 'text': '<p>To my mind, the simplest way is maintain a 1:1 between rails instances and DB shards.</p>\n'}, {'answer_id': 44578, 'author': 'salt.racer', 'author_id': 757, 'author_profile': 'https://Stackoverflow.com/users/757', 'pm_score': 3, 'selected': False, 'text': '<p>For those of you like me who hadn\'t heard of sharding:</p>\n\n<p><a href="http://highscalability.com/unorthodox-approach-database-design-coming-shard" rel="noreferrer">http://highscalability.com/unorthodox-approach-database-design-coming-shard</a></p>\n'}, {'answer_id': 48261, 'author': 'John Topley', 'author_id': 1450, 'author_profile': 'https://Stackoverflow.com/users/1450', 'pm_score': 5, 'selected': True, 'text': '<p>FiveRuns have a gem named <a href="https://github.com/bpot/data_fabric" rel="nofollow noreferrer">DataFabric</a> that does application-level sharding and master/slave replication. It might be worth checking out.</p>\n'}, {'answer_id': 55666, 'author': 'Wayne Kao', 'author_id': 3284, 'author_profile': 'https://Stackoverflow.com/users/3284', 'pm_score': 3, 'selected': False, 'text': '<p>I assume with shards we\'re talking about horizontal partitioning and not vertical partitioning (<a href="http://en.wikipedia.org/wiki/Partition_(database)" rel="noreferrer">here are the differences on Wikipedia</a>).</p>\n\n<p>First off, <strong>stretch vertical partitioning</strong> as far as you can take it before you consider horizontal partitioning.  It\'s easy in Rails to have different models point to different machines and for most Rails sites, this will bring you far enough.</p>\n\n<p>For horizontal partitioning, in an ideal world, this would be handled at the <strong>application layer</strong> in Rails.  But while it\'s not hard, it\'s not trivial in Rails, and by the time you need it, usually your application has grown beyond the point where this is feasible since you have ActiveRecord calls sprinkled all over the place.  And no one, developers or management, likes working on it before you need it since everyone would rather work on features users will use now rather than on partitioning which may not come into play for years after your traffic has exploded.</p>\n\n<p><strong>ActiveRecord layer</strong>... not easy from what I can see.  Would require lots of monkey patching into Rails internals.</p>\n\n<p>At <a href="http://www.spock.com/about" rel="noreferrer">Spock</a> we ended up handling this using a <strong>custom MySQL proxy</strong> and open sourced it on SourceForge as <a href="http://spockproxy.sourceforge.net/" rel="noreferrer">Spock Proxy</a>.  ActiveRecord thinks it\'s talking to one MySQL database machine when reality it\'s talking to the proxy, which then talks to one or more MySQL databases, merges/sorts the results, and returns them to ActiveRecord.  Requires only a few changes to your Rails code.  Take a look at the Spock Proxy SourceForge page for more details and for our reasons for going this route.</p>\n'}, {'answer_id': 121339, 'author': 'MattMcKnight', 'author_id': 8136, 'author_profile': 'https://Stackoverflow.com/users/8136', 'pm_score': 2, 'selected': False, 'text': '<p>Connecting Rails to multiple databases is not a big deal- you simply have an ActiveRecord subclass for each shard that overrides the connection property.  That makes it pretty simple if you need to make cross-shard calls. You then just have to write a little code when you need to make calls between the shards.   </p>\n\n<p>I don\'t like Hank\'s idea of splitting the rails instances, because it seems challenging to call the code between the instances unless you have a big shared library.</p>\n\n<p>Also you should look at doing something like <a href="http://github.com/technoweenie/masochism/tree/master" rel="nofollow noreferrer">Masochism</a> before you start sharding.</p>\n'}, {'answer_id': 7489829, 'author': 'Napoleon', 'author_id': 955372, 'author_profile': 'https://Stackoverflow.com/users/955372', 'pm_score': 1, 'selected': False, 'text': '<p>For rails to work with replicated environment, I would suggest using my_replication plugin which helps switch database connection to one of the slaves at run-time</p>\n\n<p><a href="https://github.com/minhnghivn/my_replication" rel="nofollow">https://github.com/minhnghivn/my_replication</a></p>\n'}, {'answer_id': 68921027, 'author': 'Oshan Wisumperuma', 'author_id': 3011280, 'author_profile': 'https://Stackoverflow.com/users/3011280', 'pm_score': 2, 'selected': False, 'text': '<p>rails 6.1 provides ability to switch connection per database thus we can do the horizontal partitioning.</p>\n<ul>\n<li>Shards are declared in the three-tier config like this:</li>\n</ul>\n<pre class="lang-yaml prettyprint-override"><code>production:\n  primary:\n    database: my_primary_database\n    adapter: mysql2\n  primary_replica:\n    database: my_primary_database\n    adapter: mysql2\n    replica: true\n  primary_shard_one:\n    database: my_primary_shard_one\n    adapter: mysql2\n  primary_shard_one_replica:\n    database: my_primary_shard_one\n    adapter: mysql2\n    replica: true\n</code></pre>\n<ul>\n<li>Models are then connected with the connects_to API via the shards key</li>\n</ul>\n<pre class="lang-rb prettyprint-override"><code>class ApplicationRecord &lt; ActiveRecord::Base\n  self.abstract_class = true\n\n  connects_to shards: {\n    default: { writing: :primary, reading: :primary_replica },\n    shard_one: { writing: :primary_shard_one, reading: :primary_shard_one_replica }\n  }\nend\n</code></pre>\n<ul>\n<li>Then models can swap connections manually via the connected_to API. If using sharding, both a role and a shard must be passed:</li>\n</ul>\n<pre class="lang-rb prettyprint-override"><code>ActiveRecord::Base.connected_to(role: :writing, shard: :shard_one) do\n  @id = Person.create! # Creates a record in shard one\nend\n\nActiveRecord::Base.connected_to(role: :writing, shard: :shard_one) do\n  Person.find(@id) # Can\'t find record, doesn\'t exist because it was created\n                   # in the default shard\nend\n</code></pre>\n<h3>reference:</h3>\n<ul>\n<li><a href="https://edgeguides.rubyonrails.org/active_record_multiple_databases.html#horizontal-sharding" rel="nofollow noreferrer">https://edgeguides.rubyonrails.org/active_record_multiple_databases.html#horizontal-sharding</a></li>\n<li><a href="https://dev.to/ritikesh/multitenant-architecture-on-rails-6-1-27c7" rel="nofollow noreferrer">https://dev.to/ritikesh/multitenant-architecture-on-rails-6-1-27c7</a></li>\n</ul>\n'}, {'answer_id': 69558415, 'author': 'Liang Zhang', 'author_id': 2644370, 'author_profile': 'https://Stackoverflow.com/users/2644370', 'pm_score': 0, 'selected': False, 'text': '<p>Proxy layer is better, it can support all program languages.</p>\n<p>For example: Apache ShardingSphere\' proxy.</p>\n<p>There are 2 different products of Apache ShardingSphere, ShardingSphere-JDBC for application layer which for Java language only and ShardingSphere-Proxy for proxy layer which for all program languages.</p>\n<p>FYI: <a href="https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-proxy/" rel="nofollow noreferrer">https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-proxy/</a></p>\n'}, {'answer_id': 69561635, 'author': 'Mayank Agrawal', 'author_id': 5907260, 'author_profile': 'https://Stackoverflow.com/users/5907260', 'pm_score': 0, 'selected': False, 'text': '<p>Depends upon rails version. Newer rails version provide support for sharding as said by @Oshan. But if you can\'t update to a newer version you can use the octopus gem.\nGem Link\n<a href="https://github.com/thiagopradi/octopus" rel="nofollow noreferrer">https://github.com/thiagopradi/octopus</a></p>\n'}]
['https://Stackoverflow.com/questions/44145', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/4061/']