The Desi Architect
Notes / Docker Sandboxes / Pagination at Scale: LIMIT/OFFSET vs Keyset Cursor
Docker Sandboxes · Tier 1: Building Blocks · Lab 16

Pagination at Scale: LIMIT/OFFSET vs Keyset Cursor

Discover why naive LIMIT/OFFSET pagination chokes your database as users scroll deeper into results, and see how keyset (cursor-based) pagination keeps query times at 0.1 ms while preventing duplicate rows during live writes.

Time35 minutes
StackPostgreSQL 16Ports 15431
MemoryAbout 80 MB
Before thisLab 04SQL Basics with PostgreSQL
Download lab (.zip) View steps
Verified runWindows, Mac, LinuxBash and PowerShell
  1. 1PrepareWhat you will learn
  2. 2PredictGuess before you run
  3. 3RunStep by step
  4. 4ObserveWhat you should see
  5. 5Break itChange one thing
  6. 6Clean upLeave nothing running
On this page
  1. 00 / What You Will Learn
  2. 01 / Why It Matters
  3. 02 / What We Are Building
  4. 03 / Words You Need
  5. 04 / Predict First
  6. 05 / Step by Step
  7. 06 / What You Should See
  8. 07 / Look Inside
  9. 08 / Break It, Fix It
  10. 09 / In Real Projects
  11. 10 / Interview Corner
  12. 11 / Clean Up

00 /What You Will Learn

By the end you will be able to
  • Read an EXPLAIN (ANALYZE, BUFFERS) plan for both OFFSET and keyset queries.
  • Explain why OFFSET 50000 LIMIT 20 scans 50,020 rows and throws away 50,000 of them.
  • Build a composite B-tree index on (created_at DESC, id DESC) for instant keyset seeks.
  • Demonstrate the duplicate item drift trap when concurrent inserts happen between pages.
  • Implement bidirectional keyset traversal (Next and Previous page) without session state.

Before you start

Finish Lab 04 (SQL Basics with PostgreSQL) and Lab 15 (Database Indexing) first. You should know what an Index Scan and B-tree are. Docker Desktop must be running.