SQL Pagination Patterns

Use OFFSET for small datasets and random-access pages. Use keyset/seek for deep paging and stability.

-- SQL Pagination Patterns
-- LIMIT/OFFSET vs Keyset (Seek) Pagination
--
-- OFFSET pagination is simple but can get slow on deep pages and can produce
-- duplicates/missing rows if data changes between requests.
--
-- Keyset pagination is fast and stable for "next page" navigation, but it
-- requires a deterministic sort key (usually created_at + id).

/* ---------------------------------------------------------------------------
   1) LIMIT/OFFSET (Page Number Pagination)
   ---------------------------------------------------------------------------
   Pros:
     - Simple, supports random access (page 17)
   Cons:
     - Can be slow for large offsets (DB still walks/skips rows)
     - Unstable if rows are inserted/deleted between requests
--------------------------------------------------------------------------- */

-- Example: page 1 (page_size=25)
SELECT id, title, created_at
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 25 OFFSET 0;

-- Example: page N
-- offset = (page_number - 1) * page_size
-- page_number=4, page_size=25 => offset=75
SELECT id, title, created_at
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 25 OFFSET 75;

-- Counting total rows for page counts (can be expensive on big tables)
SELECT COUNT(*)
FROM posts
WHERE status = 'published';


/* ---------------------------------------------------------------------------
   2) KEYSET / SEEK PAGINATION (Recommended for large datasets)
   ---------------------------------------------------------------------------
   Idea:
     - Instead of OFFSET, use the last row from the previous page as a cursor.
     - Fetch "rows after cursor" using a WHERE clause aligned with ORDER BY.

   Requirements:
     - A stable, deterministic ordering (include a unique tie-breaker like id)
     - Client stores the cursor (last_created_at, last_id)

   Pros:
     - Fast on deep pagination
     - More stable under inserts/deletes
   Cons:
     - Harder to jump to arbitrary page numbers
--------------------------------------------------------------------------- */

-- First page (no cursor yet)
SELECT id, title, created_at
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 25;

-- Next page using cursor from last row of previous page:
-- cursor_created_at = :last_created_at
-- cursor_id         = :last_id
--
-- For DESC ordering, we want rows "older than" the cursor:
-- (created_at, id) < (cursor_created_at, cursor_id)
--
-- PostgreSQL (supports row-value comparisons):
SELECT id, title, created_at
FROM posts
WHERE status = 'published'
  AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 25;

-- MySQL also supports row-value comparisons in many versions, but if you want
-- maximum compatibility, use the explicit OR form below:

-- Portable keyset form (works broadly):
SELECT id, title, created_at
FROM posts
WHERE status = 'published'
  AND (
    created_at < :last_created_at
    OR (created_at = :last_created_at AND id < :last_id)
  )
ORDER BY created_at DESC, id DESC
LIMIT 25;

-- Previous page (going "back") is trickier. One common approach:
-- - Use ASC fetch with > cursor, then reverse in application
-- or
-- - Keep a stack of cursors on the client
--
-- Example "prev page" fetch (ASC), then reverse rows client-side:
SELECT id, title, created_at
FROM posts
WHERE status = 'published'
  AND (
    created_at > :first_created_at
    OR (created_at = :first_created_at AND id > :first_id)
  )
ORDER BY created_at ASC, id ASC
LIMIT 25;


/* ---------------------------------------------------------------------------
   3) INDEXING TIPS (Crucial for performance)
   ---------------------------------------------------------------------------
   Your ORDER BY and cursor WHERE clause should match an index.
   Typical index for these queries:

   PostgreSQL:
     CREATE INDEX ON posts (status, created_at DESC, id DESC);

   MySQL:
     CREATE INDEX posts_status_created_id ON posts (status, created_at, id);
     (MySQL can scan the index in reverse for DESC in many cases)
--------------------------------------------------------------------------- */

-- PostgreSQL example:
-- CREATE INDEX CONCURRENTLY IF NOT EXISTS posts_pub_created_id_desc
--   ON posts (status, created_at DESC, id DESC);

-- MySQL example:
-- CREATE INDEX posts_pub_created_id ON posts (status, created_at, id);


/* ---------------------------------------------------------------------------
   4) NOTES ON CONSISTENCY
   ---------------------------------------------------------------------------
   - Always include a unique tie-breaker in ORDER BY (like id).
     Otherwise, paging can skip/duplicate rows when many rows share the same
     created_at.
   - Keyset pagination works best when ordering by immutable columns.
     (created_at is usually fine; updated_at can change and reshuffle results.)
--------------------------------------------------------------------------- */