Choosing Between Offset and Keyset Pagination in Sequelize for Large Append-Only Tables

The problem with deep offset pagination

When a Sequelize list endpoint serves an append-only table—one where rows are inserted but rarely updated or deleted, such as event logs or audit trails—the pagination strategy becomes a performance decision, not just a coding preference. Offset pagination is trivial to implement and returns a total count, but the database must scan and discard every row before the requested page. Keyset pagination, sometimes called the seek method, avoids that scan by remembering the last sort key value as a cursor and asking only for rows after it. The trade-off is that keyset pagination moves forward only and cannot report a total page count without an extra query.

Decision constraints

Use offset pagination when result sets are small, page depth is shallow, and the interface needs jump-to-page navigation or a total count. Use keyset pagination when the table is large, clients consume data sequentially, and consistent ordering under concurrent inserts matters more than random access.

Aspect	Offset pagination	Keyset pagination
Query pattern	LIMIT / OFFSET	WHERE sort_key > cursor
Total count	Available via findAndCountAll	Not available by default
Random access	Yes (jump to page N)	No (forward-only)
Deep page cost	Grows with offset	Stable
Concurrent inserts	Rows may shift or duplicate	Stable if sort key is unique
Client complexity	Low	Moderate (track cursor)

Trade-offs in practice

Offset pagination keeps client logic simple. Model.findAndCountAll({ limit, offset, order }) returns both the rows and a total count in one call, which maps cleanly to page-number UIs. The hidden cost is latency: as offset grows, the database reads and discards more rows before applying LIMIT. On MySQL, PostgreSQL, and SQLite, large offsets can trigger slow scans and timeouts. The count query issued by findAndCountAll is also expensive on large tables and may be inconsistent with concurrent writes.

Keyset pagination pushes a small amount of state to the client—the cursor. A query like Model.findAll({ where: { id: { [Op.gt]: lastId } }, limit, order }) lets the index seek directly to the cursor position. This keeps deep-page latency flat and prevents row shifting when new rows are inserted. The cost is that the sort key must be unique and indexed; otherwise ties produce duplicates or skips. Keyset pagination also cannot jump to an arbitrary page number without replaying earlier pages.

Offset pattern with findAndCountAll

const { Op } = require('sequelize');
const { Event } = require('./models');

async function getEventsOffset(page = 1, pageSize = 50) {
  const { rows, count } = await Event.findAndCountAll({
    where: { status: 'active' },
    limit: pageSize,
    offset: (page - 1) * pageSize,
    order: [['id', 'ASC']]
  });
  return { rows, total: count, page, pageSize };
}

Keyset pattern with a cursor

const { Op } = require('sequelize');
const { Event } = require('./models');

async function getEventsKeyset(lastId = 0, pageSize = 50) {
  const rows = await Event.findAll({
    where: {
      id: { [Op.gt]: lastId },
      status: 'active'
    },
    limit: pageSize,
    order: [['id', 'ASC']]
  });
  const nextCursor = rows.length ? rows[rows.length - 1].id : null;
  return { rows, nextCursor };
}

Validating stability and cost before rollout

Before committing to either approach, verify the behavior in your own environment. The examples above assume Sequelize v6 or v7 syntax and a dialect that supports standard SQL predicates; confirm compatibility with your installed version.

• Inspect generated SQL. Enable logging with logging: console.log in your Sequelize options and confirm that the keyset query includes a WHERE clause on the indexed sort column, an ORDER BY, and a LIMIT, without an OFFSET clause.
• Check index usage. Run your database's EXPLAIN command on the generated SQL. For PostgreSQL, use EXPLAIN ANALYZE; for MySQL and SQLite, use EXPLAIN. Verify that the plan uses an index scan or index seek on the sort column, not a sequential scan.
• Test concurrent inserts. Begin paginating with keyset, insert a new row with a sort key greater than your current cursor, and fetch the next page. Confirm the new row appears exactly once and earlier rows are not duplicated. Repeat with offset pagination and observe whether rows shift.
• Measure deep-page latency. Seed a table with enough rows to make offset cost visible—tens of thousands is usually sufficient for local testing. Compare response times for offset page 1 versus a deep page, then compare the same depth using keyset.

Limitations and dialect caveats

• findAndCountAll issues two queries; the count may be expensive on large tables and can be inconsistent with concurrent writes.
• Offset pagination with large offsets can cause slow scans and timeouts in production on MySQL, PostgreSQL, and SQLite.
• Keyset pagination requires a unique, indexed sort column. Using a non-unique column such as createdAt without a tiebreaker can produce unstable results.
• Paranoid models automatically add a deletedAt IS NULL filter. If you combine soft deletes with keyset pagination, account for that column in your cursor predicate.
• If you need snapshot consistency across multiple pages, wrap reads in a transaction with repeatable read isolation. Sequelize exposes isolation levels through transaction options, but support and semantics vary by dialect and version. Verify that your database honors the requested level before relying on it.

A practical check

After implementing either approach, request the first page and then request the page after the last returned row. For offset, verify that count matches your expected total. For keyset, verify that nextCursor advances and that no primary key appears twice across pages. Inspect the logged SQL to ensure the WHERE clause targets the indexed column and ORDER BY matches the cursor direction. If the keyset plan shows a sequential scan, add an index on the sort column before rollout.