Deep pages taking seconds with OFFSET pagination, fixed with keyset cursors

Our project management SaaS exposes an activity feed API (GET /api/v1/activities) that partners use to sync events into their own tools. In December a large customer connected a data warehouse sync that walked the entire feed every night, 100 rows per page. Our database dashboards started showing a slow query that appeared at 02:00 and ran for hours, and the API's p99 for that endpoint went from 40 ms to 6 seconds.

The slow query log made the pattern obvious:

LOG:  duration: 3214.882 ms  execute <unnamed>: SELECT "activities".* FROM "activities"
      WHERE "activities"."workspace_id" = $1 ORDER BY "activities"."created_at" DESC
      LIMIT $2 OFFSET $3
DETAIL:  Parameters: $1 = '8812', $2 = '100', $3 = '402300'

Each successive page was a bit slower than the one before. By the end of the run, a single page took over five seconds.

Reproducing it

On a staging copy with the customer's 1.2 million activities:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM activities
WHERE workspace_id = 8812
ORDER BY created_at DESC
LIMIT 100 OFFSET 400000;

Limit  (actual time=2981.4..2982.0 rows=100 loops=1)
  Buffers: shared hit=58210 read=341877
  ->  Index Scan using index_activities_on_workspace_id_and_created_at on activities
        (actual time=0.03..2962.7 rows=400100 loops=1)
        Index Cond: (workspace_id = 8812)
Execution Time: 2982.3 ms

rows=400100 for a query that returns 100 rows is the whole story.

What we ruled out

• A missing index. The plan used the composite index on (workspace_id, created_at) as intended.
• Table bloat. VACUUM (ANALYZE) on the staging copy changed nothing, and dead tuple ratios were low.
• Wide rows. Selecting only id reduced the time somewhat but the shape stayed the same: cost grew linearly with the offset.

Root cause

OFFSET n does not jump to row n. The database still produces the first n rows in order and throws them away, so page k costs about k * page_size row reads. A full walk of N rows by pages costs roughly N squared / (2 * page_size) row reads: for 1.2 million rows at 100 per page, around 7 billion. There was also a correctness bug hiding in it: created_at is not unique (bulk imports create many rows with the same timestamp), so rows with equal timestamps could appear on two pages or on none as new activity was inserted during the walk.

What is happening under the hood

PostgreSQL's documentation says it plainly: rows skipped by OFFSET still have to be computed inside the server, so a large offset can be inefficient. An index helps find the start of the ordered range, but it cannot skip to a numeric position, because an index entry does not know its rank.

Keyset (or "seek") pagination asks a different question: "give me the next 100 rows after the last one I saw". With an index on the sort columns, the database jumps straight to that key and reads only 100 entries, no matter how deep the page is.

For the comparison to be correct, the sort order must be total. PostgreSQL supports row value comparisons, so (created_at, id) < ($1, $2) compares lexicographically, exactly matching ORDER BY created_at DESC, id DESC, and can use a composite index on those columns.

Environment

Minimal reproduction

# app/controllers/api/v1/activities_controller.rb (before)
def index
  page = params.fetch(:page, 1).to_i
  scope = current_workspace.activities.order(created_at: :desc)
  render json: scope.limit(100).offset((page - 1) * 100)
end

Who is affected

Any API or admin screen that offers deep ?page=N navigation over large tables, and especially any integration that iterates through all pages. Users clicking "next" a few times on a small dataset will not notice; machines walking millions of rows will.

The fix

1. A unique, indexed sort key. We added id as a tie-breaker and built a matching index concurrently so writes were not blocked:

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

  def change
    add_index :activities, [:workspace_id, :created_at, :id],
              order: { created_at: :desc, id: :desc },
              algorithm: :concurrently,
              name: "index_activities_keyset"
  end
end

2. Opaque cursors instead of page numbers. The cursor encodes the last row's sort key. We encode it as base64 JSON so clients treat it as opaque and we can change the format later:

module Cursor
  def self.encode(record)
    Base64.urlsafe_encode64({ t: record.created_at.iso8601(6), id: record.id }.to_json, padding: false)
  end

  def self.decode(token)
    data = JSON.parse(Base64.urlsafe_decode64(token))
    [Time.iso8601(data.fetch("t")), Integer(data.fetch("id"))]
  rescue ArgumentError, KeyError, JSON::ParserError
    raise ActionController::BadRequest, "invalid cursor"
  end
end

3. The keyset query.

def index
  limit = params.fetch(:limit, 100).to_i.clamp(1, 500)
  scope = current_workspace.activities.order(created_at: :desc, id: :desc)

  if params[:cursor].present?
    t, id = Cursor.decode(params[:cursor])
    scope = scope.where("(activities.created_at, activities.id) < (?, ?)", t, id)
  end

  rows = scope.limit(limit + 1).to_a
  has_more = rows.length > limit
  rows = rows.first(limit)

  render json: {
    data: rows,
    next_cursor: has_more ? Cursor.encode(rows.last) : nil,
  }
end

The SQL it produces:

SELECT * FROM activities
WHERE workspace_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 101;

Fetching limit + 1 rows tells us whether there is another page without a separate COUNT(*).

4. Deprecate deep page=. We kept page for the first 50 pages (humans in the UI) and returned 400 with a pointer to cursor beyond that, after notifying the three partners who used it.

Alternatives and trade-offs

• Keep OFFSET but cap the depth. Simple and fine for UIs. It does not help integrations that need everything.
• Deferred join (OFFSET on an id-only index scan, then join back for full rows). Cheaper per row, still linear in depth, still inconsistent under concurrent inserts.
• Keyset trade-offs. No "jump to page 371", and total counts must come from elsewhere if needed. Every allowed sort order needs its own index with a tie-breaker, so we limited sorts on this endpoint to created_at in either direction.

How to test the fix

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM activities
WHERE workspace_id = 8812
  AND (created_at, id) < ('2024-03-02 10:15:00.123456+00', 991223344)
ORDER BY created_at DESC, id DESC
LIMIT 101;

Limit  (actual time=0.041..0.212 rows=101 loops=1)
  Buffers: shared hit=9 read=4
  ->  Index Scan using index_activities_keyset on activities (actual rows=101 loops=1)
Execution Time: 0.240 ms

A request spec walks a workspace with deliberately duplicated timestamps and checks that every row appears exactly once:

it "returns every activity exactly once across pages" do
  ws = create(:workspace)
  frozen = Time.utc(2025, 12, 1, 12)
  create_list(:activity, 250, workspace: ws, created_at: frozen) # all equal timestamps

  seen, cursor = [], nil
  loop do
    get "/api/v1/activities", params: { limit: 40, cursor: cursor }.compact, headers: auth(ws)
    body = response.parsed_body
    seen.concat(body["data"].map { _1["id"] })
    break unless (cursor = body["next_cursor"])
  end

  expect(seen.size).to eq(250)
  expect(seen.uniq.size).to eq(250)
end

Confirming in production

• The partner's nightly sync went from 4 hours 40 minutes to 6 minutes for the same 1.2 million rows.
• Endpoint p99 returned to 35 ms; the slowest page in the walk took 4 ms.
• pg_stat_statements for the old query showed total time dropping from about 16,000 seconds per day to near zero once the partner moved to cursors.

SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 2) AS mean_ms
FROM pg_stat_statements
WHERE query LIKE '%FROM "activities"%created_at%LIMIT%'
ORDER BY total_exec_time DESC LIMIT 5;

Regression guard

• The duplicate-timestamp spec runs in CI.
• A RuboCop custom cop flags .offset( in API controllers.
• An alert triggers on any query with mean execution over 500 ms and more than 1,000 calls per hour.

Security implications

Cursors are opaque but not secret: a client can decode them. They only contain a timestamp and an ID the client already received, and the query is always scoped to the caller's workspace, so a forged cursor cannot reach other data. Invalid cursors return 400 rather than a server error, and the limit is clamped so a client cannot request huge pages. Removing deep offsets also removed an easy way to load the database with a handful of requests.

Prevention checklist

• Use keyset pagination for any API that may be walked to the end.
• Always sort by a unique key or add a tie-breaker column.
• Back every allowed sort order with a matching composite index.
• Return an opaque cursor and has_more; avoid total counts on huge sets.
• Run EXPLAIN (ANALYZE, BUFFERS) at a deep page before shipping pagination.

References

• PostgreSQL 17 documentation: LIMIT and OFFSET — https://www.postgresql.org/docs/17/queries-limit.html
• PostgreSQL 17 documentation: Row constructor comparison — https://www.postgresql.org/docs/17/functions-comparisons.html
• PostgreSQL 17 documentation: Multicolumn indexes — https://www.postgresql.org/docs/17/indexes-multicolumn.html
• Rails API: add_index options — https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html