SQL Paging Strategies: How to Load Only What You Need

So, you’ve got a database full of emails, and you need to load them for your users.

But here’s the thing: you do not want to load a million emails at once.

Unless you want your database to melt like a popsicle in the sun, you need paging.

Paging is just a fancy way of saying, “Give me a few rows at a time, and I’ll ask for more when I need them.” This way, your app stays snappy, your database doesn’t cry itself to sleep, and your users don’t get hit with a tsunami of data they didn’t ask for.

So, how do we do paging in SQL?

Let’s look at simple, medium, and advanced methods in MSSQL, MySQL, and PostgreSQL.

🥤 Simple Paging (LIMIT & OFFSET)

This is the “I just need something quick” method.

It’s simple, works everywhere, and is perfect for small datasets.

Here’s how it works:

• LIMIT tells SQL how many rows to return.
• OFFSET tells SQL where to start.

MySQL & PostgreSQL:

1
2
3

SELECT * FROM emails
ORDER BY created_at DESC
LIMIT 10 OFFSET 0; -- First page (10 emails)

1
2
3

SELECT * FROM emails
ORDER BY created_at DESC
LIMIT 10 OFFSET 10; -- Second page (next 10 emails)

SQL Server (MSSQL):

1
2
3

SELECT * FROM emails
ORDER BY created_at DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY; -- First page

1
2
3

SELECT * FROM emails
ORDER BY created_at DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; -- Second page

📌 Downside: The further you go, the slower it gets!

Why?

Because the database still reads all the previous rows before returning your page.

At page 100, that’s 1000 rows read just to skip them.

Yikes.

🍔 Medium Paging (Using a WHERE Condition)

If you’re fetching data based on a column like id or created_at, you can use a WHERE condition to make things faster.

Instead of OFFSET, just remember the last item from the previous page and use that to filter the next batch.

MySQL & PostgreSQL:

1
2
3
4

SELECT * FROM emails
WHERE created_at < '2024-01-01 12:00:00'
ORDER BY created_at DESC
LIMIT 10;

MSSQL:

1
2
3

SELECT TOP 10 * FROM emails
WHERE created_at < '2024-01-01 12:00:00'
ORDER BY created_at DESC;

📌 Why this is better: It doesn’t need to scan and skip a ton of rows.

It just finds where to start and grabs the next batch.

👎 Downside: This only works if you’re paging based on a column that keeps increasing (like an id or a timestamp).

If you need flexible pagination, keep reading.

🚀 Advanced Paging (Keyset Pagination)

Now we’re talking serious performance.

Keyset pagination is the king of paging when performance matters.

It avoids OFFSET entirely and uses indexed columns to fetch the next batch efficiently.

MySQL & PostgreSQL:

1
2
3
4

SELECT * FROM emails
WHERE id > 1000
ORDER BY id ASC
LIMIT 10;

MSSQL:

1
2
3

SELECT TOP 10 * FROM emails
WHERE id > 1000
ORDER BY id ASC;

📌 Why this is the best: It only fetches the rows you need, doesn’t scan unnecessary data, and performs well even with millions of rows.

👎 Downside: You need an indexed column (like id or created_at) that lets you filter efficiently.

🧐 Which One Should You Use?

If you’re just starting out, use LIMIT OFFSET.

If performance starts getting bad, switch to a WHERE condition or keyset pagination.

If you expect millions of records, just go straight for keyset pagination and save yourself some future headaches.

🎯 Key Ideas

🔗 References

• MySQL LIMIT Documentation
• PostgreSQL LIMIT Documentation
• MSSQL OFFSET-FETCH Documentation