Ecto Pagination Without Offset: Why Keyset Wins for Deep Lists

Your feed loads fast on page 1 and slows to a crawl on page 1,000. In Ecto there is no built-in pagination module, so the slowdown comes from how you compose limit and offset. The useful takeaway is to keep limit/offset for shallow, jumpable pages and switch to keyset cursor pagination when you need stable, constant-time scrolling.

The problem with limit and offset in Ecto

Ecto expresses pagination with ordinary query functions. A typical offset page looks like:

from(p in Post, order_by: [desc: p.inserted_at], limit: 20, offset: 40)

limit caps the rows returned, offset tells the database how many rows to skip. The database still has to scan and discard those skipped rows. On large tables deep pages get progressively slower, and if rows are inserted between requests you can see duplicates or shifted items.

Offset pagination is simple and supports jumping to an arbitrary page number. That is its strength and its cost.

How keyset pagination works with Ecto queries

Keyset pagination, also called cursor pagination, avoids skipping. Instead of saying skip N rows, you filter from the last seen row.

With a deterministic sort you remember the last value you returned and query for rows that come after it. For descending order that is a less-than filter on the sort column, then order_by and limit again. Cost stays roughly constant because the database can use an index range scan from the cursor forward.

The sort must be deterministic and unique. If you sort by a non-unique column like inserted_at, add a tiebreaker such as the primary key: order_by: [desc: p.inserted_at, desc: p.id]. Filtering must use both columns, otherwise pages can skip or repeat rows.

Worked example: id-based cursor for Posts

Assume Repo is your Ecto.Repo and Post is an Ecto schema with an integer id. The examples assume Ecto 3.x, where limit/2, offset/2, where/2, and order_by behave as shown; check the docs for your pinned version. Run the following in an iex -S mix session with read access to the database.

# first page
first = Repo.all(
  from p in Post,
  order_by: [desc: p.id],
  limit: 20
)
last_id = List.last(first).id

# next page using the cursor
next_page = Repo.all(
  from p in Post,
  where: p.id < ^last_id,
  order_by: [desc: p.id],
  limit: 20
)

Meaningful placeholders: Repo, Post, and the limit value. The where clause uses ^ to bind last_id from Elixir.

Expected checks: first and next_page should not overlap and should preserve the descending id order. A practical way to verify is to compare the max id of the next page with the min id of the first page, and to enable Ecto query logging to confirm the generated SQL uses the id index rather than scanning skipped rows.

Risks: changing the order direction or sort columns without updating the cursor filter returns wrong pages. The cursor is opaque to the client, so you cannot jump to page 50 directly.

Trade-offs and when to stay with offset

Keyset pagination cannot support arbitrary page numbers. It depends on indexed sort columns and requires clients to send back a cursor. It is ideal for infinite scroll, feeds, and audit logs where users move forward sequentially.

Offset pagination remains useful for admin tables with small total rows, or when users explicitly need page numbers. It degrades with depth and is unstable under concurrent inserts, so avoid it for deep public listings.

Community libraries such as Paginator implement cursor patterns on top of Ecto, but the underlying pattern is plain Ecto queries and can be shown without dependencies.

Check it in your app

Seed a test table, fetch page 1 with both styles, insert a new row, then fetch page 2. Offset pages will shift, keyset pages will stay stable relative to the cursor. Confirm your Ecto version supports limit/2, offset/2, where/2 and order_by as used above, and ensure the sort columns have a supporting index.

Start with keyset for any list that users scroll through. Keep offset only where page numbers are a real requirement.