How do you eliminate N+1s in Rails and decide when to use CQRS?

Long Answer

At scale, Rails performance hinges on shaping data access, not just micro-optimizing Ruby. I use a layered approach: first eliminate N+1s, then reduce join cost, then decide when Active Record should yield to raw SQL or CQRS read models.

1) Eliminate N+1s intentionally

Eager loading is the first line of defense.

• preload: fires one query per association and avoids explosive row multiplication. Great for lists with post.comments or order.line_items.
  
• includes: behaves like preload unless you reference the association in conditions/order; then Rails may JOIN automatically. Pair with .references(:assoc) to be explicit.
  
• eager_load: forces a LEFT OUTER JOIN; good for filtering/sorting on the association in a single pass.
  Add strict_loading on models/relations to raise on lazy loads in production; it surfaces hidden N+1s early. Enable Bullet and rack-mini-profiler in dev to detect N+1 queries and large payloads. Prefer select/pluck to narrow columns. For big lists, stream with find_each and paginate to bound memory.
  

2) Reduce join cost and request fan-out

Heavy joins often mask report-like needs. Options:

• Counter caches: store counts (counter_cache: true) or use counter_culture for complex relations. Keep them accurate with callbacks + backfills. This removes COUNT(*) joins from hot paths.
  
• Precomputed projections: create denormalized tables or materialized views (gem scenic) for dashboards, search cards, and badges. Refresh on schedule or via CDC/outbox. Query these with slim AR models or raw SQL.
  
• Targeted JOINs: when you must JOIN, ensure composite indexes match WHERE/JOIN patterns, keep predicates sargable (no functions on indexed columns), and push filters before joins. Replace SELECT * hydrations with DTO-style select.
  
• Async loading (Rails 7 load_async): parallelize independent queries to hide latency.
  

3) Read replicas and routing

Use Rails multi-DB (connected_to(role: :reading, shard: …)) to offload read-heavy endpoints. Mark non-critical GETs as replica-safe; for read-after-write paths (checkout, profile saves), pin to primary or use session-based stickiness until replication catches up. Expose a “freshness” guard for endpoints that must be consistent, and fall back to primary on lag.

4) When to replace Active Record with raw SQL

Active Record is excellent for CRUD and straightforward relationships. I replace it when:

• Complex aggregates: cohorts, percentiles, top-N per group, window functions (ROW_NUMBER, SUM OVER) are cleaner and faster in SQL.
  
• Advanced filtering: CTEs, recursive queries, lateral joins, JSONB/GiST/GIN containment in Postgres.
  
• Bulk operations: insert_all, upsert_all, batched updates/deletes with writable CTEs.
  In these cases I use find_by_sql, exec_query, or Arel for safe interpolation, return POROs/structs, and keep queries versioned with tests. Guard raw SQL behind repository objects with clear boundaries.
  

5) When to adopt CQRS in Rails

Use CQRS when read concerns diverge from write concerns: analytics dashboards, search results, feed assembly, or multi-tenant rollups. Keep writes on normalized Active Record models with constraints; publish domain events (outbox). Build read models: denormalized tables or materialized views tuned for the exact queries. Controllers for those endpoints talk to the read store via lightweight AR models or raw SQL, often readonly and cache-friendly.

6) Caching and invalidation

Leverage HTTP caching for public pages; in-app use Redis with Rails.cache.fetch and race-condition TTL jitter. Cache expensive list decorations (badges, counts) keyed by model updated_at or version. For per-record fragments, tie keys to cache_key_with_version. Counter caches minimize invalidation surface.

7) Guardrails and tooling

Ship database indexes first; verify with EXPLAIN ANALYZE. Track query counts and timing via logs and APM; set per-action budgets. Use scopes that always eager load the right associations for list endpoints. For background jobs, process in batches and pluck ids to avoid hydrating large graphs.

The playbook: ban surprise lazy loads, compute what you can ahead of time, and choose the right abstraction—Active Record for domain writes, raw SQL for gnarly reads, and CQRS in Rails when the two diverge. That keeps latency low and headroom high as data and teams scale.

‍

Table

‍

Common Mistakes

• Using includes everywhere and accidentally forcing giant JOINs; or relying on lazy loads and shipping N+1s.
  
• Hydrating full entities for lists instead of select/pluck projections.
  
• Counting with live queries (comments.count) instead of counter_cache where correctness tolerates it.
  
• Missing composite indexes that match WHERE/JOIN, leading to table scans.
  
• Reading from replicas immediately after writes without stickiness, exposing stale data.
  
• Forcing complex analytics through Active Record scopes instead of a single SQL with CTE/window functions.
  
• Skipping EXPLAIN and APM; guessing at performance.
  
• Caching without versioned keys, causing stale fragments and hard-to-debug misses.
  
  

Sample Answers

Junior:
“I prevent N+1 queries with preload/includes and use Bullet to catch misses. I narrow columns with select and paginate lists. For counts I use counter_cache when possible.”

Mid:
“I choose preload for separate eager queries and eager_load when I must filter/order on associations. I verify indexes with EXPLAIN, use load_async for independent reads, and route safe endpoints to read replicas with stickiness after writes.”

Senior:
“I treat joins as a smell for read endpoints: I use counter caches, scenic materialized views, and read models. For analytics I replace Active Record with raw SQL (CTEs, window functions) behind repositories. We run multi-DB with connected_to, cache with versioned keys, enforce strict_loading, and assert query budgets in CI to prevent N+1 regressions.”

‍

Evaluation Criteria

Strong answers explicitly cover:

• How to eliminate N+1 queries with preload/includes/eager_load and strict_loading.
  
• Replacing heavy joins with counter caches and denormalized read models.
  
• Proper indexing and sargable predicates, validated by EXPLAIN.
  
• Use of read replicas with consistency controls.
  
• When to bypass Active Record for raw SQL (CTEs, windows) and when to introduce CQRS in Rails.
  
• Sensible caching with versioned keys.
  Red flags: “just use includes,” no mention of indexes/EXPLAIN, forcing complex analytics through AR relations, ignoring replica lag, or caching without invalidation strategy.
  
  

Preparation Tips

• Turn on Bullet; fix two N+1 hotspots with preload vs eager_load and measure deltas.
  
• Add a composite index that matches your heaviest JOIN; confirm with EXPLAIN ANALYZE.
  
• Implement a counter_cache for a list badge; backfill and compare latency to COUNT(*).
  
• Create a materialized view (scenic) for a dashboard card; swap controller to the view model.
  
• Write one raw SQL query with a CTE and ROW_NUMBER() for top-N-per-group; wrap in a repository and tests.
  
• Enable strict_loading on a hot model and fix failures.
  
• Add a replica and route a read-only endpoint via connected_to(role: :reading); add stickiness after write.
  
• Introduce versioned fragment keys using cache_key_with_version.
  
  

Real-world Context

A marketplace feed dropped from 1.2s to 280ms after replacing N+1s with preload, adding a (seller_id, created_at) index, and moving badges to a counter cache. A B2B dashboard replaced four JOIN-heavy queries with a scenic materialized view refreshed every minute; CPU fell 40% and tail latency stabilized. A SaaS analytics page abandoned AR scopes for a single CTE + window SQL, wrapped in a repository; response time fell from 3.5s to 300ms. Another team adopted connected_to read replicas with write stickiness; read throughput doubled without stale reads on post-checkout pages.

‍

Key Takeaways

• Use preload/includes/eager_load deliberately and strict_loading to eradicate N+1 queries.
  
• Replace heavy joins with counter caches and denormalized read models.
  
• Validate plans with EXPLAIN; design composite indexes and sargable predicates.
  
• Offload traffic with read replicas and stickiness after writes.
  
• Prefer raw SQL for complex analytics; adopt CQRS in Rails when read needs diverge.
  
  

Practice Exercise

Scenario:
Your Rails app shows an organization dashboard listing projects with owner, open issues count, and latest activity. It times out under load due to N+1s and heavy joins.

Tasks:

1. Add strict_loading to Project and fix raised lazy loads using preload or eager_load where sorting/filtering requires joins.
   
2. Replace issues.count with a counter_cache (issues_count) on projects; write a backfill and migration.
   
3. Create a scenic materialized view project_summaries that joins projects → owners and aggregates latest activity; index (org_id, updated_at DESC).
   
4. Swap the dashboard controller to read from ProjectSummary (readonly AR), selecting only needed columns.
   
5. Put independent queries behind load_async; paginate results.
   
6. Add composite indexes matching WHERE/JOIN; verify improvements with EXPLAIN ANALYZE.
   
7. Route the dashboard action through a read replica with connected_to(role: :reading) and enable stickiness for 60s after writes.
   
8. Cache each row fragment keyed by cache_key_with_version and add TTL jitter in Redis.
   
9. Add Bullet + rack-mini-profiler to CI to fail builds on new N+1s; record before/after p95, DB time, and query count.
   
   

Deliverable:
A measured optimization showing fewer queries, smaller joins, faster p95, and a clean boundary where Active Record handles writes while a read model (or raw SQL) powers the dashboard.