Offset vs Keyset Pagination for Soft‑Deleted Records in NestJS: Which Keeps Page Size Consistent?

The Direct Resolution

To maintain a consistent page size when dealing with soft-deleted records, keyset pagination (seek method) is the correct choice. Unlike offset pagination, which counts rows regardless of their visibility status, keyset pagination filters the dataset first and then fetches the next N available records based on a unique cursor, ensuring the requested limit is always met.

Why Offset Pagination Fails Consistency

Offset pagination relies on the OFFSET clause in SQL. If your query logic applies a filter for soft-deleted records (e.g., WHERE deletedAt IS NULL) after the database calculates the offset, or if the offset is calculated against the total table count including deleted rows, the resulting page size will fluctuate. Even when filtered correctly, OFFSET remains inefficient for large datasets because the database must scan and discard all preceding rows before returning the requested page.

Implementing Keyset Pagination in NestJS

Keyset pagination avoids the OFFSET keyword entirely. Instead, it uses the value of the last item from the previous page as a pointer (the cursor) for the next query.

Implementation Steps

1. Define the Cursor: Use a unique, monotonically increasing column (such as an auto-incrementing id or a precise createdAt timestamp).
2. Filter and Seek: Construct a query that filters out soft-deleted records and seeks records greater than the current cursor.
3. Return the Next Cursor: The API response must include the ID of the last record in the current set to serve as the cursor for the next request.

// Example TypeORM implementation in a NestJS service
async paginate(cursor: number, limit: number) {
  const records = await this.repository.find({
    where: {
      id: MoreThan(cursor),
      deletedAt: IsNull(),
    },
    order: { id: 'ASC' },
    take: limit,
  });

  const nextCursor = records.length > 0 
    ? records[records.length - 1].id 
    : null;

  return { data: records, nextCursor };
}

Trade-offs and Considerations

Feature	Offset Pagination	Keyset Pagination
Page Size	Inconsistent (if not carefully filtered)	Consistent
Performance	Degrades as offset increases (O(n))	Constant performance (O(log n))
Navigation	Supports random access (Page 5)	Sequential only (Next/Previous)

Verification

To verify this behavior, enable the TypeORM logger. Ensure the generated SQL for keyset pagination uses a WHERE clause for both the cursor and the deletedAt column, and contains no OFFSET clause. Test with a dataset where every second record is soft-deleted to confirm the page size remains exactly equal to the limit.

Missing Diagnostic Detail: Is your ordering column guaranteed to be unique? If you are sorting by a non-unique field (like category), you must implement a tie-breaker (e.g., ORDER BY category ASC, id ASC) to prevent records from being skipped.