Stable pagination patterns for PostgreSQL

Question

Prerequisites:

• PostgreSQL database with a stable primary key on the paginated table.
• A client that can store a small cursor object (values of the ORDER BY columns) between page requests.

Implementation decision:

• Define a deterministic ORDER BY: choose the business column (e.g., created_at DESC) then append a unique tie-breaker (e.g., id DESC). This ensures every row has a reproducible position.
• Choose between OFFSET+LIMIT and keyset (seek) pagination based on expected churn and UX:
  • OFFSET+LIMIT: keeps numbered pages and random access, works fine for small or infrequently changing datasets. Requires deterministic ORDER BY to minimize nondeterminism but still shifts when rows are inserted ahead of offsets.
  • Keyset (seek) pagination: client sends the last-seen ORDER BY values as a cursor and asks for the next block after that anchor. This avoids skipping or duplicating items already seen when new rows arrive.

Expected result:

• With a deterministic ORDER BY, repeated requests over the same snapshot reproduce the same page contents.
• Keyset pagination preserves the user’s traversal across concurrent inserts: new items appear on earlier pages, but the continuation from the last anchor stays stable.

One limitation:

• Keyset pagination is not suited for “jump to page 57” scenarios or queries that require arbitrary offsets; it’s optimized for sequential consumption and requires the client to retain the cursor values.

(The server will insert the verified code example showing both approaches.)

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