Right, let’s talk about LIMIT and OFFSET. You’re going to use them constantly, and you’re going to hate them eventually. They are the duct tape and baling wire of pagination—it gets the job done in a pinch, but you wouldn’t want to build a skyscraper with it. They feel intuitive: “just give me 10 results, but skip the first 20.” And for small datasets, that’s perfectly fine. But the moment your data grows, this approach reveals its fundamental, almost comical, flaw.

Think about what OFFSET 1000 actually asks the database to do. It doesn’t just magically jump to the 1001st record. No, the database has to find the first 1000+LIMIT records, sort them (if there’s an ORDER BY), and then throw the first 1000 away before finally handing you your precious 10. It’s like telling a chef to make 1010 sandwiches, line them all up, and then toss the first 1000 into the bin just so you can have the last 10. The cost of producing those discarded rows is real, and it gets worse the deeper you paginate.

Here’s the basic, harmless-looking syntax:

-- Get the first page of 10 users
SELECT id, username, created_at
FROM users
ORDER BY created_at DESC
LIMIT 10;

-- Get the *next* page (page 3, to be precise)
SELECT id, username, created_at
FROM users
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

This works. For now. But run that second query on a table with ten million users, and you’ll hear your database server groan. It must locate the first 20 records in the sorted order (which hopefully uses an index!) and then scan past them to get to your 10.

The Cursor-Based Pagination Alternative

So, how do we build pagination that doesn’t fall over? We use a “cursor.” Instead of telling the database how many rows to skip, we tell it where we left off. This is often called keyset pagination.

The trick is to use a WHERE clause that references the value of a unique, sequential column (or set of columns) from the last record you fetched. Your ORDER BY must be on this same column to make it efficient.

Let’s use our users example. Instead of using OFFSET 20 for page 3, we remember the created_at timestamp (and if that’s not unique, the id) of the last user on page 2.

-- Page 1
SELECT id, username, created_at
FROM users
ORDER BY created_at DESC, id DESC
LIMIT 10;

-- Assume the last record on Page 1 had: created_at = '2023-10-25 14:30:00', id = 456

-- Page 2: We ask for records that come *before* that last record.
SELECT id, username, created_at
FROM users
WHERE (created_at, id) < ('2023-10-25 14:30:00', 456)
ORDER BY created_at DESC, id DESC
LIMIT 10;

Why is this so much better? The database can now use an index on (created_at DESC, id DESC) to instantly find the starting point. It doesn’t have to count and discard a thousand rows; it just grabs the next 10. The performance is constant, whether you’re on page 1 or page 10,000. This is how every major API (Twitter, Facebook, etc.) handles pagination because it’s the only thing that scales.

The Inevitable Rough Edges

Now, cursor pagination isn’t perfect. The big trade-off is that you lose random page access. You can’t jump directly to page 17 anymore because you don’t have the cursor for the end of page 16. For a user scrolling through an infinite feed, this is fine. For a website where a user might want to jump deep into search results, it’s a non-starter. This is the one legitimate reason OFFSET still exists.

The other edge case is dealing with new data being inserted while you’re paginating. With OFFSET, if 5 new users are added between your first and second query, your page 2 will show 5 duplicates and miss 5 at the end. It’s a mess. With cursor pagination, new entries added after your current cursor (e.g., newer users) won’t affect your pagination at all. Your result set is stable for the duration of your “session,” which is almost always what you want.

Best Practices and Final Thoughts

My advice is this: use OFFSET only for truly small, static datasets or for administrative interfaces where users need random page access and the tables will never be large enough for it to matter. For anything that resembles a user-facing feed, list, or search result—anything that might grow—implement cursor-based pagination from the start.

Always remember: OFFSET is about skipping rows, while a cursor is about skipping values. The former forces the database to do wasteful work. The latter lets it use its indexes the way they were designed. Choose the tool that doesn’t make your database cook a thousand sandwiches just to throw them in the trash.