A feed pagination endpoint using `OFFSET`. Works fine for page 1. By page 200, Postgres is scanning 10,000 rows to return 50.

The vulnerable diff

tsapi/feed.ts

// api/feed.ts
feedRouter.get("/feed", async (req, res) => {
  const page = parseInt(req.query.page as string) || 1;
  const offset = (page - 1) * 50;
  // BUG: OFFSET requires scanning offset+limit rows
  const rows = await db.query(
    "SELECT * FROM feed ORDER BY created_at DESC OFFSET $1 LIMIT 50",
    [offset]
  );
  res.json(rows);
});

What is wrong

OFFSET works by scanning all rows up to the offset and discarding them. On page 1 (OFFSET 0), the database returns 50 rows immediately. On page 200 (OFFSET 9,950), Postgres scans 10,000 rows in sorted order, discards the first 9,950, returns the last 50. Time-to-first-byte scales with page number. Keyset pagination uses the sort column itself as the cursor — `WHERE created_at < $cursor` jumps directly to the boundary using the index, scans only 50 rows regardless of page depth.

The attack

EXPLAIN ANALYZE on page 200:

textexploit

-- OFFSET 9950 LIMIT 50
EXPLAIN ANALYZE SELECT * FROM feed ORDER BY created_at DESC OFFSET 9950 LIMIT 50;
-- Limit  (rows=50, total time: 312ms)
--   ->  Index Scan Backward on feed_created_at_idx  (rows=10000)
-- Reads 10,000 rows to return 50.

-- Keyset with WHERE
EXPLAIN ANALYZE SELECT * FROM feed WHERE created_at < '2026-09-30' ORDER BY created_at DESC LIMIT 50;
-- Limit  (rows=50, total time: 1.4ms)
--   ->  Index Scan Backward on feed_created_at_idx  (rows=50)
-- Reads 50 rows.

Keyset is ~200× faster on deep pages and the gap widens as pages grow.

Mesrai's review comment

textreview-comment.txt

mesraipilot · Bot · reviewed 2 min ago

[mesrai] [code-review] [Performance] [Pagination] [medium]

OFFSET pagination scales linearly with page number. Page 200 means
scanning ~10K rows to return 50.

Switch to keyset (cursor) pagination — use the sort column as a
cursor:

  const cursor = req.query.cursor as string | undefined;
  const rows = await db.query(
    `SELECT * FROM feed
        WHERE ($1::timestamptz IS NULL OR created_at < $1)
        ORDER BY created_at DESC LIMIT 51`,
    [cursor ?? null]
  );
  const hasMore = rows.length > 50;
  const nextCursor = hasMore ? rows[49].created_at : null;
  res.json({ rows: rows.slice(0, 50), nextCursor });

Trade-offs:
  - You lose random-access pagination (no "jump to page 200").
  - You gain constant-time per page regardless of depth.
  - Most product surfaces (feeds, lists, timelines) don't need
    random access — keyset is the right tool.

The fix

tsapi/feed.ts (fixed)

// api/feed.ts — fixed
feedRouter.get("/feed", async (req, res) => {
  const cursor = req.query.cursor as string | undefined;
  const rows = await db.query(
    `SELECT id, title, created_at FROM feed
      WHERE ($1::timestamptz IS NULL OR created_at < $1)
      ORDER BY created_at DESC LIMIT 51`,
    [cursor ?? null]
  );
  const hasMore = rows.length > 50;
  res.json({
    rows: rows.slice(0, 50),
    nextCursor: hasMore ? rows[49].created_at : null,
  });
});

Keyset uses the sort key as a cursor. The `WHERE created_at < $cursor` lets the index jump directly to the right starting row. Latency is constant per page regardless of depth. Trade: no jump-to-page-N, but most product surfaces never use that.

Why human review missed it

OFFSET pagination is the default in tutorials and ORMs. The cost is invisible until users actually paginate deep. By the time the bug shows up in metrics, the API is locked in. Mesrai catches OFFSET pagination at PR time by flagging any query with `OFFSET $offset` where offset is unbounded.

Related rules + further reading

Mesrai rule pack: performance/no-deep-offset — flags OFFSET pagination on potentially-deep result sets.

Markus Winand: 'Pagination Done the PostgreSQL Way'.

use-the-index-luke.com — keyset pagination chapter.

Takeaway

Keyset pagination scales. OFFSET does not. Use the sort key as a cursor. Same UX, constant-time per page.

OFFSET-Based Pagination Past 10K Rows: The Cursor-Pagination Fix