Active Record Performance skill for Codex

Done well means: the slow action or job has a measured baseline (query count, SQL time, allocations), each change targets the measured cause, lazy loading is caught by strict_loading in development and test, and large-table index changes never block writes.

Workflow

1. Read AGENTS.md, Gemfile.lock (Rails version, pg/mysql2/trilogy, bullet, prosopite, rack-mini-profiler) and config/database.yml.
2. Reproduce in development or a test with realistic data volume.
3. Measure (table below). Note query count, top statements and allocations.
4. Classify: per-row queries, one slow query, too many columns or rows loaded, or Ruby work per row. Fix that cause only.
5. Re-measure; add a guard (strict loading or a query-count assertion).

Need	Tool
Per-request queries and timing in dev	rack-mini-profiler; development log with config.active_record.verbose_query_logs = true
N+1 detection	bullet or prosopite in dev/test; strict_loading
Plan of a relation	Order.where(...).explain (Rails 7.1+: .explain(:analyze, :buffers) on Postgres)
Attribute queries to code in production	config.active_record.query_log_tags_enabled = true (comments in SQL)
Count queries in a test	subscribe to sql.active_record via ActiveSupport::Notifications.subscribed

explain(:analyze) executes the query; run it against development or a replica.

Loading associations

Method	SQL	Use when
preload(:items)	Separate IN query per association	Default for has_many; no filtering on the association
eager_load(:customer)	One LEFT OUTER JOIN	You filter or order by the association’s columns
includes(:items)	Preload, or eager_load if referenced in where/references	Convenience; be explicit in hot paths

orders = Order.where(account: current_account)
              .preload(:customer, line_items: :product)
              .order(created_at: :desc)
              .limit(50)

• Turn on strict loading so missed preloads raise in dev and test:

# config/environments/development.rb and test.rb
config.active_record.strict_loading_by_default = true
config.active_record.action_on_strict_loading_violation = :raise
# Or per model / per relation: self.strict_loading_by_default = true; Order.strict_loading

record.strict_loading!(mode: :n_plus_one_only) limits violations to real N+1 patterns for that record. Roll out strict loading model by model on large apps.

• Scoped associations: has_many :active_items, -> { where(active: true) }, class_name: "Item" and preload that, instead of calling .where on a preloaded association (new query per row).
• Deep N+1 playbook across ORMs: $n-plus-one-queries.

Counts

• size uses the loaded collection or a counter cache; count always runs COUNT(*); length loads all records. In views, use size.
• Counter caches for counts rendered in lists:

class Comment < ApplicationRecord
  belongs_to :post, counter_cache: true
end

Adding one to an existing table: migrate the column with default: 0, null: false, then backfill in batches before reads rely on it. Rails 7.2+ supports counter_cache: { active: false } so writes maintain the column while reads still count, then flip to true after the backfill.

Post.in_batches(of: 1_000) do |batch|
  batch.update_all(<<~SQL.squish)
    comments_count = (SELECT COUNT(*) FROM comments WHERE comments.post_id = posts.id)
  SQL
end

• Counter caches skip update_all, delete_all and insert_all; code paths using them must adjust counts themselves. Grouped counts for a page: Comment.where(post_id: ids).group(:post_id).count.

Load less

• pluck(:id, :email) returns arrays without instantiating models; pick(:email) for one row.
• select(:id, :name) keeps model objects with fewer columns; accessing an unselected attribute raises ActiveModel::MissingAttributeError.
• exists? runs SELECT 1 ... LIMIT 1; present?/any? on an unloaded relation may load rows.
• Large text, JSON or blob columns in list queries: select them out, or move to a side table.

Batches and bulk writes

User.where(active: true).find_each(batch_size: 1_000) { |u| SyncJob.perform_later(u.id) }

Order.where(status: "stale").in_batches(of: 5_000) do |rel|
  rel.update_all(status: "archived", updated_at: Time.current)
  sleep 0.05 # throttle on busy primaries
end

Product.upsert_all(rows, unique_by: %i[account_id sku], record_timestamps: true)

• find_each orders by primary key and ignores your order; use in_batches(cursor:) (Rails 8+) or keyset loops when another order matters.
• insert_all, upsert_all, update_all, delete_all skip validations and callbacks. Confirm nothing (search indexing, auditing, counter caches) depends on them.
• Mass delete_all/update_all on production data require explicit user confirmation and a dry-run count first (rel.count).
• Atomic increments: Product.where(id: id).update_counters(stock: -1) instead of read-modify-write.

Parallel independent queries

load_async runs independent queries in a background thread pool (configure config.active_record.async_query_executor = :global_thread_pool). Use it for 2-4 unrelated slow queries in one action; it does not help a single slow query.

Index migrations

class AddIndexToOrdersOnAccountCreatedAt < ActiveRecord::Migration[8.0]
  disable_ddl_transaction!

  def change
    add_index :orders, [:account_id, :created_at], order: { created_at: :desc },
              algorithm: :concurrently, if_not_exists: true
    add_index :orders, :created_at, where: "status = 'pending'", name: "index_orders_pending",
              algorithm: :concurrently, if_not_exists: true
  end
end

• algorithm: :concurrently (Postgres) requires disable_ddl_transaction!; put nothing else in that migration. A failed concurrent build leaves an INVALID index: drop it and retry.
• MySQL: InnoDB adds secondary indexes online by default; algorithm: :inplace style options belong in $mysql-online-schema-change for big tables.
• strong_migrations gem, when present, enforces these rules; follow its messages.
• references columns get an index by default; a composite index starting with that column can make it redundant.

Connection pool

• pool in database.yml must be at least Puma max_threads (plus load_async threads) per process, and Sidekiq concurrency per Sidekiq process.
• Total connections = processes x pool across web and workers; keep it under the database max_connections or put PgBouncer in front ($connection-pooling).

Gotchas

• includes with a where on a string condition referencing the association needs .references(:assoc), or Rails preloads and the SQL fails.
• default_scope with order breaks find_each expectations and adds sorts everywhere.
• Callbacks (after_save) that touch associations turn bulk updates into N queries; use update_all deliberately or move work to a job.
• touch: true chains cascade writes up the tree on every child save.
• Caching (Rails.cache.fetch) hides slow queries; fix the query first, cache second.

Verification

• Query count and SQL time before and after at two data sizes; count must not grow with rows.
• bullet/prosopite silent and strict loading green across the test suite.
• explain(:analyze) on the slowest remaining query shows the intended index.
• For counter caches: spot-check Post.find(id).comments_count == Post.find(id).comments.count on a sample after backfill.

Output

**Path:** <controller#action / job / rake task>
**Before:** <queries, SQL ms, allocations> at <data size>
**Cause:** <N+1 / COUNT per row / full-row load / missing index / callback cascade>
**Changes:** <relation changes, counter caches, migrations>
**After:** <queries, SQL ms, allocations>
**Guard:** <strict_loading scope / query-count test>