Why Cursor Pagination Beats Skip in a Metered Database

Offset pagination feels free until the bill arrives. In a database that charges per document read, skip(N) is not a shortcut that jumps ahead. It reads and throws away every document it steps over, and you pay for each one. This post shows the cost difference with real arithmetic, the cursor-based query that replaces offset entirely, the tie-breaker that keeps the ordering total, and the one case where plain skip is still the right call.

What skip actually does

skip(N) looks like it teleports to the N-th result, but the database has no way to know where the N-th document is without walking the first N. There is no free index into "the 380th matching row" for an arbitrary filter and sort. So the engine starts at the beginning of the ordered result set, counts past N documents, and only then begins returning rows to you.

On a fixed collection you never paginate deeply, nobody notices. The cost shows up when two things are true at once: the collection is large, and users go deep into it. On page 20 with a page size of 20, skip(380).limit(20) reads 400 documents to hand you 20. The other 380 are read, counted, and discarded before your page even starts. You did not ask for them, you cannot see them, and in a metered database you are billed for all of them.

The cost, in numbers

Put the two strategies side by side. The column that matters is the last one, because it is what shows up on the invoice at real traffic.

The skip row grows with page depth. The cursor row does not. It reads exactly the page size every time, whether you are on page 3 or page 3,000. And the ratio between them does not shrink as the site grows, it widens, because a larger collection is exactly the situation where people paginate deeper. The worst case for skip arrives precisely when you have the most traffic to lose money on.

There is a latency story that mirrors the cost story. Reading and discarding 380 documents is not just billed, it is slow, and it gets slower the deeper the page. Cursor pagination has flat latency because it does constant work per page.

The cursor query that replaces it

Instead of an offset, you carry forward the sort key of the last item on the current page, and the next page asks for rows ordered after it. There is no counting past anything. The database seeks straight into the index at the cursor position and reads the page.

// Carry the last item's sort key forward. Each page reads exactly what it
// returns, no matter how deep you are.
filter := bson.M{"published_at": bson.M{"$lt": cursor}}
opts := options.Find().
	SetSort(bson.D{{"published_at", -1}}).
	SetLimit(20)

rows, err := coll.Find(ctx, filter, opts)

The cursor is not a page number. It is the value of the sort field on the last row you showed, published_at here. The next request says "give me the newest 20 rows older than this timestamp," and because there is an index on published_at, the engine jumps to that point and reads forward. Page depth is irrelevant to the work done.

The tie-breaker that makes the order total

There is a bug hiding in that first query, and it is the one everyone hits second. If two documents share the same published_at, and in a real system they will, then "older than this timestamp" is ambiguous. A strict $lt on the timestamp can skip rows that share the cursor's timestamp, and a non-strict $lte can repeat the row you just showed. Either way the page boundary is wrong, and users see a duplicated or missing item exactly at the seam between pages.

The fix is to make the sort order total by adding a unique tie-breaker, so that no two documents ever compare equal. The _id is the natural choice, and it is even better when the id itself sorts meaningfully, as a ULID does. The cursor becomes a pair, and the comparison becomes a compound one.

// Compound cursor: (published_at, _id). No two rows compare equal, so the
// page boundary is exact. Works because _id is unique and ULIDs sort by time.
filter := bson.M{
	"$or": []bson.M{
		{"published_at": bson.M{"$lt": cur.PublishedAt}},
		{"published_at": cur.PublishedAt, "_id": bson.M{"$lt": cur.ID}},
	},
}
opts := options.Find().
	SetSort(bson.D{{"published_at", -1}, {"_id", -1}}).
	SetLimit(20)

Read it as "everything strictly older, plus, within the same timestamp, everything with a smaller id." Because _id is unique, the combined sort is total, so every row has exactly one position and the boundary between page N and page N+1 falls in exactly one place. No duplicates at the seam, no dropped rows. This is why a database that gives you sortable, unique ids pairs so well with cursor pagination. If your id is a random ULID or a derived one, you already have the tie-breaker you need. The derivation for stable, sortable ids is covered in deterministic ULIDs.

The index this query needs

Cursor pagination is only fast if the sort is backed by an index, and a compound cursor needs a compound index in the same field order as the sort. Without it, the engine falls back to scanning, and you have traded a billing problem for a latency problem.

// The sort is (published_at desc, _id desc), so the index must match.
{Keys: bson.D{{"published_at", -1}, {"_id", -1}}, Name: "published_at_id"}

The rule is that the index key order mirrors the sort key order. Get that alignment right and the query is an index seek followed by a sequential read of exactly one page. Get it wrong and the database quietly scans, which on a metered engine is the expensive thing you were trying to avoid, wearing a different mask.

What you give up, and how to work around it

Cursor pagination is not free of tradeoffs. The big one is that you cannot jump to an arbitrary page. There is no "go to page 47" because the cursor for page 47 is the last row of page 46, which you do not have unless you walked there. Numbered page links, the classic "1 2 3 ... 47" pager, do not fit the model.

For most modern interfaces this is fine, because the dominant pattern is next-page and infinite scroll, both of which only ever need the cursor for the immediately following page. When you do need to move backward, you keep a second cursor for the first row of the current page and run the mirror-image query with the comparison and sort reversed. Two cursors, forward and backward, cover next and previous, which is what real pagination UIs actually use.

What you genuinely lose is a cheap total count and therefore a cheap "page X of Y." Counting all matching rows is its own expensive query in a large collection, metered or not. The honest move is usually to drop the total from the UI, because "page 3 of 2,847" is a number nobody acts on, and showing it costs a full count on every page load.

When skip is still the right tool

None of this makes offset pagination wrong everywhere. For a small, bounded collection you will never page deeply into, an admin table of a few hundred rows, a settings list, the simplicity of skip wins and the cost is negligible because N never gets large. Reaching for a compound cursor and a matching index there is over-engineering.

The rule of thumb is about depth, not size alone. If page depth is bounded and small, skip is fine and simpler. If page depth is unbounded, meaning users can and will go deep, use a cursor, because that is exactly the regime where offset's cost grows without limit and a metered database turns that growth into a line item. Decide it per collection, based on how deep people actually go, and you will use each tool where it is genuinely the cheaper one.