Pagination at Scale

Where this rule comes from

Pagination is one of those features that looks identical from the outside no matter how it is implemented. The user clicks "next page," more results appear, the URL updates. Underneath, two completely different mechanisms have dominated database history, with very different scaling properties.

Offset pagination, the kind that produces URLs like ?page=42, works by telling the database "skip the first N rows, then return the next M." It is the default in will_paginate and kaminari, the two Rails pagination libraries every developer has used. It is intuitive, it supports random access to any page, and it gives you total counts. It is also a performance trap once data grows.

Cursor pagination, sometimes called keyset pagination, works by telling the database "give me M rows where some sort key is greater than X." The cursor is the value of the sort key from the last row of the previous page. No "skip" operation, no counting; the database uses the index directly. It scales to any depth without slowing down, at the cost of giving up random access to arbitrary pages.

Every API at large scale has moved to cursor pagination. Stripe's API documentation explicitly discusses the cursor-pagination decision. GitHub's API uses cursors. Mastodon's timeline uses cursors. Twitter (now X) used them for years before pagination became a different problem entirely. The senior move is to recognize when an app is about to hit the offset-pagination ceiling and switch before the production incident, not after.

The anti-pattern

Picture a Rails app with an admin dashboard. It paginates orders 50 per page, ordered by created_at DESC. kaminari is wired up, the URLs look like /admin/orders?page=N:

class Admin::OrdersController < AdminController
  def index
    @orders = Order.order(created_at: :desc).page(params[:page]).per(50)
  end
end

# Underlying SQL on page 1:
#   SELECT * FROM orders ORDER BY created_at DESC LIMIT 50 OFFSET 0;

# On page 100:
#   SELECT * FROM orders ORDER BY created_at DESC LIMIT 50 OFFSET 4950;

# On page 10,000 (after a year of growth):
#   SELECT * FROM orders ORDER BY created_at DESC LIMIT 50 OFFSET 499,950;

Here is what the database has to do for page 10,000: read 500,000 rows from the ordered index, throw away 499,950 of them, return the last 50. The "skip" operation is not magic. The database has to physically count past 499,950 entries to find row 499,951. At page 10,000, this can take hundreds of milliseconds; at page 100,000, multiple seconds.

The compounding problems:

• The "Total: 1,234,567 results" badge is its own query. COUNT(*) on a large table can take seconds even with an index. Every page load runs it.
• Pages drift under writes. A user on page 10 clicks "next" to go to page 11. In the moment between clicks, three new orders were created. The first three rows of page 11 are now duplicates of the last three rows of page 10, because the rows shifted in the underlying sort.
• Concurrent requests pay the same cost. If a script paginates through all pages to export data, the script gets exponentially slower as it deepens. Worse, it holds database connections open for the duration of each query.

Most Rails apps will not hit this in their first year. When they do hit it, it shows up as "the admin panel is slow only on the deep pages," and the fix is not to add more dynos.

Cursor pagination, mechanically

The trick: instead of "skip 49,950 rows," ask "give me 50 rows where created_at < the last row I saw." The database uses the index, walks 50 rows, returns them. No counting, no skipping. Same cost on page 1 as on page 10,000.

# A minimal cursor-pagination implementation:
class Admin::OrdersController < AdminController
  PAGE_SIZE = 50

  def index
    relation = Order.order(created_at: :desc, id: :desc)

    if (cursor = params[:cursor])
      created_at_str, id_str = cursor.split(",")
      created_at = Time.zone.parse(created_at_str)
      id         = id_str.to_i

      relation = relation.where(
        "(created_at, id) < (?, ?)",
        created_at, id
      )
    end

    @orders     = relation.limit(PAGE_SIZE).to_a
    @next_cursor = if @orders.length == PAGE_SIZE
      last = @orders.last
      "#{last.created_at.iso8601(6)},#{last.id}"
    end
  end
end

A few things to notice:

1. The cursor is a tuple of (created_at, id), not only created_at. If two orders have identical timestamps (rare but possible), created_at < X alone would skip both. Including the id as a tiebreaker makes the cursor strictly unique. The index has to cover (created_at, id) for this to be fast, which most Rails apps already have through the natural created_at + primary-key index.

2. The view passes the cursor in the URL. Instead of ?page=11, the "next page" link is ?cursor=2026-05-11T14:23:00.123456Z,4827. Each link is stable: if you bookmark page 11, you still see the same 50 records you saw before, even if new orders have been created since.

3. There is no total count. Cursor pagination cannot tell you "you are on page 11 of 247." It can tell you "there is a next page" (because the result set was full) or "you are on the last page" (because it was not full). For most applications, this is fine, the UI shows "Next" and "Previous" buttons, not page numbers. The tradeoff is real and worth being upfront about.

When you really need offsets

Some UIs genuinely need page numbers. Reports printed for users, admin tools with "go to page 47," search results that want to show "Showing 51-100 of 12,345." For these, two patterns soften the offset cost:

1. Cap the offset. Most users do not browse past page 100. After page 100, switch the UI to a search interface ("Filter by date" or "Search by ID"). Google does this: search results stop at around page 100, on the assumption that anyone who needs to look that deep should refine their query. It is a UX decision that happens to be a scaling decision.

2. Use a separately-cached count. The COUNT(*) portion of "showing 51-100 of N" can be cached or estimated. For approximate counts, Postgres exposes pg_class.reltuples as a stale-but-fast estimate. For the precision-matters case, run COUNT(*) in a background job, store the result on a small "stats" table, and update it periodically.

# Approximate count, very fast on huge tables:
result = ActiveRecord::Base.connection.execute(
  "SELECT reltuples::bigint AS estimate FROM pg_class WHERE relname = 'orders'"
)
estimate = result.first["estimate"]
# Within ~1% of the real count for normally-vacuumed tables.
# Returns instantly even on tables with billions of rows.

Real-world reference patterns

Mastodon's timeline is one of the canonical Rails references for cursor pagination at scale. Looking at the Mastodon controller code, the timeline pagination uses max_id and since_id parameters, mirroring the Twitter API conventions: max_id for "give me older statuses than this one" and since_id for "give me newer statuses than this one." The implementation is a few lines per controller, and it powers timelines with billions of total statuses across the federated network.

Stripe's API documentation is explicit about cursor pagination: every list endpoint accepts starting_after and ending_before parameters, which take resource IDs. The Stripe team has written publicly about why they chose cursors over offsets: API consumers needed pagination that stayed correct across writes (an analytics export script should not see duplicates), and they needed it to scale to merchants with millions of charges.

GitHub's GraphQL API uses Relay-style cursor connections: every paginated field returns pageInfo { hasNextPage, endCursor } and a list of edges { cursor, node }. The shape is more elaborate than Stripe's, but the underlying mechanism is the same: opaque cursors based on the last sort-key value, with no integer page numbers anywhere in the protocol. Adopting this shape is what allows GitHub's GraphQL API to paginate over repositories with millions of issues without ever slowing down.

Artsy Engineering's posts on building artist/artwork browse pages document the pragmatic mix: cursor pagination for the public-facing endless-scroll feed, offset pagination capped at page 50 for the admin UI where users genuinely need to "jump to page 25." The split based on actual UX needs is what most senior Rails apps end up doing.

Rails-specific tooling

The cursor_pagination and order_query gems wrap the cursor logic shown above so application code does not have to. order_query in particular is well-documented and handles the cursor encoding/decoding cleanly. For new apps, reaching for one of these is faster than hand-rolling.

For existing offset-paginated apps, the migration is usually a two-step rollout: add cursor pagination as a new endpoint or new query parameter, leave the offset version available during the transition, then deprecate the offsets once consumers (clients, scripts, integrations) have switched. Do not try to migrate in a single deploy; cursor and offset URLs are not compatible, and breaking bookmarks is a real UX cost.

A side-by-side benchmark

For a table with 1,000,000 orders and an index on created_at DESC, here is what EXPLAIN ANALYZE typically looks like for the two approaches:

-- Offset, page 1:
SELECT * FROM orders ORDER BY created_at DESC LIMIT 50 OFFSET 0;
-- Index Scan, ~0.1ms

-- Offset, page 10,000:
SELECT * FROM orders ORDER BY created_at DESC LIMIT 50 OFFSET 499,950;
-- Index Scan, but reads 500,000 index entries before returning 50
-- Typical execution time: ~400-800ms

-- Cursor, equivalent to page 10,000:
SELECT * FROM orders WHERE (created_at, id) < ('2025-08-12T14:23:00', 491234)
  ORDER BY created_at DESC, id DESC LIMIT 50;
-- Index Scan, reads 50 index entries, returns 50 rows
-- Typical execution time: ~0.1ms (same as page 1)

The numbers are not subtle: 4,000x to 8,000x faster for deep pagination, regardless of how much further you scroll. The cursor query is as fast on the millionth page as on the first.

When NOT to switch

For small tables (under ~10k rows) offset pagination is fine forever. The deep-page cost is negligible. Migrating to cursors adds UI complexity (no page numbers) for no performance gain.

For admin UIs where users actually need page numbers and traffic is low, offset is fine even on larger tables, as long as you do not chain it with COUNT(*) or expose deep pagination publicly.

For public APIs and user-facing list views with substantial traffic on large tables, switch to cursors before you need to. The migration is much easier when it is preemptive than when it is reactive after a performance incident.