Ecto keyset pagination: replacing offset pages that drift

The page that gets slower every week

A list endpoint built with limit and offset looks fine at launch. Months later, page 40 takes noticeably longer than page 1, and users report seeing the same row twice while scrolling. Both symptoms come from the same place: offset pagination asks the database to produce and discard every row before the page you want, and it identifies rows by position rather than by value. Insert a row at the top and every later page shifts by one.

The fix is keyset pagination, also called cursor pagination: order by a stable, unique column and fetch rows strictly after the last value you saw. In Ecto you build it with order_by, where, and limit — no special helper required. The rest of this post shows the query, the cursor-encoding decision, and one API that looks like pagination but is not.

Offset vs keyset: what actually changes

Offset pagination becomes LIMIT 20 OFFSET 400. The planner still walks 400 rows before returning anything, and the result depends on the table's state at query time. Keyset pagination becomes a range predicate: WHERE (inserted_at, id) > ($1, $2) ORDER BY inserted_at, id LIMIT 21. With an index on the ordering columns, the database seeks straight to the boundary, and the result stays stable even if rows are inserted or deleted between requests.

Aspect	OFFSET	Keyset cursor
Cost of a deep page	Scans and discards N rows	Index seek to the boundary
Stability under inserts	Rows shift between pages	Stable, value-based
Jump to page 37	Possible	Not cheaply
Requires unique ordering key	No	Yes, plus a tiebreaker

A worked keyset query in Ecto

Assume a posts table with inserted_at and id, and an index on (inserted_at, id). The cursor is the pair taken from the last row of the previous page. Fetch one extra row to learn whether a next page exists.

defmodule MyApp.PostCursor do
  import Ecto.Query

  @page_size 20

  def page(repo, cursor) do
    query =
      from(p in MyApp.Post,
        order_by: [asc: p.inserted_at, asc: p.id],
        limit: @page_size + 1
      )

    query =
      case cursor do
        nil ->
          query

        {inserted_at, id} ->
          where(query,
            [p],
            p.inserted_at > ^inserted_at or
              (p.inserted_at == ^inserted_at and p.id > ^id)
          )
      end

    rows = repo.all(query)
    {page_rows, next_cursor} = split(rows, @page_size)
    %{rows: page_rows, next_cursor: next_cursor}
  end

  defp split(rows, size) when length(rows) > size do
    {Enum.take(rows, size), cursor_for(Enum.at(rows, size - 1))}
  end

  defp split(rows, _size), do: {rows, nil}

  defp cursor_for(%{inserted_at: ts, id: id}), do: {ts, id}
end

The explicit or form is deliberate: row-value comparisons such as (a, b) > (x, y) are not portable across every adapter, while the expanded predicate is. Run this from a module in your application rather than pasting it into a shell against production data.

The caller must encode next_cursor before returning it to a client. A plain {timestamp, id} tuple is fine internally, but expose it as an opaque token — a signed Base64 string, for example — so clients cannot forge or misread it. Decoding must validate shape and types before the tuple reaches where/3; a malformed cursor should produce an empty page or a 400, not a cast error deep inside the query.

Two details matter more than they look. First, the ordering column must be unique or paired with a unique tiebreaker, otherwise rows sharing an inserted_at value can be skipped or repeated at page boundaries. Second, the comparison must match the column type: if inserted_at is :naive_datetime_usec, the decoded cursor has to be that same type, not a string.

The preload helper that looks like pagination

Ecto.Query.take/2 limits how many associated records are loaded per parent row inside a preload. It is not a top-level page function, and applying it to a normal query will not do what a paginating caller expects. If you want "the five most recent comments for each post," that is a preload concern. If you want "posts 21 through 40," that is order_by plus limit plus a cursor. Check the Ecto.Query module documentation for your installed version before depending on specific preload options.

Trade-offs and how to check your work

Keyset pagination gives up random access: there is no cheap "jump to page 37," because page numbers are positions and cursors are values. It also needs an index that matches both the filter and the ordering. A cursor on (inserted_at, id) will not help a query filtered by author_id unless the index leads with that column. And it only works on an ordering key that is stable enough to be meaningful; a frequently updated column makes a poor cursor.

To verify a change like this:

1. Confirm the resolved version by running grep '"ecto"' mix.lock in the project root. The lock file records the exact version your build uses.
2. Inspect the generated SQL with Ecto.Adapters.SQL.to_sql(:all, Repo, query), which returns the statement and its parameters. Check that the boundary is a range comparison rather than a subquery or NOT IN.
3. Ask the database what it does with that statement. In psql, prefix it with EXPLAIN (ANALYZE, BUFFERS) and look for an index scan instead of a sequential scan with a large row-removal count. Run this on a copy or a read replica, not on production.
4. Write a test that inserts several rows sharing the same inserted_at, pages through with the cursor, and asserts that the union of pages equals the full result set with no duplicates.

The practical next step is small: pick one list endpoint, add the composite index, and replace its offset with a cursor. If an endpoint genuinely needs page numbers — an admin table with a page selector, say — keep offset pagination there and accept the cost, but cap how deep it can go.