Keyset Pagination — Efficient Database-Driven Pagination

In this tutorial, you will learn about Keyset Pagination. We cover key concepts, practical examples, and best practices to help you master this topic.

Keyset pagination uses values from database columns (typically the sort key) as pagination markers, enabling the most efficient queries by leveraging database indexes without offset scanning.

What You'll Learn

You will learn keyset pagination, how it differs from cursor-based, and how to implement it with Composite keys.

How It Works

-- First page
SELECT * FROM users ORDER BY id ASC LIMIT 20;

-- Next page using last ID from previous page
SELECT * FROM users WHERE id > $1 ORDER BY id ASC LIMIT 20;

Implementation

app.get("/api/users", async (req, res) => {
  const limit = Math.min(parseInt(req.query.limit) || 20, 100);
  const after = req.query.after ? parseInt(req.query.after) : null;

  let query;
  let params;

  if (after) {
    query = "SELECT * FROM users WHERE id > $1 ORDER BY id ASC LIMIT $2";
    params = [after, limit];
  } else {
    query = "SELECT * FROM users ORDER BY id ASC LIMIT $1";
    params = [limit];
  }

  const result = await db.query(query, params);
  const users = result.rows;

  res.json({
    data: users,
    pagination: {
      limit,
      next: users.length === limit ? users[users.length - 1].id : null,
      hasMore: users.length === limit
    }
  });
});

Common Mistakes

1. Non-sequential keys — Using UUIDs or other non-sequential keys for keyset pagination.
2. No index — Keyset pagination requires an index on the sort key.
3. Sort direction issues — For descending sort, use < instead of >.

Practice Questions

1. How does keyset pagination differ from cursor-based?
2. What Database Index is required?
3. Why does keyset work best with sequential IDs?
4. How do you handle descending sort with keyset?

Challenge

Implement keyset pagination for a blog post API. Use created_at as the keyset with a composite index. Support both ascending and descending directions.

What's Next

In the next lesson, you will compare cursor vs offset trade-offs.