Sequelize limit/offset pagination for large datasets needs diagnosis

Question

Assuming a standard Sequelize release, the limit and offset options on Model.findAll() translate into SQL LIMIT and OFFSET clauses, providing a straightforward way to bound a query and paginate a large dataset. The goal is to keep each page response small while navigating the full result set, but the behavior of offset-based pagination under concurrent data changes and growing page numbers is not always clear to teams adopting it.

Two constraints create uncertainty. First, when rows are inserted or deleted between page requests, offset pagination can skip or repeat records because the offset is relative to the current result set rather than a stable cursor. Second, as the offset value increases, the database may need to scan and discard more rows before returning the requested page, which can cause latency to grow with the offset. These effects may be more pronounced when the ordering column lacks an appropriate index.

Before committing to an implementation, it is useful to clarify how Sequelize's limit/offset pagination should be evaluated for this workload. Should the team rely on Model.count() for total page calculations, or is a keyset approach required? What measurement approach best reveals whether offset latency is acceptable at the expected maximum page depth? Which ordering and indexing strategy should be verified to make offset pagination predictable?

Tasadduq Burney · Answer

Answer
Sequelize passes limit and offset straight to the database as LIMIT and OFFSET clauses. The query therefore behaves exactly like native SQL offset pagination.
Confirmed facts

Without a deterministic order column, concurrent inserts or deletions can cause skipped or duplicated rows between pages.
Increasing offset forces the engine to scan and discard preceding rows; latency grows unless the ordered column is indexed and the index can satisfy the order.
An index on the ordering column (plus any tie‑breaker columns) lets the planner use an index range scan, making cost roughly proportional to page size, not offset.
Model.count() returns a snapshot of rows matching the same where clause; if the count is taken outside a transaction or without repeatable‑read isolation, the total can become stale relative to the paginated data.

Steps to evaluate for your workload

Verify that every paginated query includes an explicit order on a column (or column set) that is unique or made unique with a tie‑breaker (e.g., primary key).
Check that an index exists on the same column(s) in the same order; run EXPLAIN on a sample query with a realistic offset (e.g., 100 000) and confirm the index is used and rows examined ≈ offset + limit.
Measure execution time at low, medium, and high offsets (e.g., 0, 10 000, 100 000) under a read‑only load. If latency stays flat, offset pagination is acceptable for the expected depth.
If latency grows linearly or you expect high write traffic, consider keyset (seek) pagination: use the last returned values of the ordered column(s) as a where clause with gt/gte and drop offset.
Decide whether you need an accurate total‑page count. If you can tolerate a slightly stale total, Model.count() is fine; otherwise compute the count in the same transaction/isolation level as the page query or avoid displaying a total.

Missing diagnostic detail
If you cannot confirm whether the ordering column is indexed, please provide the table definition and the column(s) used in order so a specific index recommendation can be given.

Sequelize limit/offset pagination for large datasets needs diagnosis

1 answer

Answer

Confirmed facts

Steps to evaluate for your workload

Missing diagnostic detail

Your perspective belongs here.

0 question comments