Optimize Database Performance in Rails and ActiveRecord (Rails 8.1)

To optimize database performance in Ruby on Rails, fix N+1 queries with includes, preload, or eager_load; index the columns your WHERE and JOIN clauses filter on; load only the columns you use with select and pluck; batch large reads and writes with find_each and insert_all; and move to read replicas or sharding only once query-level fixes run out.

This page is the single reference for database performance in Rails: the N+1 anti-pattern and its fixes, query shaping, indexing, batching, and the scaling steps that come after. Every snippet, query log, and error message was reproduced on Rails 8.1.3.1 (Ruby 3.4.10, PostgreSQL 17.11). If you landed here from an error, jump to StrictLoadingViolationError, PG::UndefinedColumn, PG::UndefinedTable, ReadOnlyError, or CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Finding which query is slow in production is a different job, covered step by step in Finding the Slow Query Killing Your Rails App.

N+1 Queries in ActiveRecord

The N+1 queries problem is a common, and usually easy to spot, performance anti-pattern: one query fetches a set of records, and then a further query runs for each of those records to load an association nobody loaded up front. ORMs like ActiveRecord invite it, because they lazy load associations at the moment they are used, so a loop over a list of products that touches product.variants runs one variants query per product on top of the query that fetched the products. It creeps into codebases over time as features evolve, and at scale it shows up as slow pages, an exhausted connection pool, and memory spikes.

The logs in this section come from the development environment of a Rails 8.1.3.1 app with query-log tags on, which inlines the query values; the /*application='Demo'*/ comment Rails appends to every query is stripped for readability.

Lazy Loading in ActiveRecord

ActiveRecord uses implicit lazy loading to make associations easy to work with. Take a webshop where each Product has any number of Variants holding a color or a size:

# app/models/product.rb
class Product < ApplicationRecord
  has_many :variants
end

# app/models/variant.rb
class Variant < ApplicationRecord
  belongs_to :product
end

In ProductsController#show, the detail view for one product, Product.find(params[:id]) fetches the product and assigns it to @product:

# app/controllers/products_controller.rb
class ProductsController < ApplicationController
  def show
    @product = Product.find(params[:id])
  end
end

The view loops over the product’s variants by calling variants on @product:

<%# app/views/products/show.html.erb %>
<h1><%= @product.title %></h1>
 
<ul>
<% @product.variants.each do |variant| %>
  <li><%= variant.name %></li>
<% end %>
</ul>

Open the loop with <%, not <%=. The <%= form also prints the return value of each, which is the whole variants array, HTML-escaped, after the list.

Calling @product.variants in the view makes Rails query the database for the variants. Besides the explicit query from the controller, the log for this request shows a second one, issued while the view renders:

Started GET "/products/1" for 127.0.0.1 at 2026-09-10 14:00:32 +0000
Processing by ProductsController#show as HTML
  Parameters: {"id" => "1"}
  Product Load (0.3ms)  SELECT "products".* FROM "products" WHERE "products"."id" = 1 LIMIT 1
  Rendering products/show.html.erb within layouts/application
  Variant Load (0.2ms)  SELECT "variants".* FROM "variants" WHERE "variants"."product_id" = 1
  Rendered products/show.html.erb within layouts/application (Duration: 8.4ms | GC: 0.0ms)
Completed 200 OK in 42ms (Views: 11.1ms | ActiveRecord: 23.9ms (2 queries, 0 cached) | GC: 0.0ms)

Two queries show one product with all of its variants:

1. SELECT "products".* FROM "products" WHERE "products"."id" = 1 LIMIT 1
2. SELECT "variants".* FROM "variants" WHERE "variants"."product_id" = 1

With config.active_record.query_log_tags_enabled off, the same lines carry bind placeholders and a list of values instead:

  Product Load (0.9ms)  SELECT "products".* FROM "products" WHERE "products"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  Variant Load (0.9ms)  SELECT "variants".* FROM "variants" WHERE "variants"."product_id" = $1  [["product_id", 1]]

Looped Lazy Loading

Lazy loading has been convenient so far. Because the query is implicit, nothing in the controller needs to change when the view stops showing variants. Now take ProductsController#index, which lists every product with its variants, implemented the same way:

# app/controllers/products_controller.rb
class ProductsController < ApplicationController
  def index
    @products = Product.all
  end
end

<%# app/views/products/index.html.erb %>
<h1>Products</h1>
 
<% @products.each do |product| %>
<article>
  <h1><%= product.title %></h1>
 
  <ul>
    <% product.variants.each do |variant| %>
      <li><%= variant.description %></li>
    <% end %>
  </ul>
</article>
<% end %>

The controller now hands the view a list of products instead of one, and the view lazy loads the variants of each product in turn. It works, but the query count is now N+1. With three products in the database, the request runs four queries instead of two:

Started GET "/products" for 127.0.0.1 at 2026-09-10 14:00:33 +0000
Processing by ProductsController#index as HTML
  Rendering products/index.html.erb within layouts/application
  Product Load (24.2ms)  SELECT "products".* FROM "products"
  Variant Load (1.2ms)  SELECT "variants".* FROM "variants" WHERE "variants"."product_id" = 1
  Variant Load (0.6ms)  SELECT "variants".* FROM "variants" WHERE "variants"."product_id" = 2
  Variant Load (0.4ms)  SELECT "variants".* FROM "variants" WHERE "variants"."product_id" = 3
  Rendered products/index.html.erb within layouts/application (Duration: 59.8ms | GC: 0.0ms)
Completed 200 OK in 72ms (Views: 26.7ms | ActiveRecord: 39.1ms (4 queries, 0 cached) | GC: 0.0ms)

The first query comes from the explicit Product.all in the controller. The other three run lazily while the view loops over the products: one per product. N is the number of products and the added one is the query that fetched them, so with N = 3 the request runs N + 1 = 3 + 1 = 4 queries. Three products are harmless; the count grows with the table, and the same page with 100 products runs 101 queries.

Eager Loading Associations: includes, preload, and eager_load

Instead of a query count that grows with the number of products, the view needs a fixed number of queries. Preload the variants in the controller, before the view renders:

# app/controllers/products_controller.rb
class ProductsController < ApplicationController
  def index
    @products = Product.all.includes(:variants)
  end
end

includes tells ActiveRecord which associations the view will touch, so it fetches the variants of all requested products in one extra query:

Started GET "/products" for 127.0.0.1 at 2026-09-10 14:00:34 +0000
Processing by ProductsController#index as HTML
  Product Load (26.8ms)  SELECT "products".* FROM "products"
  Variant Load (0.9ms)  SELECT "variants".* FROM "variants" WHERE "variants"."product_id" IN (1, 2, 3)
Completed 200 OK in 71ms (Views: 20.7ms | ActiveRecord: 42.2ms (2 queries, 0 cached) | GC: 0.0ms)

The query count is back to 2, and it stays there as products are added. includes is one of three eager-loading methods, and it chooses between the other two for you:

Product.preload(:variants).to_a
# SELECT "products".* FROM "products"
# SELECT "variants".* FROM "variants" WHERE "variants"."product_id" IN (1, 2, 3)
 
Product.eager_load(:variants).to_a
# SELECT "products"."id" AS t0_r0, "products"."title" AS t0_r1, ... "variants"."id" AS t1_r0, ... FROM "products" LEFT OUTER JOIN "variants" ON "variants"."product_id" = "products"."id"
 
Product.includes(:variants).where(variants: { name: "Variant 1" }).to_a
# SELECT "products"."id" AS t0_r0, ... FROM "products" LEFT OUTER JOIN "variants" ON "variants"."product_id" = "products"."id" WHERE "variants"."name" = 'Variant 1'
 
Product.joins(:variants).distinct.to_a
# SELECT DISTINCT "products".* FROM "products" INNER JOIN "variants" ON "variants"."product_id" = "products"."id"

preload always runs a separate query per association, with WHERE … IN (…). eager_load always builds one LEFT OUTER JOIN and hydrates products and variants from that single result, which is why every column of both tables appears in the SELECT, aliased t0_r0, t1_r0, and so on. includes picks preload unless the query filters on or references the association, in which case it switches to the JOIN: includes(:variants).where(variants: { name: "Variant 1" }) and includes(:variants).references(:variants) both produce the eager_load shape. joins(:variants) on its own is not eager loading. It runs an INNER JOIN to filter products (distinct removes the duplicates the join creates) but loads no variants, so touching product.variants afterwards is still an N+1. The Rails guide on eager loading covers nested and conditional forms.

Eager loading needs care of its own. has_one associations are safe to eager load, but a has_many reached through several joined many-to-many tables can pull far too many records into memory, and that crashes an app as surely as a bad N+1 does. When both the N+1 and the eager load hurt, rethink the query itself: maybe you only need a COUNT, which belongs in the query; maybe the pagination limit is too generous; maybe the work belongs in a background job.

Lazy or Eager? What the Numbers Say

In most situations, fetching all associated records in one query is much faster than lazy loading them, and the gap widens with the number of parents. Measured with benchmark-ips 2.15.1 against PostgreSQL 17 in a sibling container, ten variants per product, the loop reading each variant’s name and rendering nothing:

• 3 products: lazy 1.09 ms vs includes 0.77 ms (1.4× faster).
• 10 products: 3.21 ms vs 1.61 ms (2.0×).
• 100 products: 57.3 ms vs 12.7 ms (4.5×).
• 1,000 products: 764 ms vs 130 ms (5.9×).

Lazy loading gives the view flexibility without touching the controller, but a good rule of thumb is to let the controller load the data and let the view render it. Lazy loading from the view is fine for a page that shows one record and its associations, like ProductsController#show, and it can suit several views that need different data from the same controller.

Detecting N+1 Queries: Bullet, Prosopite, and strict_loading

Reading the development log catches most N+1s, and the (4 queries, 0 cached) count on the Completed line is a quick tell. Three tools automate the search; all three were exercised on Rails 8.1.3.1.

Bullet (8.2.0) watches which associations each request loads and reports the ones you should have eager loaded, plus the ones you eager loaded and never used. Add gem "bullet", group: :development to the Gemfile and enable it in config/environments/development.rb:

# config/environments/development.rb
config.after_initialize do
  Bullet.enable = true
  Bullet.rails_logger = true
  Bullet.add_footer = true
end

With that in place, the lazy /products request writes a notification to the log and a footer to the page:

GET /products
USE eager loading detected
  Product => [:variants]
  Add to your query: .includes([:variants])
Call stack

Prosopite (2.2.0) takes a different route. It fingerprints the SQL of every query inside a scan and flags a fingerprint that repeats, rather than tracking association loads. The two tools catch slightly different sets of problems, so they can run side by side. On PostgreSQL it needs the pg_query gem for the fingerprinting; without it, the first scan raises a LoadError telling you to add the gem. Add gem "prosopite" and gem "pg_query", configure it, and wrap each request in a scan:

# config/environments/development.rb
config.after_initialize do
  Prosopite.rails_logger = true
  Prosopite.raise = true
end

# app/controllers/application_controller.rb
class ApplicationController < ActionController::Base
  unless Rails.env.production?
    around_action :n_plus_one_detection
 
    def n_plus_one_detection
      Prosopite.scan
      yield
    ensure
      Prosopite.finish
    end
  end
end

Prosopite.scan { … } also takes a block, which suits a job or a script.

strict_loading (Rails 6.1+) is built in. It does not detect N+1s; it forbids the lazy load that would cause one, so the code fails loudly instead of running the extra queries. Mark an association, a query, a record, or a whole model:

# app/models/user.rb
class User < ApplicationRecord
  has_many :projects, strict_loading: true
end

Product.strict_loading.first.variants.to_a
# => ActiveRecord::StrictLoadingViolationError: `Product` is marked for strict_loading. The Variant association named `:variants` cannot be lazily loaded.
 
variant = Variant.first
variant.strict_loading!
variant.product
# => ActiveRecord::StrictLoadingViolationError: `Variant` is marked for strict_loading. The Product association named `:product` cannot be lazily loaded.

self.strict_loading_by_default = true in a model applies it to every query on that model, and config.active_record.action_on_strict_loading_violation = :log (the default is :raise) logs violations instead of raising, which is how to introduce it in an existing app. One detail to know: user.projects.size on a strict-loaded has_many does not raise. It runs a COUNT(*) without loading the association.

ActiveRecord::StrictLoadingViolationError: User is marked for strict_loading

ActiveRecord::StrictLoadingViolationError: `User` is marked for strict_loading. The Project association named `:projects` cannot be lazily loaded.

Cause: Code accessed an association lazily on a record that is marked for strict loading: User.first.projects.to_a with has_many :projects, strict_loading: true, a record loaded through strict_loading or strict_loading_by_default, or one that had strict_loading! called on it. Rails refuses to run the extra query because in a loop it would be an N+1.

Fix: Eager load the association on the query that loaded the parent records:

User.includes(:projects).first.projects.to_a.size
# => 2

preload works the same way. While migrating an existing app, set config.active_record.action_on_strict_loading_violation = :log so the violations show up in the log without breaking pages, then remove the setting once they are gone.

Bullet: USE eager loading detected

USE eager loading detected
  Product => [:variants]
  Add to your query: .includes([:variants])
Call stack

Cause: A request loaded a collection (Product.all) and then touched the same association (variants) on record after record. Bullet saw the repeated loads and names the parent, the association, and the call site. With Bullet.raise = true the same text arrives as a Bullet::Notification::UnoptimizedQueryError instead of a log entry, which is the setting to use in the test suite.

Fix: Add exactly what the message prints, .includes([:variants]), to the query that loads the parents. The counterpart notification, AVOID eager loading detected with Remove from your query: .includes([:variants]), means the opposite: an includes nobody uses. Bullet also reports Need Counter Cache with Active Record size when a size call runs a COUNT per record; the answer to that one is a counter cache.

Prosopite::NPlusOneQueriesError: N+1 queries detected

Prosopite::NPlusOneQueriesError: N+1 queries detected (0.3ms):
  SELECT "variants".* FROM "variants" WHERE "variants"."product_id" = 1
  SELECT "variants".* FROM "variants" WHERE "variants"."product_id" = 2
  SELECT "variants".* FROM "variants" WHERE "variants"."product_id" = 3
Call stack:

Cause: Within one scan, several queries shared a fingerprint, differing only in their values. That is the signature of a lazy load inside a loop, whether it goes through an association or through repeated find calls.

Fix: Eager load the association on the parent query, exactly as for Bullet. If the scan raises LoadError on PostgreSQL instead, add gem "pg_query"; Prosopite needs it to fingerprint queries.

Query Shaping in ActiveRecord: select, pluck, load, and load_async

Sometimes breaking a query into two steps both improves performance and simplifies it. If you are wrestling a subset of records into a mind-bending query, fetching the IDs of that subset first and feeding them into a second, simpler query can remove joins and subqueries altogether. The rest of this section is about the other half of query shaping: asking the database for less.

Select Only the Columns You Use: select and pluck

By default, ActiveRecord loads every column of every table involved in a query (SELECT *), whether or not the code reads them. On wide tables that is wasted I/O and, more visibly, wasted memory: loading full rows to read one attribute allocates big chunks of it. select narrows the columns, and pluck skips model instantiation entirely and returns plain values:

User.select(:id, :email).each { |user| user.email }
# SELECT "users"."id", "users"."email" FROM "users"
 
User.pluck(:id)
# SELECT "users"."id" FROM "users"
 
User.distinct.pluck(:country)
# SELECT DISTINCT "users"."country" FROM "users"

A record loaded with select only has the attributes you asked for; reading another one raises:

User.select(:id).first.email
# => ActiveModel::MissingAttributeError: missing attribute 'email' for User

It is easy to forget the SELECT * default as queries evolve, and to end up loading a lot of data nobody uses. When a query changes, ask whether the code still needs every column it loads, or whether a select (or a pluck) fits the job better.

count vs size: Avoiding Redundant Queries

This is sometimes confused with the N+1 problem, but it is a different one: innocent-looking code that queries a table it already has in memory. count always issues a SELECT COUNT(*), even when the records are loaded. Take an index action that sets @users = User.where(country: "Germany") and a view that lists them and then counts them:

<%# app/views/users/index.html.erb %>
<ul>
<% @users.each do |user| %>
  <li><%= user.name %></li>
<% end %>
</ul>
 
<h2>Number of users: <%= @users.count %></h2>

  User Load (1.8ms)  SELECT "users".* FROM "users" WHERE "users"."country" = 'Germany'
  User Count (0.8ms)  SELECT COUNT(*) FROM "users" WHERE "users"."country" = 'Germany'
Completed 200 OK in 74ms (Views: 27.1ms | ActiveRecord: 13.2ms (2 queries, 0 cached) | GC: 0.0ms)

The second query is redundant; the users are already in memory. Swap count for size:

<%# app/views/users/index.html.erb %>
<ul>
<% @users.each do |user| %>
  <li><%= user.name %></li>
<% end %>
</ul>
 
<h2>Number of users: <%= @users.size %></h2>

  User Load (2.1ms)  SELECT "users".* FROM "users" WHERE "users"."country" = 'Germany'
Completed 200 OK in 105ms (Views: 60.6ms | ActiveRecord: 14.0ms (1 query, 0 cached) | GC: 0.0ms)

The rule: count always queries. size counts in memory when the relation is loaded and runs COUNT(*) when it is not. length always loads the records and counts them in Ruby.

The order matters, too. Say a live search shows the number of matches at the top of the page, followed by the matches themselves:

@users = User.where("email ILIKE ?", search)
@total_users = @users.size

Nothing has loaded the relation when size runs, so it issues SELECT COUNT(*), and the view’s loop then issues the SELECT for the rows: two queries. Calling load first turns size into an in-memory count:

@users = User.where("email ILIKE ?", search).load
@total_users = @users.size

One query, and the view renders from the loaded records.

Aggregate in the Database, Not in Ruby

Databases are built to aggregate over big sets. Aggregating in Ruby means fetching every row, holding it in memory, and computing with high-level code, and it does not scale. You are aggregating in Ruby whenever you call max, min, or sum on a collection of records or plucked values. ActiveRecord maps the same operations to SQL functions:

Number.pluck(:number).sum
# SELECT "numbers"."number" FROM "numbers"
 
Number.sum(:number)
# SELECT SUM("numbers"."number") FROM "numbers"
 
Number.average(:number)
# SELECT AVG("numbers"."number") FROM "numbers"

The first form loads 100 rows to add them up; the other two return one number. For combined or unusual aggregates, a bit of raw SQL is fine:

sql = "SELECT AVG(number), STDDEV(number), VARIANCE(number) FROM numbers"
results = ActiveRecord::Base.with_connection { |conn| conn.select_all(sql) }
results.to_a
# => [{"avg" => 0.505e2, "stddev" => 0.290114919758820171e2, "variance" => 0.8416666666666666667e3}]

PostgreSQL’s function is VARIANCE; VAR(number) raises PG::UndefinedFunction: ERROR: function var(integer) does not exist. ActiveRecord::Base.connection.execute(sql) still works on 8.1, but with_connection (Rails 7.2+) checks the connection back into the pool when the block ends.

The counter-case is a calculation that is cheaper in memory than as repeated queries. To find which of a handful of countries have no users yet, one exists? per country runs one query per country:

countries = ["Germany", "UK", "Norway", "Netherlands", "France"]
 
countries.each do |country|
  puts country unless User.where(country: country).exists?
end
# SELECT 1 AS one FROM "users" WHERE "users"."country" = 'Germany' LIMIT 1
# SELECT 1 AS one FROM "users" WHERE "users"."country" = 'UK' LIMIT 1
# SELECT 1 AS one FROM "users" WHERE "users"."country" = 'Norway' LIMIT 1
# SELECT 1 AS one FROM "users" WHERE "users"."country" = 'Netherlands' LIMIT 1
# SELECT 1 AS one FROM "users" WHERE "users"."country" = 'France' LIMIT 1

One DISTINCT query and an array subtraction give the same answer:

existing_countries = User.distinct.pluck(:country)
puts countries - existing_countries
# SELECT DISTINCT "users"."country" FROM "users"

Rails also makes optional query parts cheap. If two similar queries differ slightly — in a before_action that serves several actions, say, or between an index and its search results — you do not have to choose between two queries and one query that over-fetches for both. Nil and empty arguments are no-ops:

# app/models/user.rb
class User < ApplicationRecord
  has_many :projects
 
  def search_projects(before_date: nil, optional_selects: [], optional_eager_loads: [], only_named: nil)
    optional_before_date_constraint = ["projects.created_at < ?", before_date] if before_date
    optional_name_constraint = { name: only_named } if only_named
 
    projects
      .includes(optional_eager_loads)
      .select(optional_selects)
      .where(optional_before_date_constraint)
      .where(optional_name_constraint)
  end
end

user.search_projects.to_sql
# => SELECT "projects".* FROM "projects" WHERE "projects"."user_id" = 1
 
user.search_projects(optional_selects: [:id, :name], before_date: Time.utc(2030), only_named: "Project 1").to_sql
# => SELECT "projects"."id", "projects"."name" FROM "projects" WHERE "projects"."user_id" = 1 AND (projects.created_at < '2030-01-01 00:00:00') AND "projects"."name" = 'Project 1'

where(nil), includes([]), select([]), and joins(nil) all leave the query untouched:

User.where(nil).includes([]).select([]).joins(nil).to_sql
# => SELECT "users".* FROM "users"

In the other direction from load, there is load_async (Rails 7.0+), which schedules a query on a background thread so several independent queries in one action can run at the same time. It is only asynchronous when an executor is configured:

users = User.where(country: "Germany").load_async
users.scheduled?
# => false

With the default config.active_record.async_query_executor = nil, load_async runs the query synchronously on the calling thread. Set it to :global_thread_pool or :multi_thread_pool and the same call returns true and the query runs in the background. Because this uses threads and checks out extra database connections, it can exhaust the connection pool if you are not careful; size the pool for it.

PG::UndefinedColumn: ERROR: column users.county does not exist

ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column users.county does not exist
LINE 1: SELECT "users".* FROM "users" WHERE "users"."county" = 'Germ...
                                            ^
HINT:  Perhaps you meant to reference the column "users.country".

Cause: A misspelled attribute in where, order, pluck, or select compiles straight into SQL, and PostgreSQL rejects the column. Here it was User.where(county: "Germany"). Rails wraps the driver error in ActiveRecord::StatementInvalid; the HINT line is PostgreSQL guessing the column you meant. A hash condition names the column qualified, users.county; order(:created) or pluck(:countries) print the unqualified form, column "countries" does not exist. The same error appears after a rename or drop migration when code, or a stale schema cache, still references the old name, and when the migration has not run in the environment that raises.

Fix: Use the column PostgreSQL suggests. After a schema change, run bin/rails db:migrate in that environment and restart the app so the schema cache reloads.

Database Indexing

Indexing is the most important, and nearly universally necessary, database performance tool. Indexes are separate data structures, typically B-trees, that let the database find matching rows without scanning the whole table, turning an O(n) search into O(log n). Every index also has to be maintained on every write, so indexing columns nobody filters on harms performance instead of helping it.

The trick is to identify the columns and associations your application filters, joins, and sorts on most often, and to index those. Revisit that profile periodically: the indexing needs of an app change as the user base, the codebase, and the feature set evolve. Reviewing indexes in a quiet period beats doing it while an influx of traffic is crashing the site.

Reading Query Plans with explain

A query plan shows how the database will execute a query: which index it uses, if any, how many rows it expects to touch, and how it joins tables. Calling explain on any relation asks PostgreSQL for the plan of that query:

User.where(id: 1).explain

EXPLAIN SELECT "users".* FROM "users" WHERE "users"."id" = 1
                               QUERY PLAN
-------------------------------------------------------------------------
 Index Scan using users_pkey on users  (cost=0.28..8.30 rows=1 width=61)
   Index Cond: (id = 1)
(2 rows)

A lookup by primary key uses the users_pkey index; this query is as fast as it gets. On Rails 8.1, explain returns an ActiveRecord::Relation::ExplainProxy that prints the plan, and it passes database options through: on PostgreSQL, explain(:analyze) runs the query and adds real timings and row counts, explain(:analyze, :buffers) adds buffer usage, and explain.pluck(:email) explains the pluck instead of the relation. The query hub covers the proxy and its options in depth; here the question is what the plan says. The line to look for is Seq Scan on a large table: the database is reading every row because no index fits the condition. This table holds 3,000 users and has no index on name:

User.where(name: "User 42").explain(:analyze)

EXPLAIN (ANALYZE) SELECT "users".* FROM "users" WHERE "users"."name" = 'User 42'
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Seq Scan on users  (cost=0.00..72.50 rows=1 width=61) (actual time=0.004..0.135 rows=1 loops=1)
   Filter: ((name)::text = 'User 42'::text)
   Rows Removed by Filter: 2999
 Planning Time: 0.014 ms
 Execution Time: 0.137 ms
(5 rows)

Rows Removed by Filter: 2999 is the cost of the missing index: PostgreSQL read all 3,000 rows to return one. After add_index :users, :name, the same query becomes an index scan:

EXPLAIN (ANALYZE) SELECT "users".* FROM "users" WHERE "users"."name" = 'User 42'
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_users_on_name on users  (cost=0.28..8.30 rows=1 width=61) (actual time=0.004..0.004 rows=1 loops=1)
   Index Cond: ((name)::text = 'User 42'::text)
 Planning Time: 0.020 ms
 Execution Time: 0.008 ms
(4 rows)

A condition that matches many rows gets a different plan. A quarter of the demo’s users are in Germany, so PostgreSQL chooses a bitmap scan, and it reads the composite (country, guest) index for a query on country alone, because the leading column of a composite index serves a query by itself:

User.where(country: "Germany").explain

EXPLAIN SELECT "users".* FROM "users" WHERE "users"."country" = 'Germany'
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on users  (cost=14.09..58.47 rows=750 width=61)
   Recheck Cond: ((country)::text = 'Germany'::text)
   ->  Bitmap Index Scan on index_users_on_country_and_guest  (cost=0.00..13.90 rows=750 width=0)
         Index Cond: ((country)::text = 'Germany'::text)
(4 rows)

Run explain on the slow queries your monitoring surfaces, on your production data or a copy of it: the planner’s choices depend on table statistics, so a tiny development database gives different plans from production.

Adding an Index in a Rails Migration: Single, Composite, Partial, and Concurrent

add_index in a migration covers every common shape. A single-column index for a column the app filters on:

class AddIndexToUsersOnCountry < ActiveRecord::Migration[8.1]
  def change
    add_index :users, :country
  end
end

A composite index for a query that filters on two columns together, such as User.where(country: "Germany", guest: false). Put the most selective column, or the one queried on its own, first:

class AddIndexToUsersOnCountryAndGuest < ActiveRecord::Migration[8.1]
  def change
    add_index :users, [:country, :guest]
  end
end

A unique index, which doubles as a constraint the database enforces:

class AddUniqueIndexToUsersOnEmail < ActiveRecord::Migration[8.1]
  def change
    add_index :users, :email, unique: true
  end
end

A partial index covers only the rows that match a condition. Say a core of frequent visitors with accounts makes up the lion’s share of your traffic but a small minority of your users; an index restricted to non-guests stays small and fast for the queries that matter:

class AddPartialIndexToUsersOnGuest < ActiveRecord::Migration[8.1]
  def change
    add_index :users, :guest, where: "(guest = false)" # the `where: condition` here makes this a partial index.
  end
end

On PostgreSQL, a plain CREATE INDEX locks the table against writes while it builds, which on a big table means downtime. algorithm: :concurrently builds the index without that lock, at the price of running outside the migration’s transaction:

class AddIndexToUsersOnNameConcurrently < ActiveRecord::Migration[8.1]
  disable_ddl_transaction!
 
  def change
    add_index :users, :name, algorithm: :concurrently
  end
end

The SQL these five migrations ran, from the development log:

CREATE INDEX "index_users_on_country" ON "users" ("country")
CREATE INDEX "index_users_on_country_and_guest" ON "users" ("country", "guest")
CREATE UNIQUE INDEX "index_users_on_email" ON "users" ("email")
CREATE INDEX "index_users_on_guest" ON "users" ("guest") WHERE (guest = false)
CREATE INDEX CONCURRENTLY "index_users_on_name" ON "users" ("name")

db/schema.rb records the concurrent index as a plain t.index ["name"], name: "index_users_on_name"; the algorithm is a property of the build, not of the index. For the full set of lock-safety rules, and a gem that stops an unsafe migration before it runs, see our Strong Migrations guide; for how migrations themselves work, see Dissecting Rails Migrations.

PG::ActiveSqlTransaction: ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

bin/rails aborted!
StandardError: An error has occurred, this and all later migrations canceled: (StandardError)
 
PG::ActiveSqlTransaction: ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Cause: Rails wraps each migration in a transaction so a failure rolls the whole migration back, and PostgreSQL refuses to build an index concurrently inside a transaction. The migration used algorithm: :concurrently without opting out of that transaction.

Fix: Add disable_ddl_transaction! at the top of the migration class, as in the migration in the previous section. Keep one concurrent index per migration: without the transaction, a failed build leaves an INVALID index behind that you have to drop by hand before retrying. The migration mechanics guide’s entry for this error covers the rollback side.

Performance Profiling for Your Ruby on Rails App

Everything so far is a category of fix. Which fixes deserve your time is a different question, and the answer is never “all of them, line by line.” Traffic is not evenly distributed across an app, and neither is database load: a few actions and a few queries account for most of it.

Your local server output is a fine place to spot an N+1 or a wide SELECT, but you are one user on a small database. It shows what a query does, not what it costs. The query to fix first is rarely the slowest single one; it is the one with the largest total time, count times duration, across real traffic. A page that takes two seconds but loads twice a day matters less than a 150 ms products page that eats 15% of your database time. On the database side, pghero (4.0.1; gem "pghero" and mount PgHero::Engine, at: "pghero" in the routes) shows PostgreSQL’s own view of the same question: index hit rates, pg_stat_statements, and unused indexes.

Everything in this guide starts from knowing which query is slow in production, not on your laptop. AppSignal’s Slow Queries ranks every sql.active_record query by impact, the time it costs you across duration and frequency together, and links each one to the actions that trigger it, so you fix the query costing your users the most time first. For the full find, diagnose, fix, and verify loop inside AppSignal, follow Finding the Slow Query Killing Your Rails App.

Batching, Bulk Writes, and Background Jobs

The database does the same work whether a query runs in a request or in a job. What batching and bulk writes change is how many round trips and how much memory that work takes, and background jobs change when it happens and who waits for it.

Batching with find_each and in_batches

Iterating over a large result set with each loads every matching row into memory at once:

User.where(country: "Germany").each do |user|
  # ...
end
# SELECT "users".* FROM "users" WHERE "users"."country" = 'Germany'

find_each walks the same rows in batches of 1,000, ordered by primary key, each batch continuing from the last id seen:

User.where(country: "Germany").find_each do |user|
  # ...
end
# SELECT "users".* FROM "users" WHERE "users"."country" = 'Germany' ORDER BY "users"."id" ASC LIMIT 1000

Over the whole table of 3,000 users, that is four queries, the last one returning nothing:

User.find_each do |user|
  # ...
end
# SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT 1000
# SELECT "users".* FROM "users" WHERE "users"."id" > 1000 ORDER BY "users"."id" ASC LIMIT 1000
# SELECT "users".* FROM "users" WHERE "users"."id" > 2000 ORDER BY "users"."id" ASC LIMIT 1000
# SELECT "users".* FROM "users" WHERE "users"."id" > 3000 ORDER BY "users"."id" ASC LIMIT 1000

The batch size is an argument:

User.find_each(batch_size: 5000) do |user|
  # ...
end
# SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT 5000

An older version of this advice spelled it find_each(:batch_size: 5000), which is a SyntaxError. find_in_batches yields each batch as an array, and in_batches yields it as a relation, so you can chain update_all or delete_all onto each batch without loading the records at all:

User.where(country: "Germany").find_in_batches(batch_size: 500) do |batch|
  # batch is an Array of up to 500 users
end
 
User.where(country: "Germany").in_batches(of: 500) do |relation|
  # relation is an ActiveRecord::Relation of up to 500 users
end
 
User.in_batches(of: 1000).update_all(guest: false)
# SELECT "users"."id" FROM "users" ORDER BY "users"."id" ASC LIMIT 1 OFFSET 999
# UPDATE "users" SET "guest" = FALSE WHERE "users"."id" <= 1000
# SELECT "users"."id" FROM "users" WHERE "users"."id" > 1000 ORDER BY "users"."id" ASC LIMIT 1 OFFSET 999
# UPDATE "users" SET "guest" = FALSE WHERE "users"."id" > 1000 AND "users"."id" <= 2000

The same idea applies to pagination: limit and offset get slower as the offset grows, because the database still walks past every skipped row, while paging by key, as find_each does, stays flat.

Bulk Writes with insert_all, upsert_all, and delete_all

create accepts an array of hashes, but that is a convenience, not a bulk operation. It runs one INSERT, in its own transaction, per record:

users = [
  { name: "Milap", email: "milap@country.com", country: "Germany" },
  { name: "Aastha", email: "aastha@country.com", country: "Germany" }
]
 
User.create(users)

    TRANSACTION (0.1ms)  BEGIN
    User Create (0.3ms)  INSERT INTO "users" ("name", "email", "country", "guest", "created_at", "updated_at") VALUES ('Milap', 'milap@country.com', 'Germany', FALSE, '2026-09-10 13:58:16.218968', '2026-09-10 13:58:16.218968') RETURNING "id"
    TRANSACTION (0.2ms)  COMMIT
    TRANSACTION (0.0ms)  BEGIN
    User Create (0.2ms)  INSERT INTO "users" ("name", "email", "country", "guest", "created_at", "updated_at") VALUES ('Aastha', 'aastha@country.com', 'Germany', FALSE, '2026-09-10 13:58:16.220122', '2026-09-10 13:58:16.220122') RETURNING "id"
    TRANSACTION (0.2ms)  COMMIT

insert_all writes the same rows in one statement, filling the timestamps with CURRENT_TIMESTAMP:

User.insert_all(users)
# INSERT INTO "users" ("name","email","country","created_at","updated_at") VALUES ('Milap', 'milap@country.com', 'Germany', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ('Aastha', 'aastha@country.com', 'Germany', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) ON CONFLICT  DO NOTHING RETURNING "id"

upsert_all inserts or updates in one statement, keyed on a unique index:

User.upsert_all(users, unique_by: :email)
# INSERT INTO "users" ("name","email","country","created_at","updated_at") VALUES ('Milap', 'milap@country.com', 'Germany', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP), ... ON CONFLICT ("email") DO UPDATE SET updated_at=(CASE WHEN ... END),"name"=excluded."name","country"=excluded."country" RETURNING "id"

Neither insert_all nor upsert_all runs validations or callbacks; they hand the rows straight to the database, so the data has to be right before the call. The query hub covers unique_by and the ON CONFLICT behavior behind both. Deletes and updates have the same split. A loop that calls delete on each record issues one DELETE per row:

users = User.where(country: "Germany")
 
users.each do |user|
  user.delete
end
# SELECT "users".* FROM "users" WHERE "users"."country" = 'Germany'
# DELETE FROM "users" WHERE "users"."id" = 4
# DELETE FROM "users" WHERE "users"."id" = 8
# ... one DELETE per row

delete_all on the relation is one statement:

User.where(country: "Germany").delete_all
# DELETE FROM "users" WHERE "users"."country" = 'Germany'

update_all is the UPDATE equivalent, with the same caveat about callbacks; the advanced-queries guide covers it in more depth:

User.where(country: "Germany").update_all(guest: true)
# UPDATE "users" SET "guest" = TRUE WHERE "users"."country" = 'Germany'

Consider a site that sells relatively inexpensive items in potentially huge quantities. A school orders tens of thousands of pencils, and the system creates a PurchaseItem for every single pencil. Creating those rows one at a time in the request locks the database with thousands of writes and keeps the customer waiting. Building the hashes in a loop and handing them to one insert_all writes to the database once, and doing that in a background job takes it out of the request entirely.

Keep Transactions Short

SQL transactions make a set of changes atomic: either every row is updated or the whole thing rolls back, and the database stays consistent either way. ActiveRecord::Base.transaction bundles a batch of changes into one:

ActiveRecord::Base.transaction do
  (1..1000).each do |i|
    User.create(name: "Les Claypool", email: "les#{i}@example.com", country: "UK")
  end
end

That is one BEGIN, a thousand INSERTs, and one COMMIT. Big transactions have legitimate uses. But a long one holds its locks and undo data for its whole duration, slowing the database down for everyone else. The first sign is time spent in commit transaction events. Barring configuration issues on the database itself, the fix is to break the transaction into smaller chunks, one transaction per chunk, so each holds its locks briefly:

(1..1000).each_slice(100) do |batch|
  ActiveRecord::Base.transaction do
    batch.each do |i|
      User.create(name: "Les Claypool", email: "les#{i}@example.com", country: "UK")
    end
  end
end

Ten transactions of a hundred inserts each. in_batches gives the same shape for updates and deletes over existing rows.

Counter Caches

Showing product.variants.size in a list of products runs one COUNT(*) per product, an N+1 that eager loading does not fix. A counter cache stores the count on the parent row and keeps it current:

# app/models/variant.rb
class Variant < ApplicationRecord
  belongs_to :product, counter_cache: true
end

class AddVariantsCountToProducts < ActiveRecord::Migration[8.1]
  def change
    add_column :products, :variants_count, :integer, default: 0, null: false
  end
end

With the column in place, size reads it without a query, count still asks the database, and creating a variant increments the counter with an UPDATE right after the INSERT:

product = Product.first
Product.reset_counters(product.id, :variants)
 
product.variants.size
# => 10
product.variants.count
# => 10
# SELECT COUNT(*) FROM "variants" WHERE "variants"."product_id" = 1
 
product.variants.create!(name: "Extra")
# UPDATE "products" SET "variants_count" = COALESCE("products"."variants_count", 0) + 1 WHERE "products"."id" = 1

Product.reset_counters backfills the column for existing rows. Scoped counters, callbacks, and the trade-offs get the full treatment in ActiveRecord’s Counter Cache.

Background jobs are where the big database work belongs. A job is free to run a resource-intensive task — building a bulk insert from a CSV, say, or spreading thousands of writes over time with find_each — without a user waiting on it. It keeps the request threads free, too. If you are processing an upload on the request thread, something is wrong; the less obvious candidates are the reports, exports, and recalculations that happen to be fast enough today. Rails 8 ships Solid Queue as the default back end, which stores the jobs in the database, so a job that hammers the primary still needs the batching and bulk-write discipline from this section.

Read Replicas

Background jobs and bulk writes go a long way, but eventually a growing app asks too much of one database. Write throughput is usually fine at that point, because most apps read far more than they write, but the primary is under constant read pressure and response times creep up. The next step is a read replica: a copy of the primary that the app reads from, so the primary spends its I/O on writes. Read replicas can be added and removed without touching the primary, which also opens the door to scaling reads automatically. Ideally, large operations then run in background jobs that read from a replica, build a bulk insert or upsert, and make one atomic write to the primary.

Setting Up a Read Replica with connects_to

Rails has supported replicas since 6.0 through multiple databases. Declare the replica in config/database.yml; replica: true marks it read-only for Rails and keeps db:create and db:migrate away from it:

# config/database.yml
production:
  primary:
    adapter: postgresql
    encoding: unicode
    url: <%= ENV["DATABASE_URL"] %>
  primary_replica:
    adapter: postgresql
    encoding: unicode
    url: <%= ENV["DATABASE_REPLICA_URL"] %>
    replica: true

Then tell ApplicationRecord which configuration serves which role:

# app/models/application_record.rb
class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class
 
  connects_to database: { writing: :primary, reading: :primary_replica }
end

connects_to needs both entries in every environment where it runs. Either give development and test a primary_replica entry that points at the same database as primary (the demo app for this guide does that, and its manual switching works), or guard the call with if Rails.env.production?.

Switching roles by hand is a block:

ActiveRecord::Base.connected_to(role: :reading) do
  ActiveRecord::Base.current_role
  # => :reading
  User.count # runs on primary_replica
end

Rails also switches automatically per request, based on the HTTP verb. Enable the middleware in config/environments/production.rb:

# config/environments/production.rb
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

GET and HEAD requests then run under the reading role and everything else under the writing role. The delay keeps a session on the primary for two seconds after it writes, so a user does not read stale data from a replica that has not caught up with their own change. Booted with these lines, the demo app answered a GET with role=reading and a POST with role=writing.

Replica Gotchas: Writes in Read-Only Requests

Automatic switching assumes that GET requests do not write. Any write in an index or show action, a counter, a “last seen at” timestamp, a lazily created record, now runs against the replica and raises. When the write is legitimate, switch the connection for that block:

ActiveRecord::Base.connected_to(role: :writing) do
  User.create!(name: "Written on GET", email: "get-#{SecureRandom.hex(4)}@example.com", country: "Germany")
end

This works inside a request that is already under the reading role; the block nests. Two structural cases need the same treatment. Database-backed sessions save on every request, including GETs, so a session store or an authentication library that writes a session record has to do so under the writing role. An API that speaks only POST, such as GraphQL, gets the writing role for every request from the middleware, so it has to pick the role itself: reading for a request that contains only queries, writing for one that contains a mutation. The role names on Rails 7.1+ are ActiveRecord.reading_role and ActiveRecord.writing_role; the older ActiveRecord::Base.reading_role raises NoMethodError on 8.1.

connected_to is only allowed on ActiveRecord::Base or on the abstract class that called connects_to. Calling it on a model raises NotImplementedError: calling `connected_to` is only allowed on ActiveRecord::Base or abstract classes., and calling it on an abstract class that used establish_connection instead of connects_to raises NotImplementedError: calling `connected_to` is only allowed on the abstract class that established the connection.

ActiveRecord::ReadOnlyError: Write query attempted while in readonly mode

ActiveRecord::ReadOnlyError: Write query attempted while in readonly mode: INSERT INTO "users" ("name", "email", "country", "guest", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id"

Cause: An INSERT, UPDATE, or DELETE ran while the reading role was current. That can be an explicit block:

ActiveRecord::Base.connected_to(role: :reading) do
  User.create!(name: "X", email: "x@example.com", country: "Germany")
end

Or it can be the automatic database selector handling a GET request whose action writes. Every write path raises the same error, with its own statement after the colon: update!, update_all (UPDATE "users" SET "guest" = $1 WHERE "users"."country" = $2), and a raw execute("INSERT …"). Rails checks the SQL before sending it, so the replica never sees the write.

Fix: Move the write to a non-GET action, or wrap exactly that write in ActiveRecord::Base.connected_to(role: :writing) { … }.

Database Sharding At A High Level

Read replicas scale reads. When the primary cannot absorb the write load, or one server cannot hold the data, what is left is scaling horizontally by sharding: splitting the database into several databases distributed across several servers, and often across regions. There are two strategies. Vertical sharding puts different tables on different nodes; the separate database in the next section is exactly that. Horizontal sharding keeps the same schema on every node and splits the rows by a key. The key is where the design choices live. Geographical sharding splits by user location, functional sharding by business area (billing on one node, user content on another), and the common SaaS cut is by tenant, since a signed-in user of one tenant never needs another tenant’s rows.

Sharding is hard and, for some businesses, not possible at all. It adds development and maintenance overhead, sometimes a lot of it, and it is usually a one-way street: once several databases hand out the same IDs for different objects, un-sharding is difficult. If you lack the resources to take this on, you probably do not need it yet. Once query optimization, read replicas, and caching are exhausted, though, sharding is the step that multiplies I/O capacity.

A Separate Database for High-Volume Tables

Sometimes a separate database server for one part of the app is the whole answer. The candidates are the parts with a clear boundary: two features that barely overlap, or one database-heavy feature that few users touch. Audit logging in a high-frequency app is the classic case, high-volume data that is written constantly and read rarely. Go for a separate database if, and only if, there is a clear line between the parts of the app that will use each one.

Configure the second database, called log here, in config/database.yml. A log_default entry carries what the log database shares across environments, including where its migrations live:

# config/database.yml
default: &default
  adapter: postgresql
  encoding: unicode
  host: <%= ENV.fetch("DB_HOST", "localhost") %>
  username: <%= ENV.fetch("DB_USER", "postgres") %>
  password: <%= ENV["DB_PASSWORD"] %>
 
log_default: &log_default
  <<: *default
  migrations_paths: db/log_migrate
 
development:
  primary:
    <<: *default
    database: my_app_development
  log:
    <<: *log_default
    database: my_app_log_development
 
production:
  primary:
    <<: *default
    url: <%= ENV["DATABASE_URL"] %>
  log:
    <<: *log_default
    url: <%= ENV["DATABASE_LOG_URL"] %>

An abstract class connects to it, and the models that live there inherit from that class instead of ApplicationRecord:

# app/models/log_record.rb
class LogRecord < ActiveRecord::Base
  self.abstract_class = true
  establish_connection :log
end

# app/models/audit_log.rb
class AuditLog < LogRecord
end

AuditLog.create!(action: "login")
AuditLog.count
# => 1
# SELECT COUNT(*) FROM "audit_logs"

The generators keep working; bin/rails g model AuditLog action:string --database log writes the migration to db/log_migrate and generates LogRecord for you, in the newer form connects_to database: { writing: :log }, which does the same job. bin/rails db:migrate migrates both databases; bin/rails db:migrate:primary and bin/rails db:migrate:log migrate one.

PG::UndefinedTable: ERROR: relation "audit_logs" does not exist

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "audit_logs" does not exist
LINE 1: SELECT COUNT(*) FROM "audit_logs"
                             ^

Cause: The database that the model’s connection points at has no such table. In order of likelihood: the migration has not run in this environment or against this database (bin/rails db:migrate, or db:migrate:log for a secondary database). Or the model connects to the wrong database — which is how the message was reproduced here, with AuditLog inheriting from ApplicationRecord instead of LogRecord while the table lives in the log database:

# app/models/audit_log.rb
class AuditLog < ApplicationRecord
end

Other causes are a self.table_name that does not match the migration, and a test database that was never prepared. The LINE 1: part shows the statement PostgreSQL rejected, with the caret under the table name; a Ghost.first on a model with no migration at all fails the same way.

Fix: Run the migration for the database that should hold the table, and make the model inherit from the abstract class that connects to it. If the table exists in the right database and the error persists, bin/rails db:migrate:status shows which migrations each database thinks it has run; Dissecting Rails Migrations explains the mechanics.

Setting Up Horizontal Sharding with connects_to shards

Horizontal sharding starts in config/database.yml again, with one writer and one replica per shard:

# config/database.yml
production:
  primary:
    adapter: postgresql
    encoding: unicode
    url: <%= ENV["DATABASE_URL"] %>
  primary_replica:
    adapter: postgresql
    encoding: unicode
    url: <%= ENV["DATABASE_REPLICA_URL"] %>
    replica: true
  primary_shard_one:
    adapter: postgresql
    encoding: unicode
    url: <%= ENV["DATABASE_SHARD_ONE_URL"] %>
  primary_shard_one_replica:
    adapter: postgresql
    encoding: unicode
    url: <%= ENV["DATABASE_SHARD_ONE_REPLICA_URL"] %>
    replica: true

connects_to shards: maps each shard name to its writing and reading configurations:

# app/models/application_record.rb
class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class
 
  connects_to shards: {
    default: { writing: :primary, reading: :primary_replica },
    shard_one: { writing: :primary_shard_one, reading: :primary_shard_one_replica }
  }
end

Switching shards is the same connected_to block, and it composes with roles. connected_to(shard: :shard_one, role: :reading) reads from the shard’s replica and blocks writes with the same ReadOnlyError as before:

ActiveRecord::Base.connected_to(shard: :shard_one) do
  ActiveRecord::Base.current_shard
  # => :shard_one
  User.count
  # => 0
end

For automatic switching per request, Rails provides a shard selector middleware. Its resolver is a lambda that maps the request to a shard name; this one looks the shard up by hostname in a Tenant model:

# config/environments/production.rb
config.active_record.shard_selector = { lock: true }
config.active_record.shard_resolver = ->(request) { Tenant.find_by!(host: request.host).shard }

Booted with these lines, the demo app served acme.example.com from shard_one and app.example.com from the default shard; an unknown host raised ActiveRecord::RecordNotFound from the resolver and got a 404. lock: true stops code inside the request from switching to another shard. The resolver is where your application’s notion of a tenant goes: a subdomain, a session, or a cookie you have to read before switching. The Rails guide’s sharding section covers the remaining options.

ActiveRecord::ConnectionNotDefined: No database connection defined for 'shard_one' shard.

ActiveRecord::ConnectionNotDefined: No database connection defined for 'shard_one' shard.

Cause: connected_to(shard: …) was called on a class whose connects_to never declared that shard. Either the shards live on another abstract class, which is how the message was reproduced here, with the shards declared on a ShardRecord while the code switched on ActiveRecord::Base:

# app/models/shard_record.rb
class ShardRecord < 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

ActiveRecord::Base.connected_to(shard: :shard_one) do
  User.count
end

Or the shard name is misspelled: connected_to(shard: :shard_two) against the configuration in the previous section raises the same error for 'shard_two'.

Fix: Declare the shard in connects_to shards: on the class you switch on, and switch on that class (ShardRecord.connected_to(shard: :shard_one) works with the declaration as shown). Keep the shard names in connects_to and in the resolver identical.

Words of Caution: Database Optimization in Ruby on Rails

Optimization deserves the same care as any other change. It is possible to over-index, or to index the wrong columns, and every unnecessary index slows down writes. Eager loading gets out of control as easily as N+1s do, and neither problem is obvious in development. Keep the local database at least a rough approximation of production. Traffic and usage patterns change week to week and season to season, depending on the business. What your monitoring shows this month is not what it will show next quarter.

Do not spend time on an optimization until you know its impact, and skip micro-optimizations that no measurement can see.

Scaling Isn’t Always Needed

The standard disclaimer for every post about scaling: make sure you have a problem before solving it. The indicator that you do is a primary database that runs at sustained high CPU or memory, after the query-level fixes are in: N+1s gone, hot columns indexed, wide reads trimmed, big writes batched. When that day comes, a read replica is the first step, a separate database for a well-bounded high-volume feature is the second, and sharding is the last.

For the mechanics of every multi-database feature this guide touched on, the Rails guide on multiple databases is the reference; for load_async in depth, read the query hub’s load_async section and Paweł Urbanek’s article on load_async.

Happy optimizing!

P.S. If you’d like to read Ruby Magic posts as soon as they get off the press, subscribe to our Ruby Magic newsletter and never miss a single post!