Keyset pagination for performance in Rest API - Step-by-Step Execution

Concept Flow - Keyset pagination for performance

Client requests first page

↓

Server returns first N items + last item's key

↓

Client stores last item's key

↓

Client requests next page with last key

↓

Server returns next N items where key > last key

↓

Repeat until no more items

↓

End

The client requests pages using the last item's key from the previous page to fetch the next set efficiently.

Execution Sample

Rest API

GET /items?limit=3
Response: items with keys 1,2,3 + last_key=3

GET /items?limit=3&after=3
Response: items with keys 4,5,6 + last_key=6

Shows how client requests pages with limit and after key to get next items efficiently.

Execution Table

Step	Request URL	Server Query Condition	Items Returned (keys)	Last Key Sent	Client Action
1	/items?limit=3	No condition (start from beginning)	[1, 2, 3]	3	Store last_key=3
2	/items?limit=3&after=3	key > 3	[4, 5, 6]	6	Store last_key=6
3	/items?limit=3&after=6	key > 6	[7, 8]	8	Store last_key=8
4	/items?limit=3&after=8	key > 8	[]	-	No more items, stop

💡 No more items after key 8, server returns empty list, pagination ends.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
last_key	null	3	6	8	8
items_returned	[]	[1,2,3]	[4,5,6]	[7,8]	[]

Key Moments - 3 Insights

Why does the client send the 'after' key in the request?

The 'after' key tells the server where to start the next page, so it only returns items with keys greater than that, avoiding duplicates and improving performance (see execution_table steps 2 and 3).

What happens if the server returns fewer items than the limit?

It means there are no more items to fetch, so pagination ends (see execution_table step 3 and 4 where fewer or no items are returned).

Why is keyset pagination faster than offset pagination?

Because the server uses the last key to jump directly to the next items without scanning or skipping rows, making queries faster especially on large datasets.

Visual Quiz - 3 Questions

Look at the execution_table, what is the last_key stored by the client after step 2?

A 6

B 3

C 8

D null

💡 Hint

Check the 'Last Key Sent' column in execution_table row for step 2.

At which step does the server return no items, indicating the end of pagination?

A Step 2

B Step 4

C Step 3

D Step 1

💡 Hint

Look for the row where 'Items Returned' is empty in the execution_table.

If the client did not send the 'after' key in step 3, what would happen?

A Server returns items with keys > 8

B Server returns no items

C Server returns items starting from key 1 again

D Server returns an error

💡 Hint

Refer to execution_table step 1 where no 'after' key means start from beginning.

Concept Snapshot

Keyset pagination uses the last item's key to fetch the next page.
Client sends 'after' key to server.
Server returns items with keys greater than 'after'.
Faster than offset pagination for large data.
Stops when server returns fewer items than limit.

Full Transcript

Keyset pagination helps fetch data pages efficiently by using the last item's key from the previous page. The client first requests a page with a limit. The server returns items and the last key. The client stores this key and sends it in the next request as 'after'. The server then returns items with keys greater than this 'after' key. This repeats until no more items are left. This method avoids scanning or skipping rows, making it faster than offset pagination, especially for large datasets.

Practice

1. What is the main advantage of using keyset pagination over traditional offset-based pagination in REST APIs?

easy

A. It requires less server memory by caching all pages in advance.

B. It allows jumping to any page number directly without sequential access.

C. It improves performance by avoiding slow offset queries on large datasets.

D. It automatically sorts data in descending order without extra code.

Solution

1. Step 1: Understand offset-based pagination issues

   Offset pagination uses a page number and offset, which becomes slow on large datasets because the database must skip many rows.

2. Step 2: Recognize keyset pagination benefits

   Keyset pagination uses a fixed key (like an ID) to fetch the next set of rows, avoiding the costly skip operation and improving performance.

3. Final Answer:

   It improves performance by avoiding slow offset queries on large datasets. -> Option C

4. Quick Check:

   Keyset pagination = better performance [OK]

Hint: Keyset uses keys, not offsets, for faster queries [OK]

Common Mistakes:

• Thinking keyset allows random page jumps
• Assuming keyset caches all data
• Believing keyset changes sort order automatically

2. Which of the following is the correct SQL snippet to implement keyset pagination for a table users ordered by id?

easy

A. SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

B. SELECT * FROM users WHERE id > 20 ORDER BY id LIMIT 10;

C. SELECT * FROM users WHERE id = 20 ORDER BY id LIMIT 10;

D. SELECT * FROM users ORDER BY id DESC LIMIT 10 OFFSET 20;

Solution

1. Step 1: Identify keyset pagination syntax

   Keyset pagination uses a WHERE clause with a key (like id > last_seen_id) and a LIMIT to fetch the next page.

2. Step 2: Analyze each option

   SELECT * FROM users WHERE id > 20 ORDER BY id LIMIT 10; uses WHERE id > 20 with ORDER BY id LIMIT 10, which matches keyset pagination logic.

3. Final Answer:

   SELECT * FROM users WHERE id > 20 ORDER BY id LIMIT 10; -> Option B

4. Quick Check:

   Keyset uses WHERE key > last_key [OK]

Hint: Keyset uses WHERE with key comparison, not OFFSET [OK]

Common Mistakes:

• Using OFFSET instead of WHERE for pagination
• Using equality (=) instead of greater than (>)
• Not ordering results by the key column

3. Given this REST API endpoint using keyset pagination:

GET /items?last_id=50&limit=5

And the database table items with IDs: [45, 47, 50, 52, 55, 60, 65], what will be the IDs returned by this request?

medium

A. [52, 55, 60, 65]

B. [45, 47, 50, 52, 55]

C. [52, 55, 60, 65, 70]

D. [51, 52, 53, 54, 55]

Solution

1. Step 1: Understand keyset pagination with last_id=50

   The API returns items with IDs greater than 50, limited to 5 results.

2. Step 2: Select IDs greater than 50 from the list

   IDs greater than 50 are [52, 55, 60, 65]. There are only 4 such items, so all are returned.

3. Final Answer:

   [52, 55, 60, 65] -> Option A

4. Quick Check:

   IDs > 50 limited to 5 = [52, 55, 60, 65] [OK]

Hint: Return items with ID > last_id, up to limit [OK]

Common Mistakes:

• Including items with ID equal to last_id
• Using offset instead of key comparison
• Assuming IDs are continuous numbers

4. You have this SQL query for keyset pagination:

SELECT * FROM orders WHERE order_date > '2024-01-01' ORDER BY order_date LIMIT 10;

But it returns duplicate rows when new orders are added. What is the likely cause?

medium

A. Ordering by a non-unique column causing duplicates

B. LIMIT value is too high causing repeats

C. Missing OFFSET clause to skip duplicates

D. Using > instead of >= in WHERE clause

Solution

1. Step 1: Identify ordering column uniqueness

   Ordering by order_date alone can cause duplicates if multiple rows share the same date.

2. Step 2: Understand keyset pagination requirements

   Keyset pagination requires ordering by a unique column or combination to avoid duplicates and missing rows.

3. Final Answer:

   Ordering by a non-unique column causing duplicates -> Option A

4. Quick Check:

   Order by unique key to avoid duplicates [OK]

Hint: Order by unique keys to prevent duplicates [OK]

Common Mistakes:

• Thinking OFFSET fixes duplicates
• Using >= instead of > causes duplicates
• Assuming LIMIT controls duplicates

5. You want to implement keyset pagination on a REST API that returns products sorted by price ascending, then by id ascending to break ties.
Which SQL WHERE clause correctly fetches the next page after last product with price=100 and id=50?

hard

A. WHERE price > 100 OR id > 50

B. WHERE price >= 100 AND id > 50

C. WHERE price > 100 AND id > 50

D. WHERE price > 100 OR (price = 100 AND id > 50)

Solution

1. Step 1: Understand multi-column keyset pagination

   When ordering by multiple columns, the WHERE clause must handle the first column and then the second to break ties.

2. Step 2: Analyze the correct condition

   The correct condition is price > 100 OR (price = 100 AND id > 50) to get all rows with higher price or same price but higher id.

3. Final Answer:

   WHERE price > 100 OR (price = 100 AND id > 50) -> Option D

4. Quick Check:

   Multi-column keyset uses OR + AND for tie-break [OK]

Hint: Use OR with AND to handle multi-column keys [OK]

Common Mistakes:

• Using AND instead of OR for first column
• Ignoring tie-break column in WHERE clause
• Using >= instead of > causing duplicates