Why page 500 is slow: offset vs keyset pagination

Someone reports that your list view is fine at the top and unusable near the bottom. Nothing is wrong with the data, the index or the network. The problem is the shape of the query.

What OFFSET actually does

The obvious way to page through records:

SELECT * FROM bookings ORDER BY created_at DESC LIMIT 50 OFFSET 25000;

Read that as an instruction and it says: produce the rows in order, then throw the first 25,000 away.

The database does exactly that. It cannot skip to row 25,001 without first establishing which rows come before it, so the work grows with the offset. Page 1 examines 50 rows. Page 500 examines 25,050 and discards 25,000 of them.

The cost is linear in how deep you are, which is why the slowness sneaks up: nobody notices during development, because nobody scrolls to page 500 with a thousand test rows in the table.

The second problem, which is worse

Offset paging is also wrong in a way that's easy to miss, and it has nothing to do with speed.

You fetch page 1 — the newest 50 bookings. While the reader looks at it, somebody creates a booking. They click "next page". The new record has pushed everything down by one, so the row that was #50 is now #51 — and it appears again, on page 2.

If a record is deleted instead, a row is silently skipped and never seen at all. Any pagination built on counting positions in a list that other people are editing will double-count and skip.

For a UI that's a bug. For a nightly export loop, it's data loss you don't find out about for a month.

What keyset paging does instead

Keyset paging — also called seek paging, or cursor paging — stops counting positions and starts naming a place:

SELECT * FROM bookings
WHERE (created_at, id) < ('2026-07-14 09:12:04', 'rec_hfsd6hs3nfhu')
ORDER BY created_at DESC, id DESC
LIMIT 50;

Instead of "skip 25,000", this says "give me what comes after this exact row." With an index on (created_at, id), the database jumps straight to that position and reads 50 rows. It does the same amount of work at page 500 as at page 1 — and it can't double-count, because the boundary is a specific record, not a number that shifts when the list does.

Three details that make it work:

1. The sort key must be unique. created_at alone isn't — two records created in the same millisecond would straddle the boundary and one would be lost. Appending the id makes the key unique, which is why the comparison is on the pair.
2. The comparison is a tuple comparison, not two separate conditions. In Postgres (a, b) < (x, y) is a single lexicographic comparison and matches the index. Writing a < x AND b < y means something different and is wrong.
3. The cursor is opaque to the client. Encode the values, hand them back as one string, and you can change the sort key later without breaking every integration.

The trade you're making

Keyset paging is not free of downsides, and the honest list is short:

• You can't jump to page 37. There's no way to construct the boundary for a page you haven't reached. Numbered pagination genuinely needs offset — but numbered pagination over 100,000 records is a UI nobody uses anyway.
• The cursor has to encode the sort. Change the sort order and old cursors are meaningless; they have to be rejected rather than silently misread.
• A total count is a separate question. "Showing 1–50 of 24,318" needs its own COUNT(*), and that count is the expensive part, not the page.

For an infinite-scrolling grid or an API that a script walks front to back — which is nearly every real case — none of those matter.

How Tabla does it

Every records listing is keyset-paged. A response carries a bookmark, and passing it back gets the next page:

GET /api/tables/tbl_…/records?page_size=100
GET /api/tables/tbl_…/records?page_size=100&bookmark=…

The bookmark is absent — not null — on the last page, which is how a loop knows it's done. Measured on a 100,000-row table, the query at page 500 costs roughly 17–19 ms of server-side work, about the same as page 1. That's the whole point of the technique: depth is free.

The API reference has the full paging contract.