Why We Ripped Out Offset Pagination on a Client Project
A client came to us with a support dashboard that had gotten slow. Not "annoying" slow, "the support team stopped using page 3 and beyond because it took eleven seconds to load" slow. The table behind it had grown from a few thousand rows at launch to over four million, and nobody had touched the pagination code in two years because it never needed touching before. It just kept getting slower as the table grew, quietly, until it wasn't quiet anymore and the support team started filing tickets about their own ticketing system.
Classic LIMIT/OFFSET pagination, sorted by created date descending. At page 1 it's instant. At page 400, the database is scanning and discarding ten thousand rows just to hand back twenty five of them. The deeper into the list you page, the more work the database throws away before returning anything useful. It's a linear cost curve disguised as a flat one, because nobody notices until someone actually pages deep enough into the list to feel it, and by then the table has usually grown large enough that the cost is severe.
There was a second bug hiding behind the slowness that the client hadn't even flagged as related: rows were occasionally duplicating or vanishing between pages. Turns out that's not a separate bug at all, it's the same root cause wearing a different symptom. Offset pagination assumes the result set holds still between requests. On a support dashboard with new tickets landing constantly throughout the business day, it never does. Every new ticket shifts everything below it in the sort order, and offset-based paging has no way to notice or correct for that shift.
We rewrote the query to use a cursor built from the sort column plus the primary key as a tiebreaker, instead of an offset. Rather than "skip four hundred pages worth of rows," the new query says "give me everything after this specific row," which the database can answer with a direct index seek regardless of how deep into the list you are. Page 400 now costs the same as page 1, because the query plan doesn't scale with how far into the history someone is browsing.
SELECT * FROM tickets WHERE (created_at, id) < (:cursor_created_at, :cursor_id) ORDER BY created_at DESC, id DESC LIMIT 25;
We paired this with a composite index matching the sort order exactly, which is the part that's easy to skip and makes the whole rewrite pointless if you do skip it. Without the matching index, the database falls back to a sequential scan and you've reinvented the original problem with extra engineering effort spent on nothing. We verified the index was actually being used with EXPLAIN ANALYZE against production-sized data before calling the migration done, using the same approach documented on PostgreSQL's official site, and cross-checked our understanding of the scan types against Use The Index, Luke while we were at it since it's the clearest free reference on the topic.
The part that actually fixed the disappearing rows
The compound key, sort column plus unique tiebreaker, is what fixed the duplicate and missing row bug, not just the speed problem. Because the cursor points at a specific, unambiguous row rather than a position that shifts as data changes, new inserts elsewhere in the table stop affecting pages the user has already loaded or is about to load next. The support team hasn't reported a phantom missing ticket since we shipped this, and page load times stopped being a topic in their weekly standup.
The one complication we didn't expect
The client's support tool had a saved-search feature that stored a specific page number in a bookmark URL, something like /tickets?status=open&page=12. Cursor pagination doesn't have page numbers, so those bookmarks needed a migration path too. We ended up storing the filter parameters in the bookmark and re-fetching from the start of the filtered result set rather than trying to preserve an exact page position, which was a reasonable compromise the client's support team barely noticed after the first week of using it, since most bookmarked searches were really about the filter criteria rather than a specific scroll depth anyway.
What this cost and what it saved
The rewrite itself took about a day and a half, most of which went into writing the concurrent-write test that actually proves the fix works rather than just looking faster in a quick manual check. We also had to touch the frontend slightly, since numbered "jump to page 47" style navigation doesn't map cleanly onto cursor pagination, and the client agreed that next/previous navigation was a fine trade for consistent sub-100-millisecond load times at any depth.
We wrote up the full pattern, including the encoding scheme we use for cursors and how we handle backward pagination without breaking the client's existing "previous page" button, over at 137foundry.com if you want the implementation details rather than just the war story. It's the same approach we now default to on any list endpoint we build that's likely to outgrow a few hundred thousand rows within its first year or two in production.
The eleven-second page load is now consistently under 100 milliseconds regardless of how deep the support team pages into the ticket history, whether that's page 2 or the equivalent of page 4,000. Sometimes the fix really is that clean once you find the actual bottleneck instead of throwing more infrastructure at the symptom and hoping a bigger database instance makes the problem go away on its own.
How we'd validate this on a new project from day one
Now that we've been through this once, we don't wait for a table to grow large enough to hurt before deciding which pagination strategy to use. If a table is going to see steady writes, orders, events, tickets, logs, anything transactional, we default to cursor pagination from the first implementation rather than treating it as a later optimization. Retrofitting is more work than building it correctly the first time, mostly because of the frontend and bookmark implications described above, which are far easier to design around from the start than to migrate around later once real users have built habits around numbered pages.
Why we're writing this up at all
We're not the first team to hit this, and we won't be the last. Offset pagination is the default almost every framework and ORM reaches for out of the box, which means most engineers build their first several list endpoints with it before ever running into the wall a growing table eventually puts up. The bug is well understood in database circles, but it keeps surprising product teams because it's invisible until a table crosses a size threshold that staging environments almost never reach. If this sounds like a dashboard you're maintaining right now, it's worth checking your own slow query logs for anything with a large OFFSET value before it turns into a client complaint instead of a proactive fix on your own timeline.
What we'd tell a team about to make the same call
If you're staring at a slow dashboard right now trying to decide whether this is worth fixing today or something to defer to next quarter, the honest signal to look at is growth rate, not current pain. A table growing by a few hundred rows a month can probably wait. A table growing by tens of thousands of rows a month is on a much shorter clock than it feels like from the inside, because the slowdown compounds quietly for a long stretch before it becomes obviously, undeniably broken. We'd rather see a team fix this while it's a mild annoyance on page 40 than wait until it's an eleven-second load on page 3 that support has already started working around by avoiding deep pages entirely, which is its own kind of hidden cost since it means real information the support team needs is effectively inaccessible to them without anyone flagging it as a problem worth escalating.
