Diagnosing Ecto Pagination Inconsistencies: Limit/Offset vs Repo.page

Recognizable Condition

When paginating Ecto queries you observe missing rows, duplicate entries, or query times that grow linearly with the page number.

Cause/Diagnostic Table

Ordered Checks

1. Verify the Ecto version in mix.exs; note whether it is >= 3.7.
2. Inspect the pagination code: are you using Repo.page/3 or manually building limit/2 and offset/2?
3. If manual, confirm the offset calculation: offset = (page - 1) * per_page and that both values are non‑negative integers.
4. Run the query in iex -S mix and capture the generated SQL with Ecto.Adapters.SQL.query/3 to see the actual LIMIT and OFFSET clauses.
5. Check the changeset used for any form or API input that supplies page or per_page; ensure it permits those fields and casts them to integers.
6. Execute the same query with both approaches on a small fixture set and compare the returned rows.

Fixes Tied to Findings

1. If the version is < 3.7, replace calls to Repo.page/3 with the explicit limit/2 and offset/2 formulation.
2. Correct the offset formula: ensure offset = (page - 1) * per_page and guard against negative values.
3. For Ecto >= 3.7, prefer Repo.page/3 as it offsets the calculation internally and avoids off‑by‑one errors.
4. Add a changeset validation like cast(params, [:page, :per_page]) |> validate_number(:page, greater_than: 0) |> validate_number(:per_page, greater_than: 0, less_than: 101) to prevent invalid pagination inputs.
5. When offset values become large (e.g., > 10000), consider keyset pagination using a unique ordered column instead of offset.

Escalation Criteria

• If after applying the fixes the query still returns incorrect rows, examine the underlying table for duplicate primary keys or missing indexes.
• If performance does not improve with keyset pagination, consult the database’s explain plan to verify that the chosen ordering column is indexed.
• Persistent errors in changeset validation indicate a mismatch between the schema field names and the parameters sent from the client; review the API contract.

Example: Switching from Manual Pagination to Repo.page

# Manual version (Ecto 2.x or 3.x < 3.7)
page = 2
per_page = 20
offset = (page - 1) * per_page
query =
  from(u in User,
    where: u.is_active == true,
    limit: ^per_page,
    offset: ^offset)

# Using Repo.page (Ecto 3.7+)
query = from(u in User, where: u.is_active == true)
 paginated = Repo.page(query, page: 2, per_page: 20)

To verify the generated SQL, run in an IEx session:

Ecto.Adapters.SQL.query(Repo, Ecto.Adapters.SQL.to_sql(query), [])
The returned :rows will contain the SQL string; check that it includes LIMIT 20 and the correct OFFSET value.
Practical way to check the result: inspect the struct returned by Repo.page/3 or the list from Repo.all/2 and assert that length(results) == per_page (except possibly the last page) and that the total_count field matches the number of rows in the fixture.
Limitations
Large offset values can cause the database to scan and discard many rows, degrading performance.
Integer‑typed offsets may overflow on extremely large tables; monitor the offset size.
Keyset pagination requires a sortable, unique column (often the primary key) and may not be applicable when the ordering is complex or non‑unique.