Keyset pagination continues after the last ordering tuple instead of skipping an increasing number of rows. It requires a deterministic ordering.
Before you start
You should understand tables, keys, joins and SELECT queries. Write down a tiny dataset before reasoning about a query. SQL examples use PostgreSQL-style syntax where relevant; planner behavior, locking and available features must be checked for the database and version used in an application.
The practical goal is to reason through this situation: Use created_at plus ID so identical timestamps still produce a unique cursor. Read the walkthrough first, then try the interview exercise before opening its answer. The important part is explaining the decision and its consequences, rather than remembering a definition alone.
Step-by-step walkthrough
Step 1: Define a total order
Combine timestamp and unique ID to distinguish ties.
Step 2: Carry the complete cursor
The next-page predicate must compare the same tuple used by ordering.
Step 3: Choose concurrent-insert behavior
Document what later pages include while the dataset changes.
Worked scenario
Use created_at plus ID so identical timestamps still produce a unique cursor.
SELECT * FROM posts
WHERE (created_at, id) < (:cursor_time, :cursor_id)
ORDER BY created_at DESC, id DESC LIMIT 24;The colon parameters are driver-dependent placeholders, not standalone PostgreSQL literals. Both descending columns match the less-than continuation direction. Null ordering needs additional policy if either field permits null.
Common mistake
Sorting only by a nonunique timestamp can skip or repeat rows.
Verify the behavior
Test identical timestamps, page boundaries and inserts between requests; check duplicates and omissions.
Interview exercise
Design a next-page cursor.
Answer and reasoning
Encode the complete order tuple and use matching comparison directions, with a defined policy for concurrent inserts.
Continue learning
Compare the scenario with the SQL and PostgreSQL interview questions and test your understanding with the SQL and PostgreSQL MCQs. For terminology and implementation details, consult the reference material.