What Is Pagination? A Practical Guide for AI-Assisted Developers

Your database has 50,000 records. Your user wants to see 20. Pagination is how you bridge that gap — and AI generates it differently than you'd expect.

Why AI Coders Need to Know This

You ask Claude to "build an API for my blog posts." It works perfectly with 50 test posts. Then your app goes to production with 50,000 posts and the page takes 8 seconds to load — because the AI loaded all records in a single query with no pagination.

Or it adds pagination, but uses offset pagination, and by page 500 the query takes 3 seconds because the database is scanning 10,000 rows just to skip them.

Pagination is one of those things that "works fine" in development and breaks in production. Understanding the difference between offset and cursor pagination lets you prompt the AI correctly from the start and avoid a rewrite later.

Real Scenario

This is a common first request — and the AI will almost always generate offset pagination with SQL LIMIT/OFFSET.

What AI Generated

// Express.js API with offset pagination
router.get('/api/posts', async (req, res) => {
  // 1. Parse pagination parameters from query string
  const page = Math.max(1, parseInt(req.query.page) || 1);
  const limit = Math.min(100, Math.max(1, parseInt(req.query.limit) || 20));
  const offset = (page - 1) * limit;

  try {
    // 2. Get total count for metadata
    const countResult = await db.query('SELECT COUNT(*) FROM posts WHERE published = true');
    const totalItems = parseInt(countResult.rows[0].count);
    const totalPages = Math.ceil(totalItems / limit);

    // 3. Get the current page of posts
    const result = await db.query(
      'SELECT id, title, slug, excerpt, published_at FROM posts WHERE published = true ORDER BY published_at DESC LIMIT $1 OFFSET $2',
      [limit, offset]
    );

    // 4. Return data with pagination metadata
    res.json({
      data: result.rows,
      pagination: {
        currentPage: page,
        totalPages,
        totalItems,
        itemsPerPage: limit,
        hasNextPage: page < totalPages,
        hasPrevPage: page > 1
      }
    });

  } catch (error) {
    console.error('Pagination query error:', error);
    res.status(500).json({ error: 'Failed to load posts' });
  }
});


// --- CURSOR-BASED PAGINATION (better for large datasets) ---

router.get('/api/posts/feed', async (req, res) => {
  const limit = Math.min(100, Math.max(1, parseInt(req.query.limit) || 20));
  const cursor = req.query.cursor; // The ID of the last item on the previous page

  try {
    let query, params;

    if (cursor) {
      // Get the published_at of the cursor item, then fetch items after it
      query = `
        SELECT id, title, slug, excerpt, published_at 
        FROM posts 
        WHERE published = true 
          AND (published_at, id) < (
            SELECT published_at, id FROM posts WHERE id = $1
          )
        ORDER BY published_at DESC, id DESC 
        LIMIT $2
      `;
      params = [cursor, limit + 1]; // +1 to check if there's a next page
    } else {
      // First page: no cursor
      query = `
        SELECT id, title, slug, excerpt, published_at 
        FROM posts 
        WHERE published = true 
        ORDER BY published_at DESC, id DESC 
        LIMIT $1
      `;
      params = [limit + 1];
    }

    const result = await db.query(query, params);
    const hasMore = result.rows.length > limit;
    const items = hasMore ? result.rows.slice(0, limit) : result.rows;
    const nextCursor = hasMore ? items[items.length - 1].id : null;

    res.json({
      data: items,
      pagination: {
        nextCursor,
        hasMore,
        limit
      }
    });

  } catch (error) {
    console.error('Cursor pagination error:', error);
    res.status(500).json({ error: 'Failed to load posts' });
  }
});

Understanding Each Part

Offset pagination: how it works

The URL /api/posts?page=3&limit=20 means "skip the first 40 results and give me the next 20." The math: offset = (page - 1) × limit → (3-1) × 20 = 40. The SQL OFFSET 40 LIMIT 20 does the actual skipping.

Pros: Simple to implement, easy to understand, supports "jump to page 50."

Cons: Gets slow on large datasets (the database counts through all skipped rows). If new items are added while paginating, results can shift — you might see the same post on two pages or miss one entirely.

Cursor pagination: how it works

Instead of a page number, you pass the ID (or timestamp) of the last item you saw. The server uses a WHERE clause to fetch items after that point. There's no counting, no skipping — the database jumps directly to the right row using an index.

Pros: Consistently fast regardless of dataset size. No duplicate or missing items when data changes.

Cons: No "jump to page 50" — you can only go forward or backward. Slightly more complex to implement.

The limit + 1 trick

The cursor pagination code fetches limit + 1 items. If it gets 21 back when you asked for 20, there's a next page. You return only 20 and use the 21st item's ID as the next cursor. This avoids a separate COUNT(*) query.

Input validation

Math.max(1, ...) ensures page/limit are at least 1. Math.min(100, ...) caps the limit to prevent someone requesting 1 million records. Always validate pagination inputs.

The response metadata

Good pagination responses include metadata so the frontend knows how to render navigation: total pages, current page, whether next/previous pages exist. Cursor pagination returns a nextCursor and hasMore boolean instead.

What AI Gets Wrong

How to Debug with AI

The page is slow (offset getting expensive)

Debugging prompt: "My /api/posts endpoint with OFFSET/LIMIT pagination takes 3+ seconds on page 200. The posts table has 100,000 rows. Should I switch to cursor pagination, or can I optimize the offset approach? Here's my current query: [paste]."

Items appearing on multiple pages

Debugging prompt: "When I paginate through my blog posts and a new post is published, items shift — post #20 on page 1 appears again as post #1 on page 2. Is this a known issue with offset pagination and how do I fix it?"

Frontend not knowing when to stop

Debugging prompt: "My infinite scroll keeps requesting the next page even when there are no more results. My API returns an empty array but no metadata. What should the API response look like to tell the frontend there are no more pages?"

Tool-specific tips

• Cursor: Ask it to add EXPLAIN ANALYZE to the pagination query to see how the database executes it and whether indexes are being used.
• Claude Code: Paste both offset and cursor versions and ask: "At what dataset size will the offset version become noticeably slower? What index do I need for the cursor version to be fast?"
• Windsurf: Good at generating the frontend pagination component (page buttons or infinite scroll) that matches your API's pagination format.

What to Learn Next

• What Is a REST API? — Pagination is a core part of API design. Understanding REST conventions makes pagination patterns clearer.
• What Is SQL? — The LIMIT, OFFSET, and WHERE clauses that power pagination are SQL fundamentals.
• What Are Database Indexes? — Cursor pagination only works fast if the cursor column is indexed. Understanding indexes explains why.
• What Is Rate Limiting? — Rate limiting and pagination work together to prevent API abuse.

FAQ