Sequelize Pagination: Offset vs Cursor for Large Tables

The problem: page 50 is slow, and page 500 is worse

Your API was fast in staging. In production, requesting page 50 of a products table takes three seconds, then ten. The culprit is usually not the missing index but the way Sequelize fetches rows: limit with offset tells the database to scan and discard every row before the requested page. As the offset grows, the database does more work to return the same number of rows.

Takeaway: use offset pagination for small tables and numbered UI pages. Switch to cursor-based pagination for large tables, infinite scroll, or any endpoint where page numbers are irrelevant.

Offset pagination in Sequelize

Offset pagination is the default mental model: ask for limit rows starting at offset. It works on Sequelize 5 and 6.

const page = 50;
const pageSize = 20;

const { rows, count } = await Product.findAndCountAll({
  limit: pageSize,
  offset: (page - 1) * pageSize,
  order: [['createdAt', 'DESC']],
});

The database must read rows 1 through 980, throw them away, then return rows 981 through 1000. That is fine for page one. It becomes expensive as the offset climbs.

Why offset slows down on deep pages

Offset does not jump to a position in an index. The database typically scans from the beginning of the ordered result set and discards rows until it reaches the offset. On a table with millions of rows, deep pagination can turn a millisecond query into a multi-second scan.

You can verify this with EXPLAIN ANALYZE in your database client. Connect to the application database with SELECT permission on the table, then run:

EXPLAIN ANALYZE
SELECT * FROM "Products"
ORDER BY "createdAt" DESC
LIMIT 20 OFFSET 980;

Look for the row-removal estimate. An offset query often shows a large number of rows removed before the limit is applied. This is a read-only diagnostic, but EXPLAIN ANALYZE executes the statement, so it consumes CPU and I/O like a normal query.

Cursor pagination with keySet

Sequelize 6 introduced built-in cursor pagination through keySet. Instead of skipping rows, the query asks for rows after a known cursor value.

const pageSize = 20;
const cursor = req.query.cursor ? JSON.parse(req.query.cursor) : null;

const products = await Product.findAll({
  limit: pageSize,
  keySet: cursor,
  order: [['createdAt', 'DESC'], ['id', 'DESC']],
});

The response should include the last row's createdAt and id as the next cursor. The client sends that value back to fetch the following page.

Cursor pagination requires a stable, unique ordering column. If createdAt is not unique, add a tiebreaker such as id. Without a unique order, pages can skip or repeat rows when new data is inserted or existing rows are updated.

Projects on Sequelize 5.x do not have built-in keySet. You must implement the cursor logic manually or upgrade before using this pattern.

The include and duplicate-row trap

Pagination becomes subtler when you add include. A join can multiply rows: one product with three reviews produces three result rows before Sequelize applies limit. The returned page can then contain fewer than twenty products, or duplicate products.

Fix it with distinct: true:

const products = await Product.findAll({
  limit: 20,
  offset: 0,
  include: [{ model: Review }],
  distinct: true,
  order: [['createdAt', 'DESC']],
});

Alternatively, run two queries: first fetch the product IDs for the page, then fetch the full records for those IDs. distinct: true wraps the query in SELECT DISTINCT, which can add sorting overhead and prevent some index optimizations, so measure both approaches if the table is large.

Practical checks and limitations

• Cursor pagination does not support jumping directly to page 42. The client must pass the previous page's cursor to move forward.
• Cursor pagination requires a monotonic or stable ordering column. Rows updated in the sort column can cause skipped or repeated results.
• Sequelize 5.x lacks built-in keySet; implement manually or upgrade.
• Offset pagination is simpler to implement and works well for numbered UI pages on small to medium tables.

To verify which pattern fits your workload, run both query shapes against a copy of production data with EXPLAIN ANALYZE. Compare actual execution time and rows scanned. Then insert a new row while paginating: with offset, the new row can shift results between pages; with cursor pagination and stable ordering, it should not appear in the middle of an existing page.

These are read operations, so no rollback is needed.

Closing

Choose offset when your UI shows page numbers and the table stays small. Choose cursor when you build infinite scroll, paginate deep into a large table, or need stable results under concurrent writes. Verify with EXPLAIN ANALYZE before you ship, and make sure your ordering column is unique enough to anchor the cursor.