Keyset Pagination in Ecto: Requirements, Minimal Design, and Where It Breaks

The problem with OFFSET at scale

If your list endpoint paginates a table that is large or frequently written to, OFFSET-based pagination in Ecto quietly becomes your worst query. Two things go wrong. First, cost: offset: 50_000 makes the database scan and discard fifty thousand rows before returning the fifty you asked for, so every deeper page is slower than the last. Second, correctness: if a row is inserted or deleted between two page requests, every subsequent page shifts by one, and clients see duplicates or silently miss records.

Keyset (cursor) pagination fixes both by replacing "skip N rows" with "give me rows after this value." The trade-off is real — you lose random page access — so this note walks through the requirements, the smallest design that works, the trust boundary the cursor creates, and the conditions under which you should not use it.

Requirements

Keyset pagination is the right tool when all of these hold:

• The dataset is large enough that deep OFFSET pages are measurably slow, or rows change often enough that page-shift duplicates matter.
• The UI moves forward (and optionally backward) through results — an infinite scroll, a "load more" button, or an API consumer iterating a collection.
• You can define a deterministic total order: a unique, indexed sort key. A primary key (id) works. A timestamp alone does not, because two rows can share it.

If the UI genuinely needs "jump to page 37," keyset pagination cannot provide it — there is no way to compute the cursor for an arbitrary position without walking there. In that case, keep OFFSET and accept its costs, or cap how deep users can page.

The smallest suitable design

Ecto ships no built-in pagination, and the single-key version is small enough to hand-roll without a dependency. One function takes a cursor and a page size, and does three things: order by the key, filter to rows beyond the cursor, and fetch one extra row to detect whether a next page exists.

def list_records(cursor \\ nil, page_size \\ 50) do
  query =
    from r in Record,
      order_by: [asc: r.id],
      limit: ^(page_size + 1)

  query =
    if cursor do
      from r in query, where: r.id > ^cursor
    else
      query
    end

  case Repo.all(query) do
    rows when length(rows) > page_size ->
      {page, [_next | _]} = Enum.split(rows, page_size)
      %{records: page, next_cursor: List.last(page).id, has_more: true}

    rows ->
      %{records: rows, next_cursor: nil, has_more: false}
  end
end

The page_size + 1 trick avoids a separate COUNT query: if you got more rows than a page, another page exists. The cursor you hand back is the sort-key value of the last row on the current page, not the extra row.

Everything here runs inside your application context — no special database permissions beyond what the repo already has. The one schema requirement is an index on the sort key; a primary key already provides one.

When the sort key is not unique

Sorting by inserted_at alone is a classic bug: rows sharing a timestamp can be skipped or repeated depending on how the database breaks ties. The fix is a composite key — (inserted_at, id) — with a tuple comparison:

from r in query,
  where: fragment("(?, ?) > (?, ?)", r.inserted_at, r.id, ^ts, ^id),
  order_by: [asc: r.inserted_at, asc: r.id]

Tuple comparison support differs between adapters (postgrex vs myxql), so verify this against your actual database before shipping it. You also need a composite index on (inserted_at, id) matching the sort direction, or the planner may fall back to a sort over a filtered set.

The cursor is a trust boundary

The moment the cursor travels to a client and comes back, it is untrusted input. A raw integer cursor invites clients to probe arbitrary record IDs; a malformed one can crash the query or, worse, be coerced into something you did not intend.

Minimum handling:

• Encode the cursor opaquely (Base64 of a small map is enough; signing it is better if clients are adversarial).
• On decode, validate type and range before it reaches the query: an id cursor must be a positive integer, a timestamp must parse and be within a sane window.
• Reject — do not repair — anything malformed, wrong-typed, or tampered.

Fuzz this path in tests. It is the one place where client-controlled data flows into your pagination logic, and it is cheap to get right.

Operational checks

Before calling this done, verify four things against a realistically sized table:

1. Query plan. Run EXPLAIN (via Repo.query!/2 with EXPLAIN ANALYZE on Postgres) on the generated query. You want an index scan using the sort key, not a sequential scan or a large sort step.
2. Stability under writes. An integration test that inserts rows while paginating forward should assert no duplicates and no omissions across all pages.
3. Boundaries. Test the empty table, exactly page_size rows remaining (must return has_more: false), and a cursor past the last row (must return an empty page, not an error).
4. Filter interaction. If the listing also filters (e.g., by account_id), the index must cover the filter and sort columns together — otherwise the "constant cost per page" claim breaks down as the filter narrows the index's usefulness.

Failure modes

• Non-unique sort key without a tiebreaker. Silent duplicates and gaps. No error is raised; you find out from user reports.
• Missing composite index. The query works in development and degrades in production as the table grows. This is why the EXPLAIN check is on the list.
• Cursor from a different filter context. A client holding a cursor from an unfiltered list and applying it to a filtered one gets confusing results. Bind the filter parameters into the cursor payload and reject mismatches.
• Backward pagination bolted on later. Reversing direction means inverting both the comparison and the sort, then re-reversing the result. Decide early whether you need it; retrofitting is fiddly.

Conditions that would change the design

Reach for a library such as Paginator when you need multi-field cursors, Relay-style connections, or bidirectional paging across several sort configurations — the boolean logic for composite cursors is a common source of subtle ordering bugs, and a tested library earns its dependency there. Switch back to OFFSET if numbered pages are a hard product requirement. And if the table is small and mostly static, plain limit/2 and offset/2 are simpler and perfectly fine — keyset pagination solves a problem you only have at scale.