Pagination patterns (cursor, offset) in HLD - Scalability & System Analysis

Scalability Analysis - Pagination patterns (cursor, offset)

Growth Table: Pagination Patterns (Cursor vs Offset)

Users / Requests	Offset Pagination	Cursor Pagination
100 users	Simple queries, small data scans, fast response	Simple cursor tokens, fast and efficient queries
10,000 users	Offset queries slow down due to large OFFSET scans	Cursor queries remain efficient, use indexed columns
1,000,000 users	Offset queries cause high DB CPU and IO, slow response	Cursor queries scale well, minimal DB load, stable latency
100,000,000 users	Offset pagination becomes impractical, DB overload	Cursor pagination requires careful token management, still scalable

First Bottleneck

The database query performance is the first bottleneck. Offset pagination requires scanning and skipping many rows as OFFSET grows, causing high CPU and IO load. This slows down response times and increases resource use. Cursor pagination avoids large skips by using indexed positions, reducing DB load and improving latency.

Scaling Solutions

• Use Cursor Pagination: Replace offset with cursor to avoid large skips and improve query efficiency.
• Indexing: Ensure columns used for cursor are indexed for fast lookups.
• Cache Results: Cache popular pages or query results to reduce DB hits.
• Read Replicas: Use DB replicas to distribute read load.
• Limit Page Size: Keep page sizes small to reduce data transfer and processing.
• Token Management: For cursor, manage tokens securely and efficiently to avoid state issues.

Back-of-Envelope Cost Analysis

Assuming 10,000 requests per second (RPS):

• Offset pagination at high OFFSET causes DB CPU spikes, increasing server costs.
• Cursor pagination keeps DB CPU stable, reducing need for extra servers.
• Storage impact is minimal for pagination itself but indexing adds overhead (~10-20% storage).
• Bandwidth depends on page size; smaller pages reduce network load.

Interview Tip

Start by explaining the difference between offset and cursor pagination. Discuss how offset causes performance issues at scale due to large skips. Then explain how cursor pagination uses indexed positions to improve efficiency. Finally, mention practical scaling techniques like indexing, caching, and read replicas. Use real numbers to show understanding.

Self Check

Your database handles 1000 QPS with offset pagination. Traffic grows 10x. What do you do first?

Answer: Switch to cursor pagination to reduce DB load caused by large OFFSET scans. This improves query efficiency and supports higher traffic without immediate hardware upgrades.

Key Result

Offset pagination works well at small scale but causes database performance issues as data grows. Cursor pagination scales better by using indexed positions, reducing DB load and improving response times.