Stop Using OFFSET in PostgreSQL. Here's the Alternative That's 100x Faster.

OFFSET 50000 on a 1M row table takes 30 seconds. The same query with keyset pagination takes 5 milliseconds. Here’s why, and how to switch.

Why OFFSET Is Slow

-- Page 1: 5ms
SELECT * FROM jobs ORDER BY id LIMIT 50 OFFSET 0;

-- Page 100: 800ms
SELECT * FROM jobs ORDER BY id LIMIT 50 OFFSET 5000;

-- Page 1000: 30 seconds
SELECT * FROM jobs ORDER BY id LIMIT 50 OFFSET 50000;

OFFSET N tells PostgreSQL: “Fetch N + LIMIT rows, then throw away the first N.” At offset 50,000, it’s scanning 50,050 rows to return 50. It gets linearly slower as you paginate deeper.

Keyset Pagination: O(1) for Every Page

-- Page 1: 5ms
SELECT * FROM jobs WHERE id > 0 ORDER BY id LIMIT 50;

-- Page 1000: 5ms (same speed!)
SELECT * FROM jobs WHERE id > 49999 ORDER BY id LIMIT 50;

Instead of “skip N rows,” you say “give me rows after this ID.” PostgreSQL uses the index to jump directly to the right position. Page 1 and page 1,000 are the same speed.

Real-World Example

We had a recalculation job that iterated over millions of rows in our job_matches table:

// Before: 83 seconds per page (OFFSET)
const result = await pool.query(`
  SELECT * FROM job_matches
  WHERE match_percentage >= 50
  ORDER BY user_id, job_id
  LIMIT 1000 OFFSET $1
`, [page * 1000]);

// After: < 5 seconds per page (keyset)
const result = await pool.query(`
  SELECT * FROM job_matches
  WHERE match_percentage >= 50
    AND (user_id, job_id) > ($1, $2)
  ORDER BY user_id, job_id
  LIMIT 1000
`, [lastUserId, lastJobId]);

Combined with a partial index:

CREATE INDEX idx_job_matches_stale_recalc
ON job_matches (user_id, job_id)
WHERE match_percentage >= 50;

From 83 seconds to under 5 seconds per batch. The total recalculation time went from hours to minutes.

The Trade-Off

Keyset pagination doesn’t support “jump to page 47.” You can only go forward/backward. For most APIs and background jobs, that’s fine. For UIs that need random page access, you’ll need a hybrid approach.

This powers the job matching engine at MisuJob, which processes 1M+ active listings.

What’s your favorite PostgreSQL optimization? Drop it below.