Comment by nuttingd

Pagination is harder than it seems to get right.

I think pagination is only predictable under these conditions:

1) The offset used for the next fetch must be based on a pointer to a unique key. We can't rely on the number of rows previously seen.

With this rule, deletes which occur to rows in previous pages will not cause unpredictable contractions.

2) The paged result set must be sorted based on a monotonically increasing field, like created_at, plus enough other fields to create a unique key. You could lean on the PK for this, i.e.: ORDER BY (created_at, id) ASC.

With this rule, new inserts which occur during page enumeration will only affect unseen pages (and we'll see them eventually)

The API call looks roughly like this:

  /orders/?region=US&offset=(2022-03-12T07:05:58Z&ord_1234)&limit=100

The DB query looks roughly like this:

  SELECT *
  FROM orders
  WHERE (created_at, id) > (:offset_created_at, :offset_id)
  OR (
    :offset_created_at IS NULL 
    AND :offset_id IS NULL
  )
  ORDER BY (created_at, id) ASC
  LIMIT :page_size

EDIT: formatting