Pagination that stays fast on a large table

Written By: BrainSoft In Backend

A table with a few million rows and an ORDER BY created_at DESC LIMIT 20 OFFSET 400000 query looks fine in development. On production data the page takes four seconds, the database burns CPU, and the API starts timing out under load. The query is not slow because the table is big. It is slow because the database has to walk and throw away every row before the offset.

You fix it by replacing OFFSET with a keyset cursor built from the sort column plus a tiebreaker, and by adding a matching composite index. Below are the concrete steps, the SQL, and the traps that bite when the sort key is not unique.

Why OFFSET gets slower as you page deeper

OFFSET asks the engine to produce N rows and discard them. The cost grows linearly with the page number, so page 1 is cheap and page 20,000 is not. An index on the sort column helps the sort, but it does not stop the row-by-row skip. This is the part people miss: the index is being used, the plan looks healthy, and the query is still O(offset).

The fix is to stop describing the page by how many rows to skip and start describing it by the last row the client already saw. That turns the query into a range scan that starts at a known point in the index. It reads twenty rows and stops.

Step by step: keyset pagination

1. Pick the sort order and make it total. If you sort by created_at, two rows can share a timestamp, so add id as a tiebreaker. The cursor is now a pair, not a single value.
2. Add a composite index that matches the sort exactly, in the same column order and direction.
3. Change the query to compare against the cursor instead of using OFFSET.
4. Return the cursor of the last row in the response so the client can ask for the next page.
5. Keep an OFFSET path only for the first page or for admin screens where deep paging does not happen.

The index is the whole trick. Get the column order wrong and the planner falls back to a sort.

CREATE INDEX CONCURRENTLY idx_orders_created_id
  ON orders (created_at DESC, id DESC);

Then the query. Note the row-value comparison, which keeps the cursor logic honest when timestamps repeat.

SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The first page omits the WHERE clause. Every later page passes the last row's created_at and id. The client does not need to know the page number, only the cursor, which you can base64 into a single opaque string so nobody depends on the internals.

Where it breaks, and how to handle it

• Jumping to page 50 in the UI. Keyset cannot do that without walking. If the product genuinely needs numbered pages, keep OFFSET for shallow pages and switch to keyset past a threshold, or drop the page numbers.
• Changing sort direction per request. A single index cannot serve both ASC and DESC on the same columns efficiently in every engine. Add the reverse index if the toggle is used.
• Filtered lists. The index has to cover the filter columns too, or the range scan degrades into a scan plus filter. Check the plan with EXPLAIN ANALYZE rather than assuming.
• Counts. A COUNT(*) over millions of rows is its own slow query. Show an approximate count or a "load more" button instead of a total. If the number matters, maintain it in a counter table.

On the API side, keep the page size fixed and capped. A client that can ask for 10,000 rows will eventually ask for 10,000 rows, and no index saves you from returning that payload. If you are wiring this into a larger system and want a second pair of eyes on the query plans, our services cover exactly this kind of work.

Frequently asked questions

#Backend