How would you implement efficient pagination on a table with 100 million rows?
sql-sys-002
Your answer
Answer as you would in a real interview — explain your thinking, not just the conclusion.
Model answer
OFFSET pagination becomes increasingly expensive because the database must scan and discard all preceding rows. At page 10,000 of a 100-million-row table, OFFSET 1000000 means the database retrieves one million rows and throws them away. Keyset pagination (also called cursor pagination) avoids this entirely: you remember the last value seen on the previous page and filter rows that come after it. This requires a stable sort key — usually the primary key or a created_at with a tie-breaker — and an appropriate index on that key. The limitations are that you cannot jump to arbitrary page numbers and the sort order must remain consistent. For read replicas or APIs I always prefer keyset; for admin screens where users expect 'go to page 50' I accept the OFFSET cost or precompute page boundaries. For very large exports I recommend cursor-based streaming using server-side cursors rather than any page-based approach.
Code example
-- OFFSET pagination (avoid for deep pages on large tables)
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 200000; -- slow: scans 200020 rows
-- Keyset / cursor pagination (fast regardless of depth)
-- First page
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Next page: pass last_created_at and last_id from previous result
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ('2025-05-01T12:00:00Z', 98765) -- last row values
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Index to support both sorts
CREATE INDEX idx_posts_created_id ON posts(created_at DESC, id DESC);Follow-up
How would you handle the case where rows are deleted between page requests with keyset pagination? What does a 'stable cursor' mean in this context?