Skip to content
Sarj Sarj Sarj Standards

no-offset-pagination

Dynamic OFFSET pagination in query SQL should use a stable cursor.

SARJ107 Rule source Tests Catalog JSON

Why

A parameterized offset grows with the requested page, forcing the database to scan and discard earlier rows. Concurrent inserts or deletes can also shift page boundaries, repeating or skipping rows.

Fix

Use keyset pagination over a stable, immutable ordering. Match the cursor predicate to the ORDER BY direction and add a unique tie-breaker. Keep dynamic OFFSET only for an intentional random-access contract or demonstrably bounded/static data, with an exact SARJ107 suppression.

Examples

Before — flagged Pagination that scans skipped rows
queries/events.sql
SELECT
  created_at,
  id
FROM
  event
ORDER BY
  created_at DESC,
  id DESC
LIMIT
  $1
OFFSET
  $2;
After — preferred Pagination bounded by a stable cursor
queries/events.sql
SELECT
  created_at,
  id
FROM
  event
WHERE
  (created_at, id) < ($1, $2)
ORDER BY
  created_at DESC,
  id DESC
LIMIT
  $3;

Formerly: no-limit-offset