HTML MASTER CLASS /// LEARN TAGS /// BUILD STRUCTURE /// SEMANTIC WEB /// HTML MASTER CLASS /// LEARN TAGS ///

Cursor Pagination in SQL & Databases

Learn about Cursor Pagination in this comprehensive SQL & Databases development tutorial. The Infinite Scroll.

⚡ Total XP: 0|💻 sql XP: 0

Skill Matrix

UNLOCK NODES BY LEARNING NEW TAGS.

Select an unlocked node to view details root

🚀 LEVEL UP TO SENIOR:Unlock 500+ Advanced Practical Challenges & Exercises.

🎓 COURSERA PARTNER:Earn professional Google, Meta, and IBM certificates to supercharge your resume.

1The Real World

When you scroll through Instagram or Twitter, they are not using 'OFFSET 5000'. They use Cursor Pagination. They send the ID of the last tweet on your screen back to the server, and the server queries WHERE tweet_id < :cursor ORDER BY tweet_id DESC LIMIT 20. This is the secret to infinite scrolling performance.

2Step-by-Step Breakdown

The Problem. If Amazon ran 'SELECT * FROM products', it would return 500 million rows, crashing the server and the user's browser. You must limit the payload.

LIMIT. The LIMIT clause goes at the very end of the query. 'SELECT * FROM users LIMIT 10;'. This tells the database to stop searching the moment it finds 10 rows.

Top N Queries. To get the 'Top 5 Highest Paid Employees', you combine ORDER BY and LIMIT. 'SELECT * FROM employees ORDER BY salary DESC LIMIT 5;'.

OFFSET. LIMIT gives you the first 10 rows. How do you get the NEXT 10 rows? You use OFFSET. 'LIMIT 10 OFFSET 10;'. This skips the first 10 rows.

Knowledge Check. If you want to display 'Page 3' of a user list, and each page shows 20 users, what should your LIMIT and OFFSET values be?

→LIMIT 20 OFFSET 20
→LIMIT 20 OFFSET 40

Pagination Formula. In your Node.js backend, you calculate the OFFSET dynamically. 'const offset = (pageNumber - 1) * limit;'.

The OFFSET Performance Trap. If a user clicks 'Page 10,000', your query says 'OFFSET 100000'. The database must still fetch and count 100,000 rows internally, discard them, and then return the 10 you want. It is very slow.

Keyset Pagination. To fix the OFFSET trap, massive sites use 'Keyset Pagination' (Cursor pagination). Instead of OFFSET, you use the WHERE clause: 'WHERE id > last_seen_id LIMIT 10;'. This uses indexes and is instant.

Standard Variations. LIMIT is standard in Postgres and MySQL. SQL Server traditionally used 'TOP 10' at the start of the query. Modern SQL standards use 'FETCH FIRST 10 ROWS ONLY'.

Summary. Always use LIMIT on large tables, and be cautious of high OFFSETs.

Level Up 🚀

Advanced cheat sheets, SEO tricks, and interview prep for this topic.

Browser Support

ChromeSupported

Fully supported.

FirefoxSupported

Fully supported.

SafariSupported

Fully supported.

EdgeSupported

Fully supported.

Accessibility (A11y)

1Announce Page Changes to Screen Readers in Paginated Tables

When a LIMIT/OFFSET-driven pagination control loads a new page of results, use an ARIA live region (aria-live="polite") to announce something like 'Showing results 21 to 30' so screen reader users know the table content changed without re-scanning it.

SEO Implications

1
Paginated Content Needs Proper Signals to Avoid Duplicate Content
Pages built from LIMIT/OFFSET queries (e.g. /products?page=2) should use unique, crawlable URLs per page with clear pagination signals, or consolidate to a single canonical view, to avoid search engines treating each offset as thin, near-duplicate content.

Best Practices

Always Pair LIMIT with ORDER BY for Deterministic Pagination

Without an ORDER BY, a table has no guaranteed row order, so LIMIT/OFFSET can return inconsistent or duplicate rows across page loads as data changes. Always sort by a stable column, ideally a unique one like id, before paginating.

Switch to Keyset (Cursor) Pagination for Large, Deep-Scrolling Datasets

High OFFSET values force the database to scan and discard huge numbers of rows before returning results. For infinite scroll or very large tables, filter with WHERE id > last_seen_id LIMIT n instead, which uses an index and stays fast regardless of how deep the user scrolls.

Frequent Bugs

THE BUG

A paginated listing occasionally shows the same row twice across two different pages, or skips a row entirely.

THE FIX

This happens when LIMIT/OFFSET is used without a stable ORDER BY, or the underlying data changes between page loads. Always order by a unique, stable column, and consider keyset pagination if rows are added or removed frequently.

THE BUG

A 'load more' feature becomes noticeably slower the further a user scrolls (page 500 takes seconds to load).

THE FIX

High OFFSET values require the database to count through and discard all preceding rows before it can return the requested page. Replace OFFSET-based pagination with keyset pagination using WHERE id > :last_id LIMIT :page_size, which lets the index jump straight to the right spot.

Real-World Examples

Implementing Cursor-Based Infinite Scroll for a Feed

A social feed needed to support fast infinite scrolling through millions of posts without the performance cliff that comes from ever-increasing OFFSET values.

-- Client sends the id of the last post it saw
SELECT * FROM posts
WHERE id < $lastSeenId
ORDER BY id DESC
LIMIT 20;

Interview Prep

Pascual Vila

Frontend Instructor // Code Syllabus

Common Pitfalls & Errors

The Error //

Using LIMIT/OFFSET without a stable ORDER BY, causing duplicate or skipped rows across pages

-- Wrong: no guaranteed order between pages
SELECT * FROM products LIMIT 10 OFFSET 10;

-- Correct: stable, unique sort key
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;

The Solution //

Without an explicit ORDER BY on a unique column, tables have no guaranteed row order, so paginating with LIMIT/OFFSET alone can return the same row twice or skip one entirely as internal ordering shifts. Always sort by a stable, ideally unique, column before paginating.

The Error //

Deep OFFSET pagination causing severe slowdowns at scale

-- Slow at scale: must scan and discard 100,000 rows first
SELECT * FROM posts ORDER BY id DESC LIMIT 10 OFFSET 100000;

-- Fast: keyset pagination uses the index directly
SELECT * FROM posts WHERE id < $lastSeenId ORDER BY id DESC LIMIT 10;

The Solution //

A high OFFSET forces the database to fetch and discard every preceding row before returning the requested page, which gets progressively slower the deeper a user paginates. Replace OFFSET with keyset (cursor) pagination on an indexed column for large or infinite-scroll datasets.