Detailed explanation
Efficient SQL pagination means returning a small, predictable batch of rows without repeatedly processing everything before that batch. LIMIT/OFFSET is convenient: SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000 returns 20 rows after skipping the first 10,000 in the ordered result. It supports numbered pages directly, but a large offset usually forces the database to traverse and discard many entries. Even with an index supporting ORDER BY, this work generally grows with the offset; without one, sorting can add substantial cost.
Keyset, or seek, pagination avoids expressing the position as a row count. Instead, the client sends the last ordering key from the previous page: SELECT * FROM orders WHERE id > :last_id ORDER BY id LIMIT 20. With a suitable index on id, the database can seek past that key and scan the next matching entries rather than walk through every earlier page. The cursor must contain the actual last returned ID, not an ID calculated from the page number, because IDs can have gaps. Select only required columns to reduce data transfer and potentially enable covering-index access.
The ordering must be deterministic and unique. If orders are sorted by created_at, add id as a tie-breaker: ORDER BY created_at, id. The next-page condition is created_at > :last_time OR (created_at = :last_time AND id > :last_id). Some databases also support the equivalent tuple comparison (created_at, id) > (:last_time, :last_id). A composite index matching the filters and ordering is important; for example, an equality filter on customer_id may suit an index on (customer_id, created_at, id). For descending order, use the corresponding less-than comparisons.
The trade-off is navigation: keyset pagination naturally supports next-page browsing but cannot directly jump to arbitrary page numbers. It also avoids offset shifts caused by earlier inserts or deletes, although updates to ordering keys can still cause missed or repeated rows. A consistent snapshot may be needed for strict consistency. In interviews, explain these trade-offs and verify performance with execution plans rather than claiming keyset pagination is always constant-time.
Key points to remember
- Use LIMIT/OFFSET for shallow results or interfaces requiring direct page-number navigation.
- Use keyset pagination for deep sequential browsing, backed by a suitable index.
- Always specify a deterministic ORDER BY with a unique tie-breaker.
- Build the cursor from the last returned ordering values and match comparisons to sort direction.
- Check execution plans and actual rows processed; filters and index design affect performance.
Common mistakes candidates make
- Assuming LIMIT makes a large OFFSET cheap because only a few rows are returned.
- Paginating on a non-unique timestamp without including a unique tie-breaker.
- Calculating cursor IDs from page numbers or assuming keyset pagination guarantees snapshot consistency.
Likely follow-up questions
How do you implement previous-page navigation with keyset pagination?
For ascending ID order, use the current page's first ID: WHERE id < :first_id ORDER BY id DESC LIMIT 20. Reverse the returned rows before displaying them in ascending order.
What should a pagination cursor contain?
Include every value needed to resume the ordering, such as created_at and id. Preserve their precision and ensure the cursor is used with the same filters and sort order; APIs can encode and sign it to detect tampering.
How do you indicate that another page exists without counting all rows?
Request page_size + 1 rows using the same filters and ordering. If the extra row exists, return only page_size rows, set has_next to true, and build the next cursor from the last row actually returned.
Interview tip: Start with the offset-versus-seek trade-off, show the indexed keyset query, and then mention tie-breakers and navigation limitations.