I recently came across a small commit switching one query from offset to cursor pagination. A few lines changed. Ordinary Tuesday stuff.
But it sent me right down memory lane, because there is a special kind of wahala that never shows up in code review, never fails a test, and never pages anybody at 3am on launch day. It waits. It sits quietly inside a batch job you wrote when the table had 40,000 rows, behaving itself perfectly, until one random Tuesday three years later the same job starts chopping memory like it skipped breakfast. And you’re staring at an out-of-memory crash asking the ancient question: what changed?
Nothing changed. That’s the whole trick. You didn’t change anything. The table did.
The entire answer in one line
If you carry only one thing from this post, carry this: it depends on who is doing the paging.
If a human is clicking through pages in a UI, page 3, then page 8, then back to page 2, that is offset work. Humans jump around, and offset lets them.
If a machine is grinding through the whole table in the background, a batch job, a sync, a nightly export, anything that starts where it stopped and keeps marching, that is cursor work. Machines don’t jump around. They march forward and want to resume exactly where they left off.
Get that split right and most of your pagination pain disappears. Now let me show you why it works that way, and why the machine case is the one that betrays you quietly.
The bet you forgot you placed
When you write a paginated sweep over a table, you place a quiet little bet on how big that table will get. The cruel part is that the bet is invisible. Offset pagination and cursor pagination look almost identical in the code. Same loop, same batch size, same upsert. One of them scales gracefully forever and the other slowly turns into a tar pit, and from across the room you cannot tell them apart.
So the offset-based feeder you shipped two years ago was not wrong. On a small table it was genuinely fine. Cheaper to write, easier to reason about, worked on the first try. The PR merged, everybody moved on, and the decision fell out of everyone’s head. Which, to be fair, is exactly what is supposed to happen to decisions that work.
Except the table kept growing, small small, in the background. And offset has one nasty property: the deeper you page, the more work the database does per page. Not the same work. More. Every single run. So the cost never stayed flat. It crept upward alongside your row count until one day it crossed the invisible line marked “available memory” and the whole thing fell over.
The job crashing today is not today’s job. It is a two-year-old decision finally presenting its bill. With interest.
And notice the shape of the casualty: a batch job, a machine sweeping the entire table. The exact case that should have been cursor from day one. Let’s break both down properly.
Offset pagination, explained like we’re gisting
Offset is “skip plenty, then grab some.” You tell the database: skip the first 40,000 rows, give me the next 10,000.
The problem is the word skip. Databases cannot teleport to row 40,001. There is no shortcut, no bookmark. To skip 40,000 rows, the database physically walks past all 40,000 of them, looks at each one, and throws it away, just to reach the ones you actually asked for.
Picture reading a novel by starting from page one every single time. Flip flip flip to where you stopped, read one page, close the book. Next session? Back to page one. By the time you finish the book, you’ve flipped through the entire thing hundreds of times. That is offset doing a full sweep. Page 1 is instant. Page 900 makes the database count to nine million before it hands you a single row.
Where it shines: a UI with an actual human inside it. When someone is browsing results and wants page 1, then page 5, then back to page 2, offset is exactly right. “Page 5, please” is easy to write, easy to hold in your head, and jumping straight to any page is instant. This is offset’s home turf, and cursor would only be a nuisance here.
What’s good about it:
- Dead simple. Your junior dev understands it in one glance.
- Jumps anywhere instantly, perfect for a UI where someone clicks straight to page 47.
- Perfectly fine on small or slow-growing tables. Genuinely. Let nobody shame you.
What bites you:
- Cost grows with depth. Deep pages get expensive, and a full sweep gets quadratically expensive as the table grows. That is the entire tar-pit-three-years-later story.
- It is a position, not an anchor. If a row ahead of you gets deleted mid-sweep, everything shifts down by one and you silently skip a row. An insert, and you process one twice. Offset counts positions, and positions move.
- The pain is invisible until it isn’t. Small table, no symptoms. Big table, sudden fire. Nothing in between to warn you.
Those last two points are exactly why offset is a terrible fit for a long-running batch sweep: the job pages deep (it visits every page) and it runs while data changes underneath it. Both of offset’s weaknesses, landing at the same time. Double wahala.
Cursor pagination, explained the same way
Cursor (also called keyset, also called “seek”) throws away the page number entirely. Instead of “skip 40,000,” it remembers the last row it saw and says: give me everything after this specific row.
You keep an actual bookmark. “Last time I stopped at the row with timestamp X and id Y. Give me what comes after that.” If there is an index on those columns, the database seeks straight to your bookmark and reads forward. No skipping. No counting to nine million. It opens the book exactly where you left it.
Every session costs the same, whether you’re on page 2 or page 900. That is the superpower.
Where it shines: batch jobs and anything that resumes. A feeder syncing a table into a search index, a nightly export, a migration, a job that got killed halfway and needs to continue from where it died. Because the bookmark is a real value from the data, you can write it down (stash it in Redis, say) and tomorrow’s run picks up exactly where today’s stopped. This is cursor’s whole reason for existing, and it is precisely what a batch job wants.
What’s good about it:
- Flat cost. Page 900 costs the same as page 2. A full sweep scales linearly, so the tar pit never forms.
- Stable under changes. The bookmark points at real values from the data, not a row count. Rows can appear and vanish elsewhere and your bookmark still means exactly what it meant. No skips, no doubles.
- Naturally resumable. “I was on page 900” becomes meaningless the moment data moves. “I stopped after row X” stays true forever.
What bites you:
- More fiddly to write. You’re threading “the last row I saw” through the loop instead of multiplying a page number.
- No jumping to page 47. Cursors only move forward from where you are, so random access is not really a thing. Which is exactly why it’s wrong for a browse-y UI and right for a march-forward batch job.
- The bookmark must be unique, or it lies to you. This is the sharp edge, so shine your eye here. If you bookmark on a timestamp alone and 5,000 rows share that timestamp, “everything after this timestamp” splits that group down the middle and you skip or double the rest. The fix is a compound bookmark: the timestamp plus a tiebreaker like the primary key. Now every row has a strict, unambiguous order, and “after this point” always means one exact spot. And your ORDER BY and your “after this” filter must name the same columns, or the whole scheme quietly falls apart.
- It leans on an index. The flat cost is real only if there’s an index on the columns you’re seeking by. No index, and your fancy cursor quietly rots back into offset’s cost. The index is doing the heavy lifting. Respect it.
So what do we actually do?
Not “cursor everything.” That is just swapping one thoughtless default for another. The rule from the top of this post is the real answer:
- A human moving through pages in a UI? Offset. Simple, correct, instant random access.
- A batch job, sync, or anything that resumes? Cursor. Flat cost, stable under writes, resumable. Offset here is the thing that OOMs in three years.
- Not sure how big the table gets? Cursor, and thank yourself later. It is the option that fails gracefully instead of falling off a cliff.
And the quieter lesson underneath all of it: the assumption is the dangerous part, not the code. When you write a paginated sweep, you are betting on a table size and a usage pattern. So say the bet out loud. Drop one comment:
// offset is fine here: human-facing UI, table capped around 50k
That one sentence is a tripwire. It turns a silent, forgotten assumption into something the next person, probably future-you, bleary and confused in front of an OOM log at 2am, can actually find and go: “Ah. The bet. The bet broke.”
The bug that gets you is never the one you’re watching on launch day. It is the reasonable little decision you made when everything was small, that you completely forgot, that was quietly compounding the whole time. Reaching for offset on a batch job is a world-class way to write one of those.
So write the comment. Leave the tripwire. Give three-years-from-now-you a fighting chance.
They’re going to be so tired.
