🚀 Scaling Orders in Rails with Database Sharding: A Real-World Guide

🧠 What is Database Sharding?

Database sharding is the process of splitting a large table into multiple smaller databases (shards), where each shard holds a subset of your total data. This makes your application more scalable, performant, and fault-tolerant.

⚠️ Our Real Problem: Orders Table at Scale

Our Rails app was receiving lakhs of orders in real time. Our orders table became a bottleneck:

• Slower queries even with indexes
• Write lock contention
• Index bloat
• Spikes during peak trading hours

We had to scale horizontally—without rewriting the whole app.

✅ Solution: Shard orders Table by user_id

We split the orders into 8 databases:

• Each named order_shard_0 to order_shard_7
• Use user_id % 8 to route traffic

⚙️ How We Did It in Rails

1. database.yml Setup

production:
  primary:
    database: app_primary
    adapter: postgresql

  order_shard_0:
    database: app_orders_0
    adapter: postgresql
  # ...up to order_shard_7

2. Abstract Base for Shards

class OrderBase < ApplicationRecord
  self.abstract_class = true
end

3. Order Model

class Order < OrderBase
  belongs_to :user
end

4. Shard Routing Logic

module ShardRouter
  SHARDS = %i[order_shard_0 order_shard_1 ... order_shard_7]

  def self.for_user(user_id)
    SHARDS[user_id % SHARDS.size]
  end
end

5. Service Object

class OrderService
  def initialize(user_id)
    @user_id = user_id
    @shard = ShardRouter.for_user(user_id)
  end

  def create_order(params)
    ActiveRecord::Base.connected_to(database: @shard) do
      Order.create!(params.merge(user_id: @user_id))
    end
  end

  def fetch_latest_orders(limit = 10)
    ActiveRecord::Base.connected_to(database: @shard) do
      Order.where(user_id: @user_id).order(created_at: :desc).limit(limit)
    end
  end
end

6. Usage

service = OrderService.new(current_user.id)
service.create_order(price: 100, quantity: 2, symbol: "RELIANCE")
orders = service.fetch_latest_orders

🔍 Rails Joins in a Sharded World

Yes, you can do order.user. But Rails does this in two queries:

SELECT * FROM orders ...  -- shard
SELECT * FROM users ...   -- primary DB

• order.user ✅
• order.build_user ✅
• User.has_many :orders ❌
• Order.includes(:user) ❌

🧠 Best Practices

• Use user_id (or other sharding key) consistently
• Wrap shard logic inside service objects
• Avoid joins across DBs
• Put reference data (like users) in the primary DB
• Cache data where needed

⚡ Results After Sharding

🧾 Conclusion

Sharding helped us break through scaling walls. It takes effort—but if you're hitting database limits, this might be your solution too.

📌 TL;DR

• Sharded orders by user_id % 8
• Used connected_to in service layer
• Rails still supports associations but no cross-DB joins
• Latency dropped & throughput improved dramatically

💬 Connect

Questions or thoughts? Email me