Somewhere around a few hundred thousand rows, OFFSET starts costing more than it should, because most database engines still scan and discard every row before the offset instead of jumping straight to that position. Here's a working step-by-step for replacing it with keyset pagination.
Photo by Brett Sayles on Pexels
Step 1: Identify a sort column your queries already use
Keyset pagination needs a column, or set of columns, that your query is already sorting by, since the technique works by seeking to a boundary value in that sort order rather than skipping a row count. A created_at timestamp or an auto-incrementing primary key both work well, as long as the column has an index.
CREATE INDEX idx_items_created_at ON items (created_at DESC);
Without this index, keyset pagination doesn't get you anything over offset pagination, since the database still has to scan the full table to find the boundary. The index is what turns "everything after this value" into a direct seek instead of a scan.
Step 2: Replace OFFSET with a WHERE boundary
The core change is small. Instead of LIMIT 20 OFFSET 40, the client sends back the sort-key value of the last row it saw, and that becomes a WHERE clause boundary.
-- Before: gets slower as offset grows
SELECT id, title, created_at FROM items
ORDER BY created_at DESC LIMIT 20 OFFSET 40000;
-- After: constant cost regardless of position
SELECT id, title, created_at FROM items
WHERE created_at < '2026-09-14T10:00:00Z'
ORDER BY created_at DESC LIMIT 20;
The index created in step one lets PostgreSQL and most other engines seek directly to that boundary value rather than counting through every row before it, which is the entire performance win in one clause.
Step 3: Add a tie-breaker for duplicate sort values
A single timestamp column almost never guarantees uniqueness, and two rows sharing the same created_at will both satisfy the boundary condition, which can cause a row to be silently skipped or repeated at the page split. Fix this with a composite key that includes a genuinely unique column, usually the primary key.
SELECT id, title, created_at FROM items
WHERE (created_at, id) < ('2026-09-14T10:00:00Z', 8842)
ORDER BY created_at DESC, id DESC
LIMIT 20;
Row-value comparisons like this are supported natively in most SQL engines, including MySQL and PostgreSQL, and they express the tie-break logic in one clause instead of a more error-prone nested OR condition.
Step 4: Return the next boundary value to the client
The API response needs to hand the client whatever it should send back on the next request, which is simply the sort-key values of the last row in the current page. Most teams either return this as raw fields or wrap it in an encoded cursor token for a public API.
const lastRow = results[results.length - 1];
const nextBoundary = { createdAt: lastRow.created_at, id: lastRow.id };
Step 5: Handle the empty and first-page cases explicitly
The first page has no boundary to filter on, so the query needs a variant without the WHERE clause, and the last page needs to return an explicit signal that no more rows exist rather than making the client guess from an empty or short result set.
-- First page: no boundary yet
SELECT id, title, created_at FROM items
ORDER BY created_at DESC LIMIT 20;
Returning an explicit has_next_page boolean in the response, rather than relying on the client to infer it from result length, avoids an entire category of off-by-one bug in pagination UI code.
Step 6: Verify with a test that forces duplicate timestamps
The tie-breaking bug in step three only shows up when two rows actually share a timestamp, which most test fixtures don't create by accident. Write a test that deliberately inserts rows with identical created_at values and asserts none are skipped or repeated across a page boundary.
test('keyset pagination handles duplicate created_at without skipping rows', () => {
const page = fetchPage({ createdAt: '2026-09-14T10:00:00Z', id: 1 }, 2);
expect(page.map(r => r.id)).not.toContain(1); // already seen, shouldn't repeat
});
Step 7: wrap the boundary in an opaque cursor for public APIs
If this endpoint is consumed internally only, returning the raw boundary fields is fine. If it's a public API, wrap them in an encoded token instead, so clients never depend directly on your internal column names or sort logic, and you retain the freedom to change either later without a breaking release.
function encodeCursor(state) {
return Buffer.from(JSON.stringify(state)).toString('base64url');
}
function decodeCursor(cursor) {
return JSON.parse(Buffer.from(cursor, 'base64url').toString());
}
MDN's documentation on base64url encoding is worth reading before hand-rolling this, since the URL-safe variant avoids padding characters that would otherwise need extra escaping in a query string.
Step 8: benchmark before and after on a realistic dataset size
The performance win from keyset pagination is invisible on a development database with a thousand rows, since offset pagination is cheap at that scale too. Benchmark both approaches against a dataset sized closer to production, or at minimum against a seeded table in the low millions of rows, so the comparison actually demonstrates the constant-cost behavior keyset pagination is supposed to provide.
EXPLAIN ANALYZE
SELECT id, title, created_at FROM items
WHERE (created_at, id) < ('2026-09-14T10:00:00Z', 8842)
ORDER BY created_at DESC, id DESC LIMIT 20;
Comparing the EXPLAIN ANALYZE output for a keyset query against the equivalent OFFSET query at a deep page number is the clearest way to show a skeptical teammate, or a client, exactly where the performance difference comes from rather than just asserting it exists.
Step 9: handle the "jump to page N" request gracefully, if you still need it
The one thing keyset pagination genuinely gives up is arbitrary page-number navigation. If part of your UI still needs it, for example an admin table with a page-number control, the pragmatic answer is usually to keep offset pagination available for that specific view while using keyset pagination for the high-traffic, deep-scrolling paths, rather than trying to force one technique to do both jobs equally well.
-- Reserve offset pagination for shallow, occasional page jumps only
SELECT id, title, created_at FROM items
ORDER BY created_at DESC LIMIT 20 OFFSET :page_number * 20;
Capping how deep this offset path is allowed to go, refusing requests past a defined page limit, keeps the expensive query pattern contained to the shallow range where it's actually cheap, instead of letting a user or a script page arbitrarily deep through the same code path that made the original migration necessary.
Step 10: keep the migration reversible until you've verified it in production
Ship the keyset endpoint alongside the existing offset endpoint under a feature flag rather than replacing it outright, and compare results between the two for a sample of real traffic before fully cutting over. This catches discrepancies, like a subtle timezone handling difference between the two query paths, while there's still an easy fallback, rather than after the old code has already been deleted.
Keyset pagination costs a bit more upfront thought than offset pagination, mostly around the tie-breaker and the encoding of the boundary value, but the payoff is a query that costs roughly the same whether it's page 2 or page 20,000. The full comparison of offset, keyset, and cursor pagination, including the API-facing cursor variant of this exact technique, covers the parts this walkthrough doesn't.
If your team is dealing with a table that's already outgrown offset pagination, 137Foundry's web development team has done this migration enough times to know where the tie-breaker bug usually hides.
Top comments (0)