Right, let’s talk about pagination. You’ve probably done the OFFSET 100 LIMIT 10 dance a thousand times. It’s the default. It’s also a performance nightmare waiting to happen, and I’m going to show you exactly why. The problem with OFFSET is that it’s a dumb skip. The database has to read the first 100 rows, count them, and then throw them away to give you the 10 you actually want. It’s like asking a librarian to count out the first 100 books in the library, then hand you the 101st. It gets slower and more expensive the deeper you paginate. It’s absurd.

Keyset pagination (sometimes called “cursor pagination”) is the scalable, professional way to do this. Instead of skipping rows by counting, you remember your place. You use a unique, sequential column (or set of columns) from the last row you fetched as an anchor point for the next query.

The Core Concept: Remembering Your Place

Imagine you’re paginating through a posts table, ordered by its creation date. With OFFSET, page 5 is OFFSET 40 LIMIT 10. With keyset pagination, you don’t care about the number of rows you’ve skipped. You only care about the last value you saw. If the last post on your current page had a created_at timestamp of '2023-10-05 08:00:00', you use that to get the next page.

The magic is in the WHERE clause. You ask for the next 10 rows where the cursor column is greater than the last value you saw.

-- First page: get the most recent 10 posts
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 10;

-- Let's say the last row on the first page has created_at = '2023-10-05 08:00:00'

-- Second page: use that value to get the *next* 10
SELECT id, title, created_at
FROM posts
WHERE created_at < '2023-10-05 08:00:00'
ORDER BY created_at DESC
LIMIT 10;

See what happened? The WHERE created_at < ... clause efficiently excludes all the rows we’ve already seen. The database can use an index on created_at to jump directly to the correct starting position. No counting, no throwing away rows. It’s brutally efficient.

Why Your Cursor Must Be Unique and Sequential

Here’s the catch that trips everyone up: what if two rows have the exact same created_at value? Your ORDER BY becomes ambiguous. One row might appear on page 1 in one query and page 2 in the next, or vanish entirely. It’s a mess.

The solution is to use a tiebreaker. You add a second column to your ordering—almost always the primary key (id), which is guaranteed to be unique and sequential. This makes your cursor unambiguous and stable.

-- First page
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 10;

-- Let's say the last row has (created_at = '2023-10-05 08:00:00', id = 456)

-- Second page: now we have a two-part WHERE clause
SELECT id, title, created_at
FROM posts
WHERE created_at < '2023-10-05 08:00:00'
   OR (created_at = '2023-10-05 08:00:00' AND id < 456)
ORDER BY created_at DESC, id DESC
LIMIT 10;

Yes, the WHERE clause gets uglier. That’s the price of correctness. This logic says: “Get all posts with a newer timestamp, OR, for posts with the same timestamp, get the ones with a lower ID than my last one.” This handles the edge case perfectly.

The Great Strength (and Weakness) of Keyset Pagination

Its biggest strength is also its main UX limitation: it’s not random access. You can’t jump to page 15. You can only go to the next page or the previous page (by reversing the sort order and the comparison operators). This is why it’s the secret sauce for infinite scroll feeds in every major app—they don’t need random access, they just need the “next” page, fast.

For a “jump to page” UI, you’re often stuck with OFFSET. My advice? If you must offer random access, use OFFSET but set a hard maximum—maybe 20 pages deep—to keep the performance hit within reason. Don’t let someone try to OFFSET 1000000.

Best Practices and Final Thoughts

  1. Index, Index, Index: Your ORDER BY clause must be backed by an index. For our example, you’d need an index on (created_at DESC, id DESC). Without it, you lose all the performance benefits.
  2. The Cursor is Opaque: Don’t try to parse the created_at and id values on the client. Treat the entire cursor as a black blob. In practice, you’ll base64-encode the values and pass them back and forth as a single string token.
  3. It’s for Read-Heavy, Append-Only Data: This pattern shines on data that’s written once and read often (like posts, logs, events). If you’re paginating a volatile dataset where rows are constantly being inserted in the middle, the semantics can get weird, but it’s still usually better than OFFSET.

Embrace keyset pagination. It requires a bit more thought upfront, but your database will thank you with faster queries and lower load, especially under heavy traffic. It’s one of the clearest signs that someone knows their way around a database beyond the basics.