Why does offset pagination slow down on deep pages?

Offset pagination requires the database to scan and skip O(N*page_size) rows, causing linear degradation.

What is the main advantage of cursor-based pagination?

Cursor pagination uses a WHERE clause on an indexed column, achieving O(1) per page regardless of depth.

Keyset (seek) is ideal for large datasets with sequential access or real-time feeds where offset becomes expensive.

Interactive deep-dive: Offset vs Cursor vs Keyset vs Seek

📦 Total records 100K

100,000

📄 Page size 50

⏱ API response time (ms) 30

30 ms

🧠 Client processing per page (ms) 10

10 ms

🎯 Target page N (deep pagination) 50

⚙️ DB read cost: $0.10 / million reads

🔁 Offset total time

all pages

⏩ Cursor total time

all pages

⏩ Keyset total time

all pages

⏩ Seek total time

all pages

✅ Offset is fine. Simplicity wins. Cursor adds complexity without benefit.

✅ Cursor / Keyset. Avoid offset deep pagination. Use indexed cursor.

✅ Seek / Keyset optimal. Constant time per page, ideal for infinite scroll.

-- Cursor: WHERE id > ? ORDER BY id LIMIT ?
SELECT * FROM items 
WHERE id > :last_seen_id 
ORDER BY id ASC 
LIMIT :page_size;
        

{
  "data": [ ... ],
  "pagination": {
    "cursor": "eyJpZCI6IDF9",
    "has_more": true,
    "next_cursor": "eyJpZCI6IDUwfQ=="
  }
}
        

💡 Keyset/Seek use same principle: WHERE indexed_col > value. Offset degrades because of OFFSET + LIMIT scanning.

Recommended by our team

The #1 AI writing tool for freelancers — perfect grammar in any language, instantly.