Keyset Pagination in Sequelize: Skip OFFSET for Faster Deep Pages

Problem: OFFSET pagination slows down as you go deeper

When you use Model.findAndCountAll({ limit, offset }) Sequelize issues two queries: a COUNT(*) to get the total rows and a SELECT … LIMIT … OFFSET … to fetch the page. On a table with hundreds of thousands of rows the database must still scan and discard the offset rows before it can return the requested page. As the offset grows, the scan becomes more expensive, so page 47 is noticeably slower than page 2.

Why OFFSET hurts performance

The OFFSET clause does not stop the engine from reading rows; it merely tells it to skip a certain number after the sort order is applied. Without an index that matches the ORDER BY clause the database may even sort the entire result set before applying the offset, turning a simple page request into a full table scan.

In addition, when the query includes a hasMany join, Sequelize may rewrite the query to avoid duplicate rows, which can cause the COUNT(*) to return an inflated number. This “wrong total” bug is a common source of confusion in admin grids that rely on findAndCountAll.

Implementing keyset pagination with Sequelize

Keyset (or cursor‑based) pagination replaces the offset with a WHERE clause that points to the last row seen on the previous page. Assuming a unique, monotonic sort key id, the query for the next page looks like:

const { Op } = require('sequelize');
const PAGE_SIZE = 50;

async function getNextPage(lastSeenId) {
  return await MyModel.findAll({
    where: { id: { [Op.lt]: lastSeenId } }, // rows before the cursor
    order: [['id', 'DESC']],
    limit: PAGE_SIZE
  });
}

// For the first page you can omit the where clause or use a very high value:
const firstPage = await getNextPage(Number.MAX_SAFE_INTEGER);
const nextCursor = firstPage.length ? firstPage[firstPage.length - 1].id : null;

The cursor itself is simply the value of the sort key from the last row returned. In practice you should encode it (e.g., base64‑JSON) before sending it to the client and decode it on the next request.

Handling duplicate sort values with a tiebreaker

Using only createdAt as the sort key can cause rows to be skipped or duplicated when multiple rows share the same timestamp. The standard remedy is a composite key: the timestamp plus a unique column such as id. The query then becomes:

async function getNextPage(lastSeen) {
  // lastSeen is an object { createdAt: '2026-09-01T12:34:56.000Z', id: 12345 }
  return await MyModel.findAll({
    where: {
      [Op.or]: [
        { createdAt: { [Op.lt]: lastSeen.createdAt } },
        {
          createdAt: { [Op.eq]: lastSeen.createdAt },
          id: { [Op.lt]: lastSeen.id }
        }
      ]
    },
    order: [['createdAt', 'DESC'], ['id', 'DESC']],
    limit: PAGE_SIZE
  });
}

This guarantees a strict, repeatable ordering even when the timestamp column has duplicates.

Trade‑offs and when to choose keyset

Keyset pagination gives you roughly constant query time regardless of how deep you go, but it sacrifices two conveniences of offset pagination:

• Random access – you cannot jump directly to “page 47” without walking through the preceding pages.
• Total page count – the COUNT(*) is omitted, so you cannot display “X of Y pages” without an additional expensive query.

These trade‑offs make keyset pagination ideal for infinite scroll feeds, mobile APIs, or any situation where clients consume data sequentially. For admin interfaces that need random page jumps, offset pagination may still be acceptable if the table is small or if you can tolerate the performance cost.

Actionable next steps

1. Verify your Sequelize version: run npm ls sequelize and confirm you are on v6 (the Op API shown above). Adjust the operator syntax if you are on v5.
2. Check that an index exists on the sort column(s). In MySQL you can run SHOW INDEX FROM my_table WHERE Column_name IN ('createdAt','id');; in PostgreSQL use \\d my_table. The index must match the ORDER BY list.
3. Add a small benchmark script that runs EXPLAIN on both the offset query and the keyset query against a table with a few hundred thousand rows. Compare the rows examined; the keyset plan should show a dramatically lower number.
4. If you detect duplicate timestamps, introduce the tiebreaker as shown in the composite‑key example and re‑run the benchmark to confirm no rows are skipped or duplicated.
5. Wrap the cursor encoding/decoding logic in a utility function, treat incoming cursors as untrusted input, and validate their type and range before building the WHERE clause.

By moving to keyset pagination you eliminate the growing cost of deep pages and avoid the inflated‑count pitfalls of joined findAndCountAll queries. Start with a single endpoint, measure the improvement with EXPLAIN, then roll the pattern out to the rest of your API.