Rails Cursor Pagination: Keyset Pagination That Scales When OFFSET Falls Over

A founder pinged me at half past three in the morning because his marketplace’s “load more” button had taken the primary Postgres offline. Not the database, the whole primary. The API had a perfectly reasonable pagination endpoint on /api/v1/listings?page=1&per_page=25, and it worked fine for the first hundred pages. Then a scraper decided page 8000 was interesting, Postgres decided to walk 200000 rows to skip 199975 of them, and every other query queued behind it. The fix that night was a WAF rule blocking page above 500. The fix the following week was rewriting the endpoint to use Rails cursor pagination and never think about OFFSET again.

After nineteen years of Rails I have seen this exact incident, or a quieter version of it, at almost every company I have advised past a few hundred thousand rows in any hot table. Rails cursor pagination — also called keyset pagination — is one of those changes that looks like a small refactor and turns out to be an outage-prevention feature. This post is the playbook I hand engineering leads when their paginate calls start ending in tears: what OFFSET actually does, what a cursor actually is, how to implement it safely, and where the edges are.

Why OFFSET Falls Over

The mental model most Rails developers carry for LIMIT 25 OFFSET 1000 is “skip a thousand rows, give me the next twenty five.” That is exactly what Postgres does, and that is exactly the problem. There is no free skip. To honour an OFFSET 1000, Postgres has to produce the first thousand rows in sorted order and then discard them. On a hot table with a million rows, OFFSET 500000 walks half a million rows before returning a single byte to your Rails app.

Run this on any non-trivial table and watch what happens:

# In rails console
Listing.order(created_at: :desc).offset(50_000).limit(25).explain

You will see something like Seq Scan or Index Scan followed by a rows count in the tens of thousands. The EXPLAIN ANALYZE output makes it worse — actual timing scales linearly with offset. Page 1 is 4ms, page 100 is 40ms, page 2000 is 800ms, page 20000 is a timeout. I dig into query plans in detail in Rails EXPLAIN ANALYZE if you have never traced one before.

The second problem with OFFSET is stability. Between fetching page 3 and page 4, a new row can arrive at the top of the list. The user then sees the same row twice on page 4, or misses one entirely. On any feed that is actively being written to — orders, notifications, marketplace listings, chat messages — OFFSET pagination is quietly incorrect even when it is fast.

What Rails Cursor Pagination Actually Is

Keyset pagination flips the question around. Instead of “give me rows 1001 to 1025,” you ask “give me the next 25 rows after this specific row.” The “specific row” is identified by the values of the columns you are ordering by — the cursor. Postgres can jump directly to that spot in the index and stream forward. The cost is the same on page 1 as it is on page 20000.

The minimum viable Rails cursor pagination for a single-column ordering looks like this:

# app/controllers/api/v1/listings_controller.rb
class Api::V1::ListingsController < Api::V1::BaseController
  MAX_PER_PAGE = 100

  def index
    per_page = [params[:per_page].to_i, MAX_PER_PAGE].min.clamp(1, MAX_PER_PAGE)

    scope = Listing.published.order(id: :desc)
    scope = scope.where("id < ?", params[:after].to_i) if params[:after].present?

    listings = scope.limit(per_page + 1).to_a
    has_more = listings.size > per_page
    listings = listings.first(per_page)

    render json: {
      data:        listings.map { |l| ListingSerializer.new(l).as_json },
      next_cursor: has_more ? listings.last.id : nil
    }
  end
end

Three lines do the real work. Order by id descending, filter with id < cursor, ask for one extra row so you can tell the client whether there is a next page. On an indexed id column this is O(log n) plus O(per_page) — the same cost at row 1 and row 10 million.

Handling Ties: Always Have a Tiebreaker

The one-column example above works because id is unique. The moment you start ordering by anything that is not unique — created_at, updated_at, score, priority — you need a tiebreaker. Two listings created in the same millisecond will otherwise cause rows to be skipped or repeated at cursor boundaries.

The right cursor is a compound (sort_column, id) pair. This is the shape I use for anything user-facing:

class Api::V1::ListingsController < Api::V1::BaseController
  def index
    per_page = params.fetch(:per_page, 25).to_i.clamp(1, 100)

    scope = Listing.published.order(created_at: :desc, id: :desc)

    if params[:after].present?
      created_at, id = decode_cursor(params[:after])
      scope = scope.where(
        "(listings.created_at, listings.id) < (?, ?)",
        created_at, id
      )
    end

    listings = scope.limit(per_page + 1).to_a
    has_more = listings.size > per_page
    listings  = listings.first(per_page)

    next_cursor = has_more ? encode_cursor(listings.last) : nil

    render json: { data: serialize(listings), next_cursor: next_cursor }
  end

  private

  def encode_cursor(record)
    payload = { c: record.created_at.iso8601(6), i: record.id }
    Base64.urlsafe_encode64(payload.to_json, padding: false)
  end

  def decode_cursor(token)
    payload = JSON.parse(Base64.urlsafe_decode64(token))
    [Time.iso8601(payload["c"]), payload["i"].to_i]
  rescue JSON::ParserError, ArgumentError
    raise ActionController::BadRequest, "invalid cursor"
  end
end

The row-value comparison (created_at, id) < (?, ?) is the important bit. Postgres understands this as “any row whose created_at is strictly less than X, or where created_at equals X and id is less than Y.” It uses a compound index on (created_at, id) if you have one, and you should. The migration:

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

  def change
    add_index :listings, [:created_at, :id],
              order: { created_at: :desc, id: :desc },
              algorithm: :concurrently,
              name: "index_listings_on_created_at_id_desc"
  end
end

If the index is missing Postgres falls back to a sort, and the whole point evaporates. Verify with Listing.order(created_at: :desc, id: :desc).limit(25).explain — you want to see Index Scan Backward or Index Only Scan, never Sort. Related, pg_stat_statements will happily show you which of your pagination endpoints are still sorting when you thought they were not.

Signing Cursors So Clients Cannot Forge Them

The Base64 cursor above is opaque enough to look sensible in a URL, but it is not tamper-proof. A curious API consumer can decode it, change the timestamp, and query rows they should not see — especially if the cursor is scoped to a filter that lives client-side. For any endpoint where the cursor implies authorization (say, “listings I have access to before this point”), sign the cursor with MessageVerifier:

class CursorCodec
  KEY = "cursor.v1"

  def self.encode(payload)
    Rails.application.message_verifier(KEY).generate(payload, purpose: :cursor)
  end

  def self.decode(token)
    Rails.application.message_verifier(KEY).verify(token, purpose: :cursor)
  rescue ActiveSupport::MessageVerifier::InvalidSignature
    raise ActionController::BadRequest, "invalid cursor"
  end
end

Now a mutated cursor fails signature verification and never touches your database. The purpose string protects you from cross-context reuse — a cursor generated for one endpoint cannot be replayed against another.

The Full Rails Cursor Pagination Pattern

For any team building more than one paginated endpoint, wrap the whole thing in a service so the controller stays boring. This is roughly what I extract on day one:

# app/services/cursor_paginator.rb
class CursorPaginator
  Result = Struct.new(:records, :next_cursor, keyword_init: true)

  def initialize(scope:, order:, per_page: 25, after: nil, max_per_page: 100)
    @scope        = scope
    @order        = order # e.g. { created_at: :desc, id: :desc }
    @per_page     = per_page.to_i.clamp(1, max_per_page)
    @after        = after
  end

  def call
    scope = @scope.order(@order)
    scope = apply_cursor(scope, @after) if @after.present?

    records  = scope.limit(@per_page + 1).to_a
    has_more = records.size > @per_page
    records  = records.first(@per_page)

    Result.new(
      records:     records,
      next_cursor: has_more ? encode(records.last) : nil
    )
  end

  private

  def apply_cursor(scope, token)
    values  = decode(token)
    columns = @order.keys.map { |c| "#{@scope.klass.table_name}.#{c}" }
    op      = @order.values.first == :desc ? "<" : ">"

    scope.where("(#{columns.join(",")}) #{op} (#{Array.new(columns.size, "?").join(",")})", *values)
  end

  def encode(record)
    payload = @order.keys.each_with_object({}) do |col, h|
      value  = record.public_send(col)
      h[col] = value.is_a?(Time) ? value.iso8601(6) : value
    end
    CursorCodec.encode(payload)
  end

  def decode(token)
    payload = CursorCodec.decode(token)
    @order.keys.map do |col|
      value = payload[col] || payload[col.to_s]
      value.is_a?(String) && value.match?(/\dT\d/) ? Time.iso8601(value) : value
    end
  end
end

The controller collapses to:

class Api::V1::ListingsController < Api::V1::BaseController
  def index
    result = CursorPaginator.new(
      scope:    Listing.published.for_user(current_user),
      order:    { created_at: :desc, id: :desc },
      per_page: params[:per_page],
      after:    params[:after]
    ).call

    render json: {
      data:        serialize(result.records),
      next_cursor: result.next_cursor
    }
  end
end

Every paginated endpoint in the app now shares the same cursor shape, the same signature, the same guarantees. New engineers stop reinventing pagination in every PR.

Bidirectional Pagination for UI Feeds

APIs usually only need next_cursor. UI feeds — Twitter-style timelines, chat scrollback, admin dashboards with “previous page” buttons — need both directions. The trick is that “previous page” is just “next page in the opposite sort order”:

def index
  direction = params[:before].present? ? :backward : :forward
  cursor    = params[:before] || params[:after]

  order = direction == :forward ? { created_at: :desc, id: :desc }
                                : { created_at: :asc,  id: :asc }

  result = CursorPaginator.new(
    scope: Listing.published, order: order,
    per_page: params[:per_page], after: cursor
  ).call

  records = direction == :forward ? result.records : result.records.reverse

  render json: {
    data:            serialize(records),
    next_cursor:     direction == :forward ? result.next_cursor : encode_forward(records.last),
    previous_cursor: encode_backward(records.first)
  }
end

The subtle part is remembering to reverse the records on the way out so the client always renders in the display order. Test this. It is the bug I have watched senior engineers ship at 4pm on a Friday more than once.

When OFFSET Is Still Fine

Cursor pagination is not free — you give up random access to page N and you give up the total count. If a human needs to jump to page 400 of a report, cursors will not help them. If a stakeholder needs “showing 47 of 12300 records,” you either run a separate COUNT(*) (fine if the number is bounded and the count is cached) or you use OFFSET pagination and accept that page 400 is expensive.

The rules I use:

• Public APIs, mobile clients, infinite scroll feeds: always cursor pagination.
• Admin CRUD tables under 100000 rows with page numbers users click: OFFSET is fine, and the boring choice is often the right one.
• Anywhere a scraper can control page: cursor, or cap the page number aggressively.
• Anywhere the data changes while the user pages: cursor, or you will ship duplicates.

The counter cache pattern helps when you need a total that would otherwise require a COUNT(*) alongside the paginated query — that combination is a classic Rails performance trap.

Common Gotchas

Three things bite teams the first time they roll out Rails cursor pagination at any scale.

The first is missing indexes. A cursor query on unindexed columns is just a slow sort with extra steps. Add the compound index in the same PR as the code change, CONCURRENTLY in production, and verify the plan.

The second is order mismatch. If your order is created_at DESC, id DESC but the cursor condition is >, you will get an empty page and no obvious error. Write a test that asks for 200 records, paginates 20 at a time, and asserts the concatenated set equals the raw query with the same order. That test catches almost every mistake in this code path.

The third is nullable sort columns. NULL compares oddly in row-value comparisons and Postgres orders nulls last by default on ascending, first on descending. If you paginate over published_at DESC where published_at can be NULL, either add NULLS LAST, add a WHERE published_at IS NOT NULL filter, or use a COALESCE in both the order clause and the cursor condition. Consistency matters more than which choice you make.

Rails Cursor Pagination FAQ

What is the difference between cursor pagination and keyset pagination in Rails?

They are the same technique. “Keyset” describes the mechanism — using the values of the sort key to locate the next page. “Cursor” describes the API surface — the client sends an opaque token instead of a page number. In Rails, a cursor is typically an encoded (and often signed) keyset. Any post or gem that talks about one usually means both.

Should I use the pagy gem for cursor pagination?

Pagy is excellent for OFFSET pagination and now ships a keyset variant (Pagy::Keyset) that works well for straightforward cases. For a marketplace, a chat app, or any API with signed cursors, I still prefer the small hand-rolled CursorPaginator shown above because the cursor shape and signing strategy live in your codebase, not in a gem’s internals. For an internal admin dashboard, pagy is fine — reach for it and move on.

How do I paginate a joined query with cursor pagination?

You pick the sort key on the driving table, index the compound key there, and use select("DISTINCT ON") or a subquery to keep the join from duplicating rows. The rule is that whatever columns you order by must uniquely identify a row after all joins — otherwise the cursor comparison skips or repeats. If you cannot make the sort key unique post-join, restructure the query so the driving table paginates first and the joins fetch related data for the page.

Can I convert a live production API from OFFSET to cursor pagination without breaking clients?

Yes, and this is the migration I run most often. Add after as a new optional parameter, keep page working for one release, return both next_cursor and the existing pagination metadata, and log which clients are still sending page. After a deprecation window, drop page and require after. Mobile clients typically pin to a version so a hard cutover breaks users; a versioned API endpoint (/api/v2/listings) is often cleaner than trying to support both indefinitely.

Need help auditing a Rails API that is slowing down under real traffic — pagination, N+1 queries, or the database layer that quietly gave up? TTB Software specializes in Rails performance and fractional CTO work for teams past the prototype stage. We have been doing this for nineteen years.