A query that returns in 4 milliseconds on page 1 and 4 seconds on page 4,000 looks like a bug report waiting to happen, except nothing in the code actually changed between those two requests. The query plan is identical. Only the offset number is different. That's the part that makes this failure mode so easy to miss until it's already in production.
Photo by Pixabay on Pexels
What OFFSET actually asks the database to do
OFFSET doesn't mean "start reading at position N." It means "read and discard the first N rows, then start returning results." The database has no shortcut for skipping directly to a row position in a sorted result set, so satisfying OFFSET 500000 genuinely costs roughly 500,000 rows of scan-and-discard work before the first returned row even gets evaluated.
-- This does real work for every one of the 500,000 skipped rows
SELECT id, title FROM articles
ORDER BY created_at DESC LIMIT 20 OFFSET 500000;
This is true across essentially every relational engine in common use, including PostgreSQL, because it's a property of how offset-based result sets are defined, not an implementation quirk of one particular database.
Why an index doesn't fix it
The instinct when a query is slow is to add an index, and that instinct is only half right here. An index on the sort column speeds up the ordering itself, turning an expensive sort into a fast index scan. It does nothing for the discard cost of the offset, because the database still has to walk past that many index entries before it reaches the ones it's actually going to return.
CREATE INDEX idx_articles_created_at ON articles (created_at DESC);
This index makes every page faster in absolute terms, but the relative problem stays exactly the same: page 20,000 is still proportionally far more expensive than page 1, just with a lower constant factor than an unindexed scan would produce.
Why nobody notices until it's a specific complaint
The query plan for OFFSET 40 and OFFSET 400000 looks structurally identical in EXPLAIN output. There's no red flag, no warning, no changed access pattern to spot in a code review. The degradation is smooth and gradual as the table grows and as users page deeper, which means it typically surfaces as a single confusing support ticket, "page 40 of my search results is slow," rather than a monitoring alert anyone would think to set up in advance.
By the time that ticket lands, the underlying table has usually been growing for months, and the pagination code has been unchanged the entire time. The bug was always there. It just needed enough data and a user patient enough to page deep enough to trigger it.
The fix doesn't require abandoning SQL pagination
The alternative isn't giving up on paginated queries, it's changing what the query asks for. Keyset pagination replaces "skip N rows" with "give me rows after this specific boundary value," which the database can satisfy with a direct index seek instead of a scan-and-discard.
-- Constant cost regardless of how deep into the table this boundary sits
SELECT id, title FROM articles
WHERE created_at < '2026-09-14T10:00:00Z'
ORDER BY created_at DESC LIMIT 20;
The tradeoff is losing the ability to jump to an arbitrary page number, which matters for some UIs and doesn't matter at all for most infinite-scroll feeds or API list endpoints, where "next page" is the only navigation anyone actually uses.
Where this shows up outside plain SQL
The same underlying cost applies to document databases too. MongoDB's .skip() method has documented the same behavior for years, since skipping documents in a cursor requires the same discard-as-you-go approach as SQL's OFFSET. Large-scale public APIs like Stripe's avoid the problem entirely by never exposing offset-style pagination on their list endpoints in the first place, using cursor-based pagination as the only option from day one.
How to actually catch this before a client does
Most teams only find this problem reactively, after a specific complaint. Catching it proactively means logging the offset value alongside query duration for any paginated endpoint, then alerting when the two start correlating. A query duration that climbs steadily as a function of a single request parameter is a distinctive enough signature that it's worth a dedicated check rather than relying on generic slow-query monitoring to happen to flag it.
-- A quick audit: are any endpoints regularly requesting offsets past a risky threshold?
SELECT query, calls, mean_exec_time FROM pg_stat_statements
WHERE query LIKE '%OFFSET%' ORDER BY mean_exec_time DESC LIMIT 20;
PostgreSQL's pg_stat_statements extension makes this kind of audit straightforward on an existing database without adding new instrumentation, since it already tracks execution time per normalized query shape. Running this once against a production database is often enough to reveal whether the offset problem is already present somewhere, well before it generates its first support ticket.
Why teams keep rediscovering this same bug independently
Part of why this pattern keeps surprising experienced engineers is that it doesn't fail the way most performance bugs do. A slow endpoint usually correlates with load, time of day, or a recent deploy, all of which point an investigation somewhere specific. This one correlates with a single request parameter that looks completely benign in code review, since OFFSET reads as simple arithmetic rather than as an implicit full-table-scan-and-discard operation.
That mismatch between how the code reads and what the database actually does underneath it is the whole story. Nothing about LIMIT 20 OFFSET 500000 looks dangerous on a screen. The cost is entirely a property of how the query executor satisfies it, invisible from the SQL text itself.
A cheap mitigation if a full migration isn't feasible right now
Not every team can justify a full keyset migration immediately, especially against a legacy endpoint with many existing integrations. A lower-effort mitigation is simply capping how deep offset pagination is allowed to go, refusing requests past a defined page limit with a clear error rather than letting the query execute and take however long it takes.
-- Refuse offset requests past a defined depth instead of letting them run slow
IF requested_offset > 10000 THEN
RAISE EXCEPTION 'Page depth exceeds supported range; use cursor pagination instead';
END IF;
This doesn't solve the underlying problem, and any client that genuinely needs to page that deep will need the real fix eventually, but it converts an unbounded, silently degrading query into a fast, explicit error, which is a meaningfully better failure mode while a proper migration gets scheduled.
The honest tradeoff
Offset pagination isn't wrong, it's a reasonable default for small or slow-growing tables where the simplicity is worth more than the eventual cost. The mistake is treating it as a default that scales without a plan to revisit it, and then discovering the actual cost curve only after a table has already grown large enough for the discard cost to be user-visible.
Checking whether any endpoint's offset value regularly climbs into the tens of thousands is a five-minute audit that catches this before it becomes a ticket. The full walkthrough of keyset and cursor pagination, including the SQL for handling tie-breaking correctly, covers the replacement in more depth than this piece does.
If a specific endpoint in your product is already exhibiting this pattern, 137Foundry has diagnosed and migrated this exact problem for products where nobody realized it was the offset value causing the slowdown until someone actually looked.
Top comments (0)