AI Rules for Pagination Patterns

AI Fetches Everything and Paginates in JavaScript

AI generates the most dangerous pagination pattern: fetch all records from the database, then slice in application code. const allUsers = await db.users.findAll(); const page = allUsers.slice((pageNum - 1) * 20, pageNum * 20). This works with 100 records and crashes with 100,000 — loading the entire table into memory on every page request. It is the single most common performance catastrophe in AI-generated code.

Real pagination happens at the database level: SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 40. The database returns only the requested page — not the entire table. AI does not generate database-level pagination without explicit rules because the fetch-all approach is simpler to write and works in development (small datasets).

These rules cover three pagination strategies: offset (simplest, has limitations), cursor (best for APIs), and keyset (best for large datasets). Each has trade-offs — the right choice depends on your use case.

Rule 1: Offset Pagination with Database LIMIT/OFFSET

The rule: 'Use SQL LIMIT and OFFSET for offset-based pagination: SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 40. In ORMs: db.users.findMany({ take: 20, skip: 40, orderBy: { createdAt: "desc" } }). Always return pagination metadata: { data: users, meta: { page: 3, pageSize: 20, totalCount: 1547, totalPages: 78 } }. Never fetch all records — LIMIT/OFFSET operates on the database server.'

For limitations: 'Offset pagination has two problems at scale: 1) OFFSET N scans N rows before returning results — page 1000 is slow because the DB scans 20,000 rows to skip them. 2) Items shift between pages when records are inserted or deleted — page 2 might show items that were on page 1 a moment ago. For small datasets (<10K rows) and traditional pagination UIs, offset is fine.'

For total count: 'COUNT(*) on large tables is slow (full table scan without an index). Options: cache the count (refresh periodically), use an estimated count (pg_stat_user_tables.n_live_tup for PostgreSQL), or skip total count entirely and use has-more pagination (return 21 items, display 20, has_more = true if 21 items returned).'

• LIMIT + OFFSET at the database level — never fetch all and slice in JS
• Return metadata: page, pageSize, totalCount, totalPages
• Offset limitation: slow on deep pages (OFFSET 10000 scans 10000 rows)
• Offset limitation: items shift when records are added/deleted between pages
• Fine for <10K rows and traditional page-number navigation

Rule 2: Cursor-Based Pagination for APIs

The rule: 'Use cursor-based pagination for all APIs: return a cursor (encoded pointer to the last item) instead of page numbers. Request: GET /api/users?limit=20&cursor=eyJpZCI6MTAwfQ. Response: { data: users, nextCursor: "eyJpZCI6MTIwfQ", hasMore: true }. The cursor encodes the position — the next page starts after that position. No OFFSET scan, no item shifting, works at any scale.'

For implementation: 'Cursor is typically the ID or timestamp of the last item, base64-encoded: const cursor = Buffer.from(JSON.stringify({ id: lastItem.id })).toString("base64"). Query: WHERE id > decodedCursor.id ORDER BY id ASC LIMIT 20. This uses an index scan (fast at any page depth) instead of OFFSET (slow at deep pages).'

For the API contract: 'Use the Relay connection spec for GraphQL: { edges: [{ node, cursor }], pageInfo: { hasNextPage, endCursor } }. For REST: { data, nextCursor, hasMore }. The cursor is opaque to the client — they pass it back without parsing. This lets you change the cursor implementation without breaking clients.'

Rule 3: Keyset Pagination for Large Datasets

The rule: 'Use keyset pagination (seek method) for datasets with millions of rows. Instead of OFFSET, use a WHERE clause on the sort column: SELECT * FROM events WHERE created_at < :last_seen_timestamp ORDER BY created_at DESC LIMIT 20. This is O(1) regardless of page depth — the database uses the index directly, no matter how deep the page.'

For compound keys: 'When sorting by a non-unique column (created_at), use a compound key to break ties: WHERE (created_at, id) < (:last_timestamp, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20. The compound key ensures: consistent ordering even when timestamps are identical, and no duplicate/missing items across pages.'

For the trade-off: 'Keyset pagination cannot jump to arbitrary pages — no "go to page 50". It only supports: next page and previous page. This is perfect for: infinite scroll, activity feeds, log viewers, and any UI where users navigate sequentially. For UIs that need page numbers, use offset pagination (accept the deep-page performance cost).'

• WHERE sort_column < :last_value — O(1) at any depth, uses index
• Compound keys for non-unique sort: (created_at, id) < (:ts, :id)
• No page jumping — next/previous only — perfect for feeds and infinite scroll
• Millions of rows: keyset is fast everywhere, offset is slow on deep pages
• Use offset when page numbers are required — keyset when sequential navigation suffices

Rule 4: Infinite Scroll Implementation

The rule: 'Use cursor or keyset pagination for infinite scroll — never offset (items shift on insert/delete, causing duplicates and gaps). Use Intersection Observer to detect when the user approaches the bottom: const observer = new IntersectionObserver((entries) => { if (entries[0].isIntersecting && hasMore) fetchNextPage(); }). With TanStack Query: useInfiniteQuery({ queryKey, queryFn, getNextPageParam: (lastPage) => lastPage.nextCursor }).'

For the loading pattern: 'Show a loading indicator at the bottom while fetching. Keep existing items visible — never show a full-page spinner. Use a sentinel element (empty div) at the bottom as the intersection target. Prefetch the next page when the user is 80% through the current page — by the time they scroll to the bottom, the next page is cached.'

For virtual scrolling: 'For very long lists (10K+ items rendered), use virtual scrolling: @tanstack/react-virtual, react-window, or react-virtuoso. Only DOM elements in the viewport are rendered — scroll position determines which items are in the viewport. This prevents DOM node count from growing linearly with scrolled content.'

Rule 5: Pagination API Design

The rule: 'Standardize pagination parameters across all endpoints: limit (items per page — default 20, max 100), cursor (opaque string for cursor-based), page (number for offset-based). Always validate: limit must be 1-100 (reject larger — prevent fetching the entire table), cursor must be a valid base64 string, page must be a positive integer. Default limit if not provided — never unlimited.'

For the response: 'Always return pagination metadata alongside data: { data: [...], pagination: { nextCursor, hasMore, totalCount (if offset-based) } }. The client needs: data to render, hasMore to show/hide the load more button, and nextCursor/nextPage for the next request. Without metadata, the client cannot know if more data exists.'

For consistency: 'Use the same pagination structure on every list endpoint — users, orders, products, comments. The client pagination component works identically regardless of the data type. Different pagination structures per endpoint = different client code per endpoint = maintenance nightmare.'

• Standardize: limit (1-100, default 20), cursor or page — validated
• Response: { data, pagination: { nextCursor, hasMore, totalCount } }
• Same structure on every list endpoint — one client pagination component
• Default limit if not provided — never unlimited — max 100
• Validate all params: limit range, cursor format, page positive integer

Complete Pagination Rules Template

Consolidated rules for pagination patterns.

• Database-level: LIMIT/OFFSET or WHERE cursor — never fetch all and slice in JS
• Offset for page-number UIs (<10K rows) — cursor for APIs — keyset for large datasets
• Cursor: opaque, base64-encoded — client passes back without parsing
• Keyset: WHERE (sort_col, id) < (:val, :id) — O(1) at any depth
• Infinite scroll: cursor + IntersectionObserver + useInfiniteQuery — prefetch at 80%
• Virtual scrolling for 10K+ rendered items: react-virtual, react-window
• Standardize: limit (1-100), cursor/page — same structure on every endpoint
• Always return pagination metadata: nextCursor, hasMore, totalCount