Stop Loading Entire Tables: Pagination Choices in Rails Active Record

The problem is memory and time on the index page

An admin index that does Post.all works until the table grows. The controller builds an ActiveRecord::Relation, the view iterates over it, and Rails instantiates an object for every row. Response time climbs, worker memory grows, and a single request can push a process toward OOM.

The useful takeaway is to keep the relation lazy and slice it in the database. Pagination moves the work to SQL with LIMIT and an ordering key, so only the page you need is materialized.

Why full collection loading hurts

Loading thousands of records at once creates two costs. First, memory: each ActiveRecord instance carries attribute hashes and association proxies. Second, database time: without a limit the database scans and returns the whole table.

The classic N+1 amplifies this. If the view accesses post.user.name for each row without eager loading, Rails issues an extra query per record. Pagination reduces the number of rows, but you still need includes for associated data.

Offset pagination for page numbers

Offset pagination is the familiar page 1, 2, 3 UI. In Active Record it is .order(...).limit(n).offset(m).

An example controller action that slices by params:

class PostsController < ApplicationController
  def index
    page = [params[:page].to_i, 1].max
    per_page = [params[:per_page].to_i, 25].clamp(1, 100)

    @posts = Post.order(created_at: :desc, id: :desc)
                 .limit(per_page)
                 .offset((page - 1) * per_page)
  end
end

Render a partial with prev/next links using page and per_page. The view never receives the whole set.

Limitations: offset is O(N) as the offset grows. The database still walks past the skipped rows. For deep pages this becomes noticeably slower. Counting total rows with .count on every request adds another full scan on large tables; cached counters or database-specific estimates are safer.

Cursor pagination for stable, fast feeds

Cursor pagination fetches records after a known key instead of skipping rows. It uses a unique, monotonically increasing column, typically id or created_at plus id.

A minimal cursor query:

last_created_at = params[:after_created_at]
last_id = params[:after_id]

scope = Post.order(created_at: :desc, id: :desc).limit(25)
if last_created_at && last_id
  scope = scope.where('created_at < ? OR (created_at = ? AND id < ?)', last_created_at, last_created_at, last_id)
end

The next page link carries the last item's key as after. Because the database can use an index on the ordering columns, it does not need to scan prior rows.

Cursor pagination trades random access for speed. You cannot jump to page 100 without walking cursors, and you need a stable ordering. It works well for infinite scroll and activity feeds.

Trade-offs and practical checks

Offset is easier for jump-to-page UIs and is fine for small to medium tables with proper indexes. Cursor is preferable for large, append-only feeds where deep pagination is common. The Active Record methods shown are stable across recent Rails versions; confirm behavior against your specific version if you are on an older release.

Before deploying, ensure an index exists on the columns used for ordering and filtering. Without it, both strategies degrade to full table scans.

Verify implementation by inspecting the generated SQL in Rails logs. You should see LIMIT and either OFFSET or a WHERE clause on the cursor key, not a plain SELECT * FROM posts. Check that the relation remains a Relation until the view iterates, and that includes is used for associations to avoid N+1.

Actionable closing

Replace collection loads with a sliced relation, choose offset for page numbers and cursor for deep feeds, add indexes on ordering columns, and avoid per-request .count on massive tables. Those changes keep memory bounded and keep SQL predictable as data grows.