Skip to content
Sarj Standards

Python · performance

no-offset-pagination

python:no-offset-pagination

Store queries should use keyset cursors instead of `OFFSET` pagination.

Code
SARJ025
Default
error
Fix
none
Languages
python

Why

Large offsets require scanning discarded rows and can skip or repeat results during concurrent writes.

Fix

Filter on the ordered key after the last result, then apply `ORDER BY` and `LIMIT`.

Before / after

Executed by this rule’s unit tests.

Before

Page selected with an offset

app/task_store.py · focus
QUERY = "SELECT id FROM task ORDER BY id LIMIT :limit OFFSET :offset"

After

Page selected after the last ordered key

app/task_store.py · focus
QUERY = "SELECT id FROM task WHERE id > :cursor ORDER BY id LIMIT :limit"

Limits

  • Only SQL string literals in recognized store modules are analyzed.
  • Dynamic SQL, comments, prose, and BigQuery `WITH OFFSET AS` array indexing are excluded.