Rails ActiveRecord Shard Routing for Partitioned Tables

Rails has had first-class horizontal sharding since 6.1, and it does exactly what it promises: it switches connections when told to. Everything else — deciding which shard, keeping queries prunable, and keeping migrations away from partition DDL — is the application’s job. This guide covers all three. It joins the Django, SQLAlchemy and Hibernate material in ORM Integration & Partition-Aware Routing, part of Partitioning Implementation Patterns & Routing.

Prerequisites

Step 1 — Declare the shards

# config/database.yml
production:
  shard_0:
    <<: *default
    host: db-0.internal
    database: app_shard_0
  shard_0_replica:
    <<: *default
    host: db-0-replica.internal
    database: app_shard_0
    replica: true
  shard_1:
    <<: *default
    host: db-1.internal
    database: app_shard_1
  shard_1_replica:
    <<: *default
    host: db-1-replica.internal
    database: app_shard_1
    replica: true
Connections multiply: shards × pools × workers × instances Four shards each with a writing and a reading pool of five connections gives forty per Puma worker. Three workers per instance and six instances produce 720 connections against a fleet whose shards are each configured for 200. The arithmetic has to be done before deployment, because the failure appears as connection refusals under load rather than at boot. 4 shards × 2 roles × pool 5= 40 connections per worker × 3 Puma workers= 120 per instance × 6 instances= 720 fleet-wide each shard sees180 connections against max_connections 200 At 90% of the ceiling there is no room for a maintenance session, a replica promotion or an autoscaling event. Either shrink the pools, put a pooler in front of each shard, or reduce the number of roles that keep idle connections open. 
# app/models/application_record.rb
class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

  connects_to shards: {
    shard_0: { writing: :shard_0, reading: :shard_0_replica },
    shard_1: { writing: :shard_1, reading: :shard_1_replica },
  }
end

Operational note: Every shard name declared here becomes a connection pool per process. Multiply by the number of Puma workers and threads before checking it against each database’s max_connections.

DBA tip: Give the reading role a database user with no write privileges. It turns a routing mistake from a silent wrong-shard write into an immediate permission error.

Step 2 — Resolve the shard once, at the edge

# app/middleware/shard_selector.rb
class ShardSelector
  def initialize(app) = @app = app

  def call(env)
    request = ActionDispatch::Request.new(env)
    tenant  = TenantResolver.from(request)         # subdomain, token claim, header
    shard   = ShardDirectory.shard_for(tenant.id)  # cached, versioned

    ActiveRecord::Base.connected_to(shard: shard, role: :writing) do
      Current.tenant_id = tenant.id
      @app.call(env)
    end
  end
end
# jobs must carry the shard explicitly — they have no request to resolve from
class ApplicationJob < ActiveJob::Base
  around_perform do |job, block|
    shard = job.arguments.first.is_a?(Hash) ? job.arguments.first[:shard] : nil
    raise ArgumentError, "job enqueued without a shard" if shard.nil?
    ActiveRecord::Base.connected_to(shard: shard.to_sym, role: :writing) { block.call }
  end
end
Every entry point needs its own shard resolution A web request resolves the tenant in middleware and wraps the whole action in a connected_to block. A background job takes the shard from its arguments and wraps its perform. A rake task or console session has neither, so it silently uses the default shard — which is the source of most wrong-shard writes in Rails applications. web requesthas a subdomain/token background jobhas job arguments rake task / consolehas neither middleware resolves,connected_to wraps around_perform resolves,connected_to wraps nothing wraps itdefault shard is used correct shardwrites land where expected shard_0 by defaultwrong for every other tenant Make the default connection point at a database with no application tables, so an unwrapped path fails loudly instead of writing to shard zero. It is a one-line configuration change and it converts a data-corruption bug into an exception.

Operational note: Current.tenant_id is thread-local and reset per request by Rails’ CurrentAttributes. Do not rely on it inside threads you spawn yourself — pass the value explicitly.

SRE tip: Add a request-tagged log line with the resolved shard. During an incident, “which shard served this request” is the first question and should not require reasoning about middleware order.

Step 3 — Make unprunable relations impossible

Routing gets the query to the right shard; the partition key gets it to the right partition:

# app/models/event.rb
class Event < ApplicationRecord
  PARTITION_KEY = :occurred_at

  scope :in_window, ->(from, to) { where(occurred_at: from...to) }   # half-open

  def self.load_guarded(relation)
    unless relation.where_clause.send(:predicates).any? { |p| p.to_s.include?("occurred_at") }
      raise UnprunableQuery, "Event query without an occurred_at range would scan every partition"
    end
    relation.load
  end
end
# an explicit query object beats a default_scope, which is inherited in surprising places
class EventsInWindow
  def initialize(tenant_id:, from:, to:) = (@tenant_id, @from, @to = tenant_id, from, to)

  def call
    Event.where(tenant_id: @tenant_id).in_window(@from, @to)
  end
end

Operational note: Ruby ranges with ... are half-open, which is exactly the shape partition bounds need. Using .. produces an inclusive upper bound and double-counts the boundary instant across consecutive windows.

DBA tip: Avoid default_scope for this. It is silently inherited by associations and unscoped removes it entirely — a query object makes the requirement explicit and greppable.

Step 4 — Keep partition DDL out of migrations

# db/migrate/20260803000001_create_events_partitioned.rb
class CreateEventsPartitioned < ActiveRecord::Migration[7.1]
  def up
    execute <<~SQL
      CREATE TABLE events (
        id          bigserial,
        tenant_id   bigint      NOT NULL,
        occurred_at timestamptz NOT NULL,
        payload     jsonb,
        PRIMARY KEY (id, occurred_at)
      ) PARTITION BY RANGE (occurred_at);

      CREATE TABLE events_default PARTITION OF events DEFAULT;
      CREATE INDEX events_tenant_ts_idx ON events (tenant_id, occurred_at DESC);
    SQL
  end

  def down
    execute "DROP TABLE events"
  end
end
# lib/tasks/partitions.rake — the recurring half, run by the scheduler
namespace :partitions do
  task maintain: :environment do
    ShardDirectory.all_shards.each do |shard|
      ActiveRecord::Base.connected_to(shard: shard, role: :writing) do
        ActiveRecord::Base.connection.execute("CALL maintain_partitions()")
      end
    end
  end
end
Migrations run once per shard; the rake task runs forever Migrations create the parent table, its indexes and the default partition on each shard, once. A scheduled rake task iterates every shard and calls the idempotent maintenance procedure, creating future partitions and retiring old ones on every run. Mixing the two produces a migration history that grows by one file per month. db:migrate — once per shard rake partitions:maintain — hourly parent table with PARTITION BY parent indexes and constraints the DEFAULT partition reviewed, versioned, reversible monthly children, N months ahead retention detach past the cutoff runs against every shard in turn idempotent: safe at any frequency With schema_format set to :sql, structure.sql captures the partitioned DDL faithfully. The default :ruby format cannot represent PARTITION BY at all, so a schema load silently produces an unpartitioned table — which passes every test. That silent downgrade is the single most damaging Rails-specific mistake on a partitioned schema; set the format first.

Operational note: schema_format = :sql must be set before the first partitioned migration. Switching afterwards leaves a schema.rb in the repository that contradicts the database and will be loaded by someone’s db:setup.

SRE tip: Run rake partitions:maintain from the deploy pipeline as well as from the scheduler. Deploys are frequent enough to be a useful second trigger and cost nothing when the run is a no-op.

Verification

Check routing, pruning and schema fidelity separately:

# routing: the connection actually used
ActiveRecord::Base.connected_to(shard: :shard_1, role: :writing) do
  puts ActiveRecord::Base.connection_db_config.name    # => shard_1
end
# pruning: read the plan Rails produced, not one you retyped
sql = Event.where(tenant_id: 8842).in_window(Date.new(2026,8,1), Date.new(2026,9,1)).to_sql
puts ActiveRecord::Base.connection.execute("EXPLAIN (COSTS OFF) #{sql}").map { |r| r["QUERY PLAN"] }
Index Only Scan using events_tenant_ts_idx on events_2026_08 events
  Index Cond: ((tenant_id = 8842) AND (occurred_at >= '2026-08-01') AND (occurred_at < '2026-09-01'))

# schema fidelity: the dumped structure must still say PARTITION BY
grep -c 'PARTITION BY RANGE' db/structure.sql

Failure mode table

Failure mode Root cause SRE mitigation
Writes land on the wrong shard a code path ran outside any connected_to block and used the default connection point the default at a database with no application tables so it raises; add the shard to the request log
A schema load produces an unpartitioned table schema_format left at :ruby, which cannot express PARTITION BY set :sql before the first partitioned migration; assert on structure.sql content in CI
Queries scan every partition in production but not in tests the test database has one partition, so an unprunable query looks identical seed test databases with three or more partitions and assert on the plan, as in the pruning verification guide

FAQ

Does ActiveRecord choose the shard automatically?

No. Rails provides the connection-switching machinery and leaves the decision to you: a connected_to block, or a middleware that resolves the shard from the request and wraps the whole action. Anything outside such a block uses the default shard, which is why background jobs and console sessions are where mis-routed writes appear.

How do I stop ActiveRecord from generating unprunable queries?

Add a default scope or an explicit query object that requires the partition key, and raise when a relation is loaded without it. Rails makes this straightforward with a custom relation method, and the guard should fire at load time rather than at build time because relations are assembled across controllers, serializers and views.

Can Rails migrations create partitioned tables?

The parent table yes, through execute with raw DDL inside a migration. The child partitions no — they belong to a scheduled rake task, because they must be created forever rather than once. Keeping child DDL out of migrations also keeps schema.rb from trying to describe tables it cannot represent; use the SQL schema format instead.

Back to Orm Integration Partition Routing