pagination-design

Encode the last record's sort values (e.g., created_at and id) as base64 JSON and return it as next_cursor. On the next request, decode the cursor and filter rows with WHERE created_at < value, fetching limit + 1 records to determine has_next.

Use offset pagination only for tables under 10k rows where deep linking to page numbers matters. For tables over 100k rows, use cursor or keyset pagination, which run in O(log n) with proper indexes and avoid inconsistent results when data changes.

Keyset pagination passes actual column values (like before_created_at and before_id) in the URL instead of an opaque token. It offers the same O(log n) performance as cursors but produces transparent, shareable, bookmarkable URLs.

COUNT(*) scans the entire table, becoming expensive at millions of rows. Alternatives include omitting total_count, using PostgreSQL's reltuples estimate from pg_class, or caching the count with a TTL.

Duplicates and skips happen when sorting by a non-unique column or when rows are inserted between page requests. Always add a unique tiebreaker column like id to the sort, and use cursor or keyset pagination instead of offset.

Yes. Apply filters before the cursor condition and create a composite index with filter columns first, then sort columns. For GraphQL, follow the Connections spec with edges, pageInfo, startCursor, and endCursor.