Keyset pagination for performance in Rest API - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of keyset pagination query

Consider a REST API that uses keyset pagination on a table sorted by created_at and id. What is the output of the following SQL snippet when last_seen_created_at = '2024-01-01 10:00:00' and last_seen_id = 100?

SELECT * FROM posts WHERE (created_at, id) < (:last_seen_created_at, :last_seen_id) ORDER BY created_at DESC, id DESC LIMIT 3;

Rest API

last_seen_created_at = '2024-01-01 10:00:00'
last_seen_id = 100

-- Assume posts table has these rows sorted by created_at DESC, id DESC:
-- (2024-01-01 10:00:00, 100), (2024-01-01 09:59:59, 105), (2024-01-01 09:59:59, 102), (2024-01-01 09:59:58, 110), (2024-01-01 09:59:57, 101)

A Rows with (2024-01-01 10:00:00, 101), (2024-01-01 09:59:59, 105), (2024-01-01 09:59:58, 110)

B Rows with (2024-01-01 10:00:00, 100), (2024-01-01 09:59:59, 105), (2024-01-01 09:59:59, 102)

C Rows with (2024-01-01 09:59:59, 105), (2024-01-01 09:59:59, 102), (2024-01-01 09:59:58, 110)

D Rows with (2024-01-01 09:59:58, 110), (2024-01-01 09:59:57, 101), (2024-01-01 09:59:59, 102)

💡 Hint

Remember that keyset pagination uses the tuple comparison to get rows strictly less than the last seen key.

The query fetches rows where the tuple (created_at, id) is less than (2024-01-01 10:00:00, 100) in descending order. So it excludes the last seen row and gets the next 3 rows with smaller tuples.

🧠 Conceptual

intermediate

Why use keyset pagination over offset pagination?

Which of the following is the main reason to prefer keyset pagination over offset pagination in REST APIs?

A Keyset pagination avoids skipping rows and is faster on large datasets.

B Keyset pagination allows jumping to any page number instantly.

C Keyset pagination requires less client-side state management.

D Keyset pagination always returns results in random order.

💡 Hint

Think about how offset pagination slows down as the offset grows.

Offset pagination becomes slower with large offsets because the database must scan and skip many rows. Keyset pagination uses the last seen key to efficiently fetch the next set without scanning skipped rows.

🔧 Debug

advanced

Identify the bug in keyset pagination query

Find the bug in this keyset pagination SQL query snippet:

SELECT * FROM messages WHERE created_at < :last_seen_created_at ORDER BY created_at DESC LIMIT 5;

Assume last_seen_created_at is the timestamp of the last message from the previous page.

A It can skip messages with the same created_at timestamp as last_seen_created_at.

B It will return duplicate messages from the previous page.

C It uses ascending order instead of descending order.

D It does not limit the number of rows returned.

💡 Hint

Think about what happens if multiple messages share the same timestamp.

The query only filters by created_at < last_seen_created_at, so messages with the same timestamp as the last seen message are skipped entirely, causing missing data. Keyset pagination should include a tie-breaker column like id to avoid this.

📝 Syntax

advanced

Correct keyset pagination WHERE clause syntax

Which option correctly implements a keyset pagination WHERE clause for sorting by created_at DESC and id DESC with last seen values :last_created_at and :last_id?

A WHERE created_at > :last_created_at AND id > :last_id

B WHERE (created_at, id) < (:last_created_at, :last_id)

C WHERE created_at > :last_created_at OR (created_at = :last_created_at AND id > :last_id)

D WHERE created_at < :last_created_at OR (created_at = :last_created_at AND id < :last_id)

💡 Hint

Remember that for descending order, smaller tuples come after larger ones.

Option D correctly expresses the tuple comparison for descending order by checking if created_at is less than last_created_at or equal but id is less. Option D is invalid syntax in many SQL dialects. Options C and D use the wrong comparison direction.

🚀 Application

expert

Implement keyset pagination in REST API response

You have a REST API endpoint that returns a list of products sorted by updated_at DESC and product_id DESC. You want to implement keyset pagination. Which approach correctly returns the next page token and the next page of results?

A Return the current page number and use <code>OFFSET page_number * page_size</code> in the next request to fetch the next page.

B Return the last product's <code>updated_at</code> and <code>product_id</code> as a cursor token. Use them in the next request to filter with <code>WHERE (updated_at, product_id) &lt; (:last_updated_at, :last_product_id)</code> and order by <code>updated_at DESC, product_id DESC</code>.

C Return the total count of products and calculate the next page by dividing count by page size.

D Return the first product's <code>updated_at</code> and <code>product_id</code> as a cursor token and filter with <code>WHERE (updated_at, product_id) &gt; (:first_updated_at, :first_product_id)</code>.

💡 Hint

Keyset pagination uses the last seen key to fetch the next page efficiently.

Option B correctly uses the last product's keys as a cursor token and filters the next page with a tuple comparison in descending order. Option B uses offset pagination, which is less efficient. Option B is unrelated to keyset pagination. Option B uses the first product's keys and the wrong comparison direction.