Offset-based pagination in Elixir

Pagination is a technique used to break a large dataset into manageable “pages” (chunks) of results. It’s essential for:

• UI rendering (e.g., showing multiple pages in Google search results)
• Reports and dashboards

There are a couple of pagination strategies but I want to discuss the simplest one which is Offset-based pagination. Before we begin there are two essential terms that you need to understand, LIMIT and OFFSET.

Limit is exactly what it sounds like - it limits the number of rows returned to the user. Offset on the other hand is the number of rows that will be skipped in order to start fetching rows. In other words, if we have an offset of 20 and a limit of 10, the first 20 records will be skipped, and then we will only fetch 10 records.

We use Ecto.query functions, limit/3 and offset/3 to do this.

  def get_related_episodes(question_embedding, offset) do
    Episode
    |> order_by([e], asc: l2_distance(e.embedding, ^question_embedding))
    |> limit(10)
    |> offset(^offset)
    |> Repo.all()
  end

A simple formula that you can use to calculate your offset is:

offset = (page_number - 1) * Limit

On the first page the offset will be 0 * 1 = 0

second page = (2 -1) * 10 = 10 ….

One disadvantage of Offset based pagination is it becomes slower as OFFSET increases because the DB must read then discard previous rows. Each call still sorts the data, and the more rows it skips, the slower it gets. That is why you should consider learning about Keyset Pagination next.