Pagination in SQL: Because Who Wants to Load a Million Rows at Once?

"Efficiency is doing things right; effectiveness is doing the right things." – Peter Drucker

Alright, let’s get one thing straight: if you’re not using pagination in your SQL queries, you’re basically leaving performance on the table. And no, I’m not talking about the kind of table you eat dinner on—I’m talking about database tables, where all the magic happens. Pagination is the secret sauce that keeps your apps running smoothly, even when you’re dealing with millions of rows of data. It’s like the difference between trying to drink from a firehose and sipping from a fancy espresso cup. One’s a disaster, and the other is pure elegance.

Now, before we dive into the nitty-gritty, let’s give a shoutout to Markus Winand, the genius behind the Use-the-Index-Luke website. If you haven’t checked it out yet, do yourself a favor and go read it. This guy is like the Yoda of SQL performance, and a lot of what I’m about to share comes straight from his wisdom. So, props to Markus for making us all better at databases.

Alright, let’s get into it. We’re going to talk about three ways to handle pagination in SQL: the Offset Method, the Seek Method, and the Window Functions approach. Each has its own strengths and weaknesses, and by the end of this, you’ll know exactly when to use which. Let’s flex those SQL muscles!

The Offset Method

Let’s start with the Offset Method. This is the “I don’t care about performance, just give me the data” approach. It’s simple, it’s easy, and it’s the first thing most people learn when they start messing with pagination. Here’s how it works:

SELECT *
FROM songs
ORDER BY release_date DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

What’s happening here?

• We’re selecting all columns from the songs table.
• We’re ordering the songs by their release date, so the newest ones show up first.
• We’re skipping the first 10 rows (OFFSET 10 ROWS).
• Then, we’re fetching the next 10 rows (FETCH NEXT 10 ROWS ONLY).

Result:

• You get songs 11 to 20. Simple, right?

Why it’s cool:

• It’s dead simple to write and understand.
• Perfect for small datasets or when you’re just prototyping.

Why it’s not so cool:

• It’s O(n) complexity, meaning the database has to scan through all the rows up to the offset before it can start returning results. If you’re dealing with a million rows, this can get painfully slow.
• It’s not stable—if new rows are added, the pagination can get messed up because the offsets shift.

The Seek Method

Now, if you’re ready to level up, let’s talk about the Seek Method. This is the “I’m too smart to waste time counting rows” approach. Instead of skipping rows, you tell the database exactly where to start. It’s like saying, “Hey, give me the next 10 songs after this specific song.” This method is a game-changer for performance, especially with large datasets.

Here’s how it works:

SELECT *
FROM songs
WHERE (release_date, song_id) < ('2023-10-01', 12345)
ORDER BY release_date DESC, song_id DESC
FETCH FIRST 10 ROWS ONLY;

What’s happening here?

• We’re grabbing all the columns from the songs table.
• We’re using a WHERE clause to find songs released before a specific date and song ID.
• We’re sorting the songs by release date and song ID, so the newest ones show up first.
• We’re fetching only the first 10 rows that match the condition.

Result:

• You get the next 10 songs after the song with the release date 2023-10-01 and song ID 12345.

Why it’s cool:

• It’s O(log n) complexity because the database can use an index to skip rows. This means it’s blazing fast, even with millions of rows.
• It’s stable—if new rows are added, the pagination doesn’t get messed up because you’re using actual values, not offsets.

Why it’s not so cool:

• It’s a bit more complicated to set up because you need to know the last value from the previous page.
• You need a deterministic sort order, which means you have to include a unique column (like song_id) in your ORDER BY clause to avoid ambiguity.

Window Functions

If you really want to flex your SQL skills, you can use Window Functions. These are like the secret sauce of SQL—they let you do all sorts of cool stuff without changing the actual data. For pagination, you can use the ROW_NUMBER() function to assign a unique number to each row and then filter based on that.

Here’s how it looks:

SELECT *
FROM (
  SELECT songs.*, 
         ROW_NUMBER() OVER (ORDER BY release_date DESC, song_id DESC) AS rn
  FROM songs
) AS tmp
WHERE rn BETWEEN 11 AND 20
ORDER BY release_date DESC, song_id DESC;

What’s happening here?

• We’re using the ROW_NUMBER() function to assign a unique number to each row based on the release date and song ID.
• We’re wrapping this in a subquery (tmp) and then filtering to get rows 11 to 20.
• Finally, we’re ordering the results again to make sure they’re in the right order.

Result:

• You get songs 11 to 20, just like in the offset method, but with the added power of window functions.

Why it’s cool:

• It’s super flexible and powerful.
• Works well in databases like PostgreSQL, Oracle, and SQL Server.
• It’s pipelined, meaning the database can stop processing rows as soon as it finds the ones you need.

Why it’s not so cool:

• Not all databases support this optimization, so it might not be as efficient everywhere.
• It’s more complex to write and understand, so it’s not ideal for beginners.

Which Method Should You Use?

• Offset Method: Great for small datasets or when you need simplicity. But if you’re dealing with a lot of data, this method will slow you down.
• Seek Method: Perfect for large datasets where speed is important. It’s stable, fast, and efficient.
• Window Functions: Awesome for advanced queries and analytical tasks. Use this when you need maximum flexibility and your database supports it.

Final Thoughts

Pagination is one of those things that seems simple at first but has a lot of depth once you start digging into it. Whether you’re building a music app, a game leaderboard, or just exploring data, knowing how to paginate efficiently can save you a ton of time and headaches.

• If you’re just starting out, the Offset Method is your friend. It’s easy to use and gets the job done.
• If you’re dealing with large datasets and need speed, the Seek Method is the way to go. It’s fast, stable, and efficient.
• And if you’re ready to flex your SQL skills, Window Functions are the ultimate tool for advanced pagination and analytics.

So go ahead, try out these methods, and see which one works best for your project. And remember, the next time someone complains about slow loading times, you can just casually drop, “Oh, you should try using the seek method for pagination.” 😄

And again, big thanks to Markus Winand for sharing his knowledge on Use-the-Index-Luke. If you want to dive deeper into SQL performance, his website is a goldmine of information.

Cheers 🍺!