Pagination is critical for every web application displaying lists. Stack Overflow paginates 50M+ questions using OFFSET/FETCH. Twitter uses keyset pagination for infinite scroll with 500M+ tweets. LinkedIn ranks feed items using window functions. Amazon calculates product rankings per category with PARTITION BY. Financial systems use window frames for running totals and moving averages. Reddit optimizes pagination for 1B+ posts with sub-50ms response times.

Stack Overflow Question Pagination

**Company**: Stack Overflow

**Challenge**: Stack Overflow has 50M+ questions. Users browse questions with page numbers (go to page 50), sort by newest/votes/activity. The system needs fast pagination with consistent ordering. OFFSET/FETCH provides standard SQL pagination but performance degrades for deep pages.

**Solution**: Stack Overflow uses OFFSET/FETCH for page numbers with covering indexes. For pages 1-100 (95% of traffic), OFFSET/FETCH is fast (50-200ms). For deep pages (page 1000+), they use hybrid approach: cache page boundaries and use keyset pagination.

**Results**: 95% of page requests are pages 1-100 (50-200ms response time). 4% are pages 100-1000 (200-1000ms). 1% are deep pages (1000+) using keyset (50ms). Covering indexes eliminate key lookups. Tie-breaker (question_id) ensures consistent ordering. Total count cached for 5 minutes to avoid scanning 50M rows per request.

-- Stack Overflow pagination implementation

-- Covering index for fast pagination

CREATE INDEX IX_Questions_Pagination

ON questions (created_at DESC, question_id DESC)

INCLUDE (title, view_count, answer_count, score, user_id)

WHERE deleted_at IS NULL;

-- Page request (95% of traffic: pages 1-100)

DECLARE @PageNumber INT = 50;

DECLARE @PageSize INT = 15;

DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;

SELECT

question_id,

title,

view_count,

answer_count,

score,

created_at

FROM questions

WHERE deleted_at IS NULL

ORDER BY created_at DESC, question_id DESC

OFFSET @Offset ROWS

FETCH NEXT @PageSize ROWS ONLY;

-- Execution time: 50-200ms for pages 1-100

-- Total count (cached for 5 minutes)

SELECT COUNT(*) AS total_questions

FROM questions

WHERE deleted_at IS NULL;

-- Result: 50,000,000 (cached)

-- Performance metrics:

-- Page 1: 50ms

-- Page 50: 150ms

-- Page 100: 200ms

-- Page 1000: 1000ms (rare, 1% of traffic)

All