Keyset Pagination

Hook: Interviewers love this one because it turns a slow 'skip N rows' problem into a fast 'start from here' problem.

Question: What is keyset pagination, and why is it faster than OFFSET/LIMIT for large tables?

Answer: Keyset pagination means you page by the last row you already saw, not by counting from the beginning. Instead of saying 'give me page 200', you say 'give me the next rows after this key'. That lets the database jump into an ordered index near the right spot, which is much faster on big tables. The most important rule is to sort by a stable order, usually a timestamp plus a unique id.

Interview-Ready Answer: I use keyset pagination when I want fast, stable paging on large result sets. I sort by a deterministic key, like created_at and then a unique id, and for the next page I filter on the last seen key instead of using OFFSET. That avoids scanning and discarding thousands of rows, so the query stays fast even deep into the list. The big gotcha is that the order must be unique and stable, or you can get duplicates or skipped rows.

What it is

Keyset pagination is also called seek pagination. A seek means the database uses an ordered index to jump to a position, like opening a book at a bookmarked page, instead of flipping through every page from the front. The client sends back the last seen sort key values, often called a cursor, but this is not the same thing as a SQL cursor that holds server state.

How it works under the hood

1. You choose a deterministic ORDER BY, such as ORDER BY created_at, id. The extra unique column is a tie-breaker, which means it breaks ties when two rows share the same timestamp.
2. The first request uses only LIMIT and the sort order. Example idea: 'give me the first 20 rows ordered by created_at, id'.
3. The response includes the last row's key values, for example 2024-01-01 10:02:00 and 104.
4. The next request adds a predicate such as WHERE (created_at, id) > (...) for ascending order. That tells the database to start after the last row it already returned.
5. If there is a matching B-tree index, which is the common ordered index structure in SQL databases, the engine can jump near that key and read only the next page of rows. It does not need to count or discard all earlier rows.
6. The database stops after it finds enough rows for the page size, such as 20, 50, or 100 rows.

Why it is faster than OFFSET

With OFFSET 100000 LIMIT 50, the database may still have to walk past about 100,000 rows before it can return the 50 you asked for. With keyset pagination, it seeks to the last key and reads the next 50 rows. In practice, that can be the difference between a query that finishes in milliseconds and one that takes seconds on a large table.

When and why to use it

• Use it for feeds, activity logs, inboxes, search results, and admin lists that the user scrolls forward through.
• Use it when the table is large or growing fast.
• Use it when you care about consistency as rows are inserted or deleted.
• Do not use it when the user must jump directly to an arbitrary page number, because keyset is about navigation, not random access.

Important edge cases

• Non-unique sort keys: If you sort only by created_at, rows with the same timestamp can be skipped or repeated. Always add a unique tie-breaker like id.
• Nullable columns: Comparisons with NULL are tricky because NULL does not compare like a normal value. Avoid nullable sort columns or define a clear NULLS FIRST/NULLS LAST rule.
• Updates to sort columns: If a row changes its sort key between requests, it can move to a different page. This is one reason feeds often sort by creation time, which usually does not change.
• Backward paging: To go to the previous page, you typically reverse the comparison and reverse the final sort in the application.

Memory rule: keyset pagination is 'bookmark, not count'. The bookmark is the last key you saw; the database continues from there.

Comparison with OFFSET

OFFSET is simple and fine for tiny tables, but it gets more expensive as the offset grows. Keyset is the better default for performance-sensitive lists because the database can use the index order directly. The trade-off is that keyset needs a stable sort key and cannot easily jump to page 37 without walking there page by page.

Real-world story

Imagine an e-commerce admin dashboard that lists recent orders. On quiet days, the team used OFFSET/LIMIT to show page 1, page 2, and so on. During a flash sale, thousands of new orders arrived every minute, and page 3 started showing duplicate orders while some orders seemed to vanish between refreshes. The slow query log also lit up because a request like OFFSET 50000 forced the database to scan far too many rows.

The fix was keyset pagination on (created_at, id). The dashboard now asks for 'orders after the last order I saw', so the next page is stable even while new rows are inserted at the top. The incident symptoms were very concrete: users complained that the same order appeared twice, support agents missed a few recent orders, and PostgreSQL showed rising latency on the listing endpoint. After the change, the page response time dropped and the list became predictable again.

What goes wrong when you misunderstand it: if you page only by created_at, two orders with the same timestamp can cause one to be skipped. In a real outage, that looks like 'I cannot find the order anymore' even though the row is still in the database.

Follow-up & Tricky Questions:

• How do you paginate backwards? Reverse the comparison and the sort direction. For example, if forward uses > with ascending order, backward uses < and then you reverse the returned rows in the application.
• What index do you need? An index that matches the leading sort columns, usually (created_at, id) or the exact composite order you query with. Without it, the database may still scan a lot of rows and lose the speed benefit.
• How do you return the next cursor? Send back the last row's sort key values, often encoded into a token. The client does not need to understand the SQL; it just echoes the token into the next request.
• What happens if rows are inserted while paging? Keyset pagination is much more stable than OFFSET because new rows usually appear before or after the cursor instead of shifting every later page. You can still see new rows on the first page, but you usually will not get duplicates from simple forward paging.
• Can you use it with filters? Yes, but the filter should be part of the same query shape every time. If you change the filter, you need a new cursor because the old one no longer describes the same ordered set.
• Does keyset guarantee a perfect snapshot? No. It improves paging stability, but true snapshot consistency depends on transaction isolation and how long the transaction stays open.
• Is OFFSET always wrong? No. For tiny tables or occasional admin screens, OFFSET can be simpler and perfectly fine. The problem is deep paging on large, changing data.
• Can you keyset paginate without a unique key? Not safely. You need a deterministic tie-breaker, otherwise the database cannot know where one page ends and the next begins.

Tricky gotcha 1: Is a SQL cursor the same as keyset pagination? No. A SQL cursor is a server-side object with state; keyset pagination is usually stateless and stores the last key in the client request.

Tricky gotcha 2: Can I jump to page 500 instantly? Not with pure keyset. Keyset is great for moving forward or backward from a known row, but not for arbitrary page numbers.

Tricky gotcha 3: Why not sort by name? You can, but names may not be unique and can change. A good pagination key should be stable, ordered, and usually unique when combined with a tie-breaker.

Common Mistakes:

• Using only one non-unique column: This can skip or repeat rows. Fix: add a unique tie-breaker such as id.
• Forgetting the matching index: The query can still be slow if the database cannot seek efficiently. Fix: index the same columns you sort by.
• Assuming page numbers are free: Keyset is about continuation, not random access. Fix: expose next/previous cursors instead of numeric pages for large lists.
• Ignoring NULL and updates: NULL comparisons and changing sort keys can make paging inconsistent. Fix: choose stable, non-null ordering columns whenever possible.

Memory Hook: Bookmark, don’t count. OFFSET counts from the start; keyset uses the last row as a bookmark and resumes there.

Cheat Sheet:

• Keyset pagination = page by last seen key.
• Use a stable ORDER BY and a unique tie-breaker.
• Query shape: WHERE key > last_key ORDER BY key LIMIT n.
• Fast on large tables because the index can seek.
• Better than OFFSET for deep pages and fast-changing data.
• Not ideal for jumping to arbitrary page numbers.

Practice Tasks:

• Rewrite an OFFSET/LIMIT query from your app into keyset form.
• Add a composite index that matches the query's ORDER BY and test the plan.
• Design a backward-page query for the same table and explain how the cursor changes.