Active Record Pagination: Transitioning from Offset to Keyset for High-Concurrency Datasets

Direct answer

Rails 7.x has no built-in keyset pagination API, so the recommended architecture is a thin, hand-written scope layer on top of Active Record relations — or a well-maintained gem such as Pagy (which ships keyset support in recent versions) — rather than custom raw SQL scattered through controllers. The scope accepts an opaque cursor, applies a WHERE filter on the ordering columns, and returns a limit + 1 result so you know whether a next page exists. This keeps everything inside the standard relation pattern (chainable, composable with where/order) while eliminating the linear scan that makes deep OFFSET pages expensive under concurrency.

Why this is the right shape

Two properties matter for your case. First, keyset pagination is roughly constant-time regardless of page depth, because the database seeks directly into an index instead of scanning and discarding N rows. Second, it is stable under concurrent writes: OFFSET counts positions, so rows inserted or deleted between requests shift every later page and cause skips or duplicates; keyset anchors to row values, which do not move. For a high-traffic endpoint serving deep pages, both properties are exactly what you need. The trade-off is real: you lose random page access ("jump to page 47") and cheap total page counts. Keyset fits next/previous or infinite-scroll APIs; if your UI requires numbered pages, this is the wrong tool.

Handling non-unique sort keys

A single non-unique column like created_at is not sufficient: ties at the page boundary will silently drop or repeat rows. The standard fix is a composite key with a unique tiebreaker, almost always the primary key:

scope :after_cursor, ->(cursor) do
  return all if cursor.blank?
  ts, id = decode_cursor(cursor)
  where("(created_at, id) > (?, ?)", ts, id)
       .order(created_at: :asc, id: :asc)
end

Row-value (tuple) comparison like (created_at, id) > (?, ?) is supported by PostgreSQL, MySQL, and SQLite, and it lets the database use one composite index. Two requirements follow:

• Matching index: add a composite index on (created_at, id) in the same order and direction as the ORDER BY. Without it, the query still sorts and you lose most of the benefit.
• Consistent direction: if you sort descending, both the comparison operator and the index direction must match. Mixed-direction ordering works on PostgreSQL but needs careful index design.

Encode the cursor as an opaque value (e.g., Base64-encoded JSON of the last row's key columns) rather than exposing raw IDs. That way you can change the ordering strategy later without breaking clients, and clients cannot hand-craft cursors that bypass your assumptions.

Verification before rollout

1. Run EXPLAIN ANALYZE on the old OFFSET query at a deep page (e.g., offset 100,000) and on the keyset equivalent. Confirm the keyset plan shows an index range scan on your composite index — not a filesort or sequential scan — and compare rows scanned and execution time.
2. In a staging copy, paginate through the entire table and assert the union of all pages equals the full table exactly once (no gaps, no duplicates).
3. Run a concurrency test that inserts rows while paginating; keyset should show no duplicates or skips, while the OFFSET version demonstrates the anomaly.
4. If you currently render total counts, note that COUNT(*) often becomes the new bottleneck once pagination is cheap — omit it or cache it.

Caveats

Gem APIs and Rails version compatibility change over time; confirm any gem you adopt supports your exact Rails version, and inspect the SQL it generates rather than trusting it uses the index. Never mix keyset and OFFSET in the same endpoint. If deep-linking to arbitrary pages is a hard requirement, you will need a hybrid design, which is a separate architectural decision.