Recipe

Offset Pagination Patterns

Offset pagination is the simplest way to slice a result set into pages. It works well for small datasets and admin-facing tables, but breaks down under heavy write load. This recipe walks through three battle-tested patterns we use across Meridian endpoints, with notes on when to reach for cursor pagination instead.

1. Classic LIMIT / OFFSET

The textbook approach. Accept page andper_page query params, convert to OFFSET in SQL. Trade-off: every page beyond the first scans and discards rows, so latency climbs linearly with page number. Capper_page at 100 and refuse offsets above ~10,000.

SELECT id, title, created_at
FROM articles
WHERE workspace_id = $1
ORDER BY created_at DESC
LIMIT $2 OFFSET $3;

2. Total Count Strategies

Clients usually want a total so they can render a page picker. A nakedCOUNT(*) doubles query cost. Prefer one of three options: cache totals in a sidecar table updated by trigger, use an approximate count frompg_class.reltuples, or return has_more only and let the UI show a "Next" button.

For Meridian's admin console we return both an exact count andhas_more; the count is materialized hourly and trades freshness for predictable latency.

3. When to Switch to Cursors

Offset breaks under two conditions: deep pagination and concurrent writes. Once your dataset crosses ~1M rows, or new rows are inserted at the head faster than a user can page, switch to keyset (cursor) pagination. The cursor encodes the last seen sort key, so the query uses an index seek instead of an offset scan, and skipped or duplicated rows become impossible.

Public feeds, infinite scroll, mobile clients: cursor.
Admin tables, exports, deterministic page links: offset.
Anything driven by ML ranking that re-sorts on each call: cursor.