Cursor‑Based Pagination in Phoenix + Ecto: Solving the Endless‑Scroll Bottleneck

Why offset pagination hurts large datasets

When a Phoenix controller asks the database for “page 3” using OFFSET 20 LIMIT 20, the query must scan the first 20 rows before returning the next 20. With thousands or millions of rows this becomes CPU‑intensive and can exhaust memory on the server.

Cursor‑based pagination: the thesis

The alternative is cursor pagination. Instead of an offset, the query uses a stable primary key value as a cursor. Each page fetches rows where the key is greater than the last key from the previous page, giving constant‑time look‑ups and eliminating the performance cliffs of OFFSET.

How Ecto implements cursor pagination

Ecto 6.0 introduced Repo.cursor/3. You call it on a query, supply a limit, and optionally an after value that represents the cursor. The cursor is usually the primary key of the ordered row.

• Ordering: specify an order_by clause that sorts by a unique, indexed column (typically the primary key).
• Cursor: the value of that column from the last row of the previous page.
• SQL generated: Ecto adds WHERE id > $1 (or the appropriate column) instead of OFFSET n.

The cursor is passed as a parameter, not a string, so the database can use an index to jump directly to the starting row.

Worked example: Blog.Post list

Assume a schema Blog.Post with a unique, indexed id column.

# In a controller or LiveView
posts = Repo.all(
  from(p in Blog.Post,
    order_by: [asc: p.published_at]),
  limit: 20
)
# Capture the cursor from the last record
after_cursor = Enum.last(posts) |> case do
  nil -> nil
  post -> post.id
end

# Fetch the next page
next_posts = Repo.all(
  from(p in Blog.Post,
    where: p.published_at > ^after_cursor,
    order_by: [asc: p.published_at]),
  limit: 20
)

The SQL that Ecto emits for the second call looks like:

SELECT * FROM "posts"
WHERE "published_at" > $1
ORDER BY "published_at" ASC
LIMIT $2;

Notice the WHERE id > ? (or the timestamp column) clause – the database can use the index to jump directly to the starting row, giving O(1) fetch time regardless of how many rows precede the page.

Trade‑offs and limitations

Cursor pagination shines when you can always move forward, but it has drawbacks:

• It cannot efficiently retrieve an arbitrary page number; you must walk the pages sequentially.
• The ordering column must be unique and indexed; duplicate keys or missing indexes degrade performance.
• If the underlying data changes (e.g., a post is reordered by a new timestamp), the cursor may become stale, causing duplicate or missing rows.
• Ecto versions before 6.0 lack Repo.cursor, so older Phoenix apps need to upgrade Ecto or implement custom logic.

Jumping to page 5 requires storing intermediate cursors, which adds complexity compared to a simple OFFSET request.

Verification steps

1. Run the initial Repo.all query in IEx and capture the id of the last row as after_cursor.
2. Execute the same query again with where: p.id > ^after_cursor and limit: 20.
3. Confirm that the new rows are exactly the continuation of the previous set (no overlap, no gaps).
4. Enable Ecto debug logging (Ecto.Logging.console) and verify that the generated SQL contains a WHERE id > ? clause.

Actionable closing

If your application suffers from slow endless‑scroll loading, replace OFFSET‑based list calls with a cursor built from the primary key. Ensure the column is unique and indexed, and test the two‑step verification above. For Phoenix LiveView, the same cursor can be passed to the client as a hidden token, allowing the UI to request the next slice without re‑querying the whole table.