Designing stable pagination in PostgreSQL

Problem: a user navigates a paginated list and finds items moved between pages after concurrent inserts or deletes. That surprising behavior usually stems from underspecified ordering in the query.

Implementation summary (no code here — server injects the verified example):

• Always define a complete ORDER BY. Use your primary business column (for example created_at DESC) and add a unique tie-breaker (usually the primary key) so each row has a deterministic position.
• If users need numbered-page random access and the dataset is small or rarely changing, OFFSET + LIMIT with a deterministic ORDER BY is acceptable.
• If rows are inserted or removed frequently and users browse forward/backward from the newest items, use keyset (seek) pagination. The client stores the last-seen row values (the cursor) and requests the next block after that anchor.

Expected behavior:

• Deterministic ORDER BY guarantees the same logical ordering every time the same snapshot of data is queried. That prevents inconsistent tie-breaking across planner or scan variations.
• OFFSET counts positions. When items are inserted ahead of an offset, later pages shift and can show duplicates or gaps.
• Keyset pagination anchors on real row values, so continuing from a cursor returns the next items after that anchor even if new items were added before it.

Limitations and tradeoffs:

• Keyset pagination does not support arbitrary “go to page N” navigation easily. It’s designed for sequential traversal.
• OFFSET with large offsets is inefficient because the database still computes and discards the skipped rows; it’s also fragile for changing lists.
• You must include the same columns in the cursor that define the ORDER BY; otherwise the continuation point won’t match the ordering.

Use this rule-of-thumb for team docs: never paginate without ORDER BY; always add a unique tie-breaker when equal values are possible; treat OFFSET as positional convenience, not a stability guarantee; prefer keyset when row churn would make page numbers misleading.

(The server will append the canonical article link and insert the verified example implementation.)

Working example

// main.mjs
// Provides small, pure functions that demonstrate LIMIT/ORDER BY behavior for pagination
// Works on in-memory arrays of {id: number|string, created_at: ISOString, ...}
// No side effects on import.

// Sort helper: accepts an array of sort specifiers like [{key:'created_at', dir:'desc'}, {key:'id','dir':'asc'}]
// If no tie-breaker provided, the sort only uses the provided keys.
export function sortRows(rows, orderSpecs = [{key: 'created_at', dir: 'desc'}]) {
  const cmp = (a, b) => {
    for (const spec of orderSpecs) {
      const {key, dir = 'asc'} = spec;
      const av = a[key];
      const bv = b[key];
      if (av === bv) continue;
      // handle ISO date strings naturally by string comparison (ISO sorts chronologically)
      if (av == null) return dir === 'asc' ? -1 : 1;
      if (bv == null) return dir === 'asc' ? 1 : -1;
      if (av < bv) return dir === 'asc' ? -1 : 1;
      return dir === 'asc' ? 1 : -1;
    }
    return 0; // equal on all provided keys
  };
  // slice to avoid mutating input
  return rows.slice().sort(cmp);
}

// OFFSET + LIMIT pagination: mimic SQL's ORDER BY ... LIMIT ... OFFSET ...
// rows: array, orderSpecs: as above, limit: number, offset: number
export function paginateOffset(rows, orderSpecs, limit, offset) {
  if (!Number.isInteger(limit) || limit < 0) throw new TypeError('limit must be non-negative integer');
  if (!Number.isInteger(offset) || offset < 0) throw new TypeError('offset must be non-negative integer');
  const sorted = sortRows(rows, orderSpecs);
  return sorted.slice(offset, offset + limit);
}

// Keyset (seek) pagination: return the first `limit` rows strictly before/after a cursor value
// direction: 'after' for newer -> older navigation with created_at DESC; 'before' for the reverse
// cursor is an object with the same keys as the leading orderSpecs, e.g. {created_at: '2026-01-01T00:00:00Z', id: 42}
// This function assumes orderSpecs is non-empty and that cursor provides values for the leading keys.
// It returns {rows: [...], nextCursor: {...}|null}
export function paginateKeyset(rows, orderSpecs, limit, cursor = null, direction = 'after') {
  if (!orderSpecs || orderSpecs.length === 0) throw new TypeError('orderSpecs must be provided');
  if (!Number.isInteger(limit) || limit <= 0) throw new TypeError('limit must be positive integer');
  // Build comparator for orderSpecs so we can test row vs cursor
  const compareRowToCursor = (row, cur) => {
    for (const spec of orderSpecs) {
      const key = spec.key;
      const dir = spec.dir || 'asc';
      const rv = row[key];
      const cv = cur[key];
      if (rv === cv) continue;
      if (rv == null) return dir === 'asc' ? -1 : 1;
      if (cv == null) return dir === 'asc' ? 1 : -1;
      if (rv < cv) return dir === 'asc' ? -1 : 1;
      return dir === 'asc' ? 1 : -1;
    }
    return 0;
  };

  // For keyset, we must sort the whole set in the same ORDER BY direction
  const sorted = sortRows(rows, orderSpecs);

  let filtered;
  if (!cursor) {
    filtered = sorted;
  } else {
    if (direction === 'after') {
      // For 'after', we want rows that come after the cursor in the sorted array.
      // compareRowToCursor returns negative when row sorts before cursor, positive when after.
      filtered = sorted.filter(r => compareRowToCursor(r, cursor) > 0);
    } else {
      // 'before' -> rows that come before cursor
      filtered = sorted.filter(r => compareRowToCursor(r, cursor) < 0);
    }
  }

  const page = filtered.slice(0, limit);
  const nextCursor = page.length === 0 ? null : Object.fromEntries(orderSpecs.map(s => [s.key, page[page.length - 1][s.key]]));
  return {rows: page, nextCursor};
}

Read the full article: https://chriseugenerodriguez.com/blog/stable-postgresql-pagination