Part 5 of 5 · 4 min read
Exercises
Easy
Easy 1: Name why OFFSET gets slower (15 minutes)
State why OFFSET 100000 is more expensive than OFFSET 0 for the same query.
Done when: the need to generate and discard the first hundred thousand rows internally is correctly named.
Hint
OFFSET 100000 still has to generate and discard the first hundred thousand rows before returning this page's rows.
Easy 2: Name what keyset pagination remembers (15 minutes)
State what keyset pagination remembers between pages, instead of a row count.
Done when: the sort key's value from the last row of the previous page is correctly named.
Hint
The sort key's value from the last row of the previous page.
Easy 3: Name what a window function computes (15 minutes)
State what ROW_NUMBER() OVER (ORDER BY placed_on) computes for each row.
Done when: that row's position, or rank, within the sorted order is correctly described.
Hint
That row's rank, or position, within the overall sorted order.
Medium
Medium 1: Compare costs at a deep page (25 minutes)
Explain why page one thousand of an OFFSET-paginated result is more expensive than page one, while page one thousand of a keyset-paginated result costs roughly the same as page one.
Done when: the explanation correctly ties OFFSET's growing cost to discarding more rows at deeper pages, and keyset's flat cost to always filtering directly from a remembered key regardless of depth.
Hint
OFFSET's cost grows because deeper pages mean more discarded rows; keyset's cost stays flat because it always filters directly from a remembered key, regardless of depth.
Medium 2: Write a keyset pagination query (25 minutes)
Given a previous page ending at order_id = 5000, write the keyset query for the next page of orders ordered by order_id, twenty rows per page.
Done when: a correct query such as SELECT * FROM orders WHERE order_id > 5000 ORDER BY order_id LIMIT 20; is given.
Hint
SELECT * FROM orders WHERE order_id > 5000 ORDER BY order_id LIMIT 20;
Medium 3: Decide when a window function is actually needed (30 minutes)
A page needs to display "showing result 1,042 of 50,000" alongside paginated data. Decide whether OFFSET, keyset pagination, or a window function best addresses this specific requirement.
Done when: a window function such as ROW_NUMBER() is correctly identified as the tool that directly provides the row's position, which neither OFFSET nor plain keyset pagination computes on their own.
Hint
A window function such as ROW_NUMBER(), since it directly computes the row's position, something neither OFFSET nor keyset pagination provide on their own.
Hard
Hard 1: Explain why keyset pagination needs a sargable filter and an index (30 minutes)
Explain why keyset pagination's performance benefit depends on both the previous page's sargable condition and an existing index on the sort column.
Done when: the explanation correctly ties keyset pagination's constant cost to the index allowing the filter to jump directly to the right starting point, noting that without a supporting index, the same filter would fall back to a full scan.
Hint
The index lets the sargable filter jump directly to the right starting point in sorted order; without that index, the same filter condition would require scanning from the beginning, losing the constant-cost benefit.
Hard 2: Diagnose a slow, deep page in production (35 minutes)
A paginated API endpoint using OFFSET becomes noticeably slower as users click through to later pages, especially past page fifty. Explain the cause and propose a fix.
Done when: the cause is correctly identified as OFFSET's growing cost with page depth, with keyset pagination proposed as the fix, replacing the row count with a remembered key from the last row of each page.
Hint
The cause is OFFSET's growing cost with depth, discarding more rows on each later page; switching to keyset pagination, remembering the last row's key instead of counting rows, fixes this.
Hard 3: Handle a limitation of keyset pagination (40 minutes)
A user interface currently lets users jump directly to an arbitrary page number, such as page 47. Explain why this specific feature is harder to support with pure keyset pagination than with OFFSET, and propose how a system might handle it.
Done when: the explanation correctly notes that keyset pagination naturally supports moving to the next or previous page via a remembered key, but jumping to an arbitrary page number requires knowing that page's starting key in advance, which pure keyset pagination does not provide; a reasonable proposal might use OFFSET only for the specific, rarer case of direct page-number jumps, while using keyset pagination for ordinary next/previous navigation.
Hint
Keyset pagination naturally supports next/previous navigation via a remembered key, but jumping to an arbitrary page number needs that page's starting key known in advance, which keyset alone does not provide; a reasonable system might reserve OFFSET for the rarer direct-jump case and use keyset pagination for ordinary browsing.