Pagination Done Right: Cursor vs Offset and Why It Matters at Scale

Pagination Done Right: Cursor vs Offset and Why It Matters at Scale Your API uses OFFSET for pagination. At page 10000, queries take 30 seconds. Here is why, and the fix. The Problem with OFFSET -- Page 1: fast (skip 0, read 20) SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 0 ; -- Page 500: slow (database reads and discards 10000 rows) SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 10000 ; OFFSET scans and discards rows. The deeper you paginate, the slower it gets. Cursor-Based Pagination app . get ( " /api/posts " , async ( req , res ) => { const limit = Math . min ( parseInt ( req . query . limit ) || 20 , 100 ); const cursor = req . query . cursor ; // Last item ID from previous page let query = db ( " posts " ). orderBy ( " created_at " , " desc " ). limit ( limit + 1 ); if ( cursor ) { const cursorPost = await db ( " posts " ). where ({ id : cursor }). first (); query = query . where ( " created_at " , " < " , cursorPost . created_at ); } const posts =