Pagination strategies overview

You’ve got the two classics already in hand: offset-based (skip/limit) and keyset (a.k.a. cursor-based) pagination. But the world of pagination has a few more tricks depending on what trade-offs you care about (speed, consistency, UX). Here’s a quick map:

1. Offset-based pagination

   • Skip X, take Y.

   • Simple, flexible, but gets slower on large datasets because the database has to scan through rows.

2. Keyset / Cursor-based pagination

   • Use a stable ordering key (like id or created_at) and fetch rows after/before that.

   • Very fast and consistent, but you lose “random access” (you can’t just jump to page 57).

3. Seek pagination

   • Often considered the same as keyset, but sometimes distinguished when you seek on multiple columns or a compound index.

4. Hybrid approaches

   • Example: use offset for small page jumps (page 1–5), then switch to cursors when users are deep in the dataset.

5. Time-based pagination

   • For data that naturally flows by time (like logs, chat messages, feeds), you paginate using timestamps instead of arbitrary IDs.

6. Scrollable cursors (server-maintained state)

   • The server/database holds a cursor handle and the client fetches more by keeping that handle alive. Very common in DB drivers, but heavier on resources.

7. Relay-style connections (GraphQL)

   • Built on cursor-based, but adds “edges” and “connections” with before/after/first/last arguments for more flexibility.

So depending on how you slice it, you can say there are a handful of distinct strategies, but they’re all riffs on two main families: offset vs cursor/seek.

Would you like me to break down when you’d actually want to use each one, or are you just mapping the landscape right now?

Nice, rolling up sleeves time 🛠️. Since you asked for implementations, I’ll sketch example Node.js snippets for each style. I’ll assume we’re working with Express + a SQL database (PostgreSQL) via pg, but the patterns translate fine to ORMs like Sequelize/Prisma or even Mongo.

---

1. Offset-based pagination

js
// GET /users?page=2&limit=10
app.get("/users", async (req, res) => {
  const { page = 1, limit = 10 } = req.query;
  const offset = (page - 1) * limit;

  const { rows } = await db.query(
    "SELECT * FROM users ORDER BY id LIMIT $1 OFFSET $2",
    [limit, offset]
  );

  res.json(rows);
});

✅ Easy, but slow for huge offsets.

---

2. Keyset (cursor) pagination

js
// GET /users?limit=10&after=42
app.get("/users", async (req, res) => {
  const { limit = 10, after } = req.query;

  let query = "SELECT * FROM users";
  let params = [limit];

  if (after) {
    query += " WHERE id > $2";
    params.push(after);
  }

  query += " ORDER BY id ASC LIMIT $1";

  const { rows } = await db.query(query, params);
  res.json(rows);
});

✅ Very fast, consistent ordering.
🚫 Can’t jump to arbitrary pages.

---

3. Seek pagination (compound keys)

js
// GET /posts?limit=10&afterId=100&afterCreated=2023-01-01
app.get("/posts", async (req, res) => {
  const { limit = 10, afterId, afterCreated } = req.query;

  let query = `
    SELECT * FROM posts
    WHERE (created_at, id) > ($2, $3)
    ORDER BY created_at, id
    LIMIT $1
  `;

  const { rows } = await db.query(query, [limit, afterCreated, afterId]);
  res.json(rows);
});

✅ Handles ties in sorting by using multiple columns.

---

4. Hybrid approach

js
// Offset for shallow pages, cursor for deeper ones
app.get("/users", async (req, res) => {
  const { page = 1, limit = 10, after } = req.query;

  if (page <= 5) {
    const offset = (page - 1) * limit;
    const { rows } = await db.query(
      "SELECT * FROM users ORDER BY id LIMIT $1 OFFSET $2",
      [limit, offset]
    );
    return res.json(rows);
  }

  if (after) {
    const { rows } = await db.query(
      "SELECT * FROM users WHERE id > $2 ORDER BY id LIMIT $1",
      [limit, after]
    );
    return res.json(rows);
  }

  res.status(400).json({ error: "Need 'after' for deep pages" });
});

---

5. Time-based pagination

js
// GET /logs?limit=50&before=2023-09-01T00:00:00Z
app.get("/logs", async (req, res) => {
  const { limit = 50, before } = req.query;

  const { rows } = await db.query(
    "SELECT * FROM logs WHERE created_at < $2 ORDER BY created_at DESC LIMIT $1",
    [limit, before || new Date().toISOString()]
  );

  res.json(rows);
});

✅ Great for logs/feeds.

---

6. Scrollable cursors (server-maintained)

js
// Imagine a database cursor you keep open
app.get("/stream-users", async (req, res) => {
  const client = await db.connect();
  await client.query("BEGIN");

  // open cursor
  await client.query("DECLARE user_cursor CURSOR FOR SELECT * FROM users ORDER BY id");

  // fetch a batch
  const { rows } = await client.query("FETCH 20 FROM user_cursor");

  // cleanup: COMMIT to close cursor
  await client.query("COMMIT");
  client.release();

  res.json(rows);
});

✅ Efficient streaming.
🚫 State is server-bound, harder to scale.

---

7. Relay-style (GraphQL)

js
// GraphQL schema (simplified)
type UserConnection {
  edges: [UserEdge]
  pageInfo: PageInfo
}
type UserEdge {
  cursor: String
  node: User
}
type PageInfo {
  hasNextPage: Boolean
  endCursor: String
}

js
// Resolver
const users = async (_, { first, after }) => {
  let query = "SELECT * FROM users WHERE id > $2 ORDER BY id ASC LIMIT $1";
  const { rows } = await db.query(query, [first, after || 0]);

  return {
    edges: rows.map(u => ({ cursor: u.id.toString(), node: u })),
    pageInfo: {
      hasNextPage: rows.length === first,
      endCursor: rows.length ? rows[rows.length - 1].id.toString() : null,
    },
  };
};

---

That’s the full set 🎯. Each of these comes with trade-offs: offset = easy but slow, cursor = fast but strict order, scrollable = powerful but sticky, etc.

Do you want me to wrap these into a single Express server demo with routes for each style, or keep them as standalone snippets like above?