RedBite’s admin order list paginates with the obvious SQL:
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 50 OFFSET 250000;Page 2 (OFFSET 50) returns in a few milliseconds. By the time an admin
clicks through to page 5,000 (OFFSET 250000) during a bulk audit, the
same query takes 8 seconds — on a table that isn’t even RedBite’s
largest. Nothing about the table changed between those two requests.
OFFSET itself is the entire cost.
Why OFFSET gets slower as the page number grows
OFFSET 250000 doesn’t mean “jump straight to row 250,000.” Postgres has
no way to know which physical row is the 250,000th in sort order without
first counting through everything before it. The actual execution: scan
the index in created_at DESC order, walk past the first 250,000
matching rows one at a time, discarding each one, and only then start
returning the 50 rows the query actually asked for.
EXPLAIN ANALYZE SELECT * FROM orders ORDER BY created_at DESC LIMIT 50 OFFSET 250000;
Limit (cost=12863.42..12865.99 rows=50)
-> Index Scan Backward using orders_created_at_idx on orders
(actual time=0.05..187.32 rows=250050 loops=1)rows=250050 in the actual line is the tell: Postgres read and
discarded 250,000 rows to get to the 50 it returns. OFFSET 50 on page 2
discards 50 rows — trivial. OFFSET 250000 on page 5,000 discards
250,000 — and that discard cost grows linearly with the page number,
forever, with no index able to shortcut it, because the index only tells
Postgres the order of rows, not how to skip directly to an arbitrary
position in that order.
Fix: cursor-based pagination — remember where you left off
Instead of asking Postgres to count through N rows every single page, remember the last row you actually saw, and ask for “everything after that specific row” instead of “everything after position N.” Postgres’s index can jump straight to a specific value efficiently — that’s what indexes are for — it just can’t jump to a specific position without counting there.
-- first page: no cursor yet
SELECT * FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 50;
-- next page: cursor = (created_at, id) of the last row from the previous page
SELECT * FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 50;EXPLAIN ANALYZE SELECT * FROM orders
WHERE (created_at, id) < ('2026-08-14 09:00:00', 48213)
ORDER BY created_at DESC, id DESC LIMIT 50;
Limit (cost=0.43..4.21 rows=50)
-> Index Scan Backward using orders_created_at_id_idx on orders
(actual time=0.02..0.09 rows=50 loops=1)rows=50 in the actual line, not 250050 — Postgres’s index seeks
directly to the cursor value and reads exactly the 50 rows needed, with
no discard pass at all. This query costs the same whether it’s “page 2”
or “page 5,000,” because there is no page number in it — only “give me
the next 50 rows after this specific point,” which is a seek, not a scan.
Why the tiebreaker column (id) matters: created_at alone isn’t
guaranteed unique — two orders can share a timestamp down to the stored
precision. Without id as a tiebreaker in both the ORDER BY and the
WHERE comparison, rows with a duplicate created_at value could be
skipped or repeated across page boundaries. The composite (created_at, id) comparison guarantees a strict, total order with no ties, so paging
forward never drops or duplicates a row.
What cursor pagination gives up, on purpose
Cursor pagination isn’t a strict upgrade — it trades away two things
OFFSET gives you for free, and both matter for deciding where to use
it:
- No “jump to page N” UI. A cursor only knows “next” and “previous”
relative to where you are; it can’t compute what row starts page 350
without walking there. If the UI genuinely needs numbered page links
(common in admin tools, less common in infinite-scroll feeds),
OFFSET— accepted as slow on deep pages, or capped at a reasonable max page depth — may still be the right trade for that specific screen. - Rows inserted between page loads shift nothing, which is sometimes
surprising. With
OFFSET, a new row inserted while paging can shift every subsequent row’s position by one, occasionally causing a row to appear twice or get skipped across page loads. Cursor pagination doesn’t have this bug — each page is defined relative to a specific row value, not a position — but it also means a newly inserted row at the top won’t retroactively appear on a page you’re already past, which is usually what you want and occasionally not what a user expects from a live feed.
What RedBite shipped
Kept OFFSET for the first couple of pages’ UI (page 1, 2, 3 links,
genuinely useful for an admin jumping around near the top) and switched
to cursor-based “load more” for anything past that — which, in practice,
is where every slow query in the logs was actually coming from. The
8-second page 5,000 query doesn’t exist anymore, because nothing asks for
“page 5,000” anymore — it asks for “the 50 orders after this one,”
every time, at the same cost regardless of how deep into the list that
is.
Hit a pagination UI that genuinely needs both jump-to-page and cursor
performance? Drop it in #scaling on Discord.