Database.Middle.How would you implement pagination in SQL?

Implementing pagination in SQL lets you retrieve a subset of rows at a time—useful for showing results page-by-page in apps (like social feeds, product listings, etc.).

Here’s how to do it using different techniques:

---

✅ 1. OFFSET-FETCH / LIMIT (Simple Pagination)

📌 PostgreSQL / MySQL / SQLite:

-- Get page 3, 10 items per page
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;  -- OFFSET = (page - 1) * pageSize

📌 SQL Server:

-- Requires ORDER BY for OFFSET-FETCH
SELECT * FROM posts
ORDER BY created_at DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Page	Limit	Offset
1	10	0
2	10	10
3	10	20

⚠️ Downsides of OFFSET

• ❌ Slower for large OFFSET values (e.g., page 1000)
• ❌ Inconsistent results if data changes between queries (insert/delete)

✅ 2. Keyset Pagination (a.k.a. Seek Method)

Much faster and more stable for large or real-time datasets.

Example:

-- After last post on previous page had created_at = '2024-01-01 10:00'
SELECT * FROM posts
WHERE created_at < '2024-01-01 10:00'
ORDER BY created_at DESC
LIMIT 10;

Works best when you have an indexed column to paginate by (e.g. created_at, id).

Benefits:

• ✅ Fast even for deep pages
• ✅ More stable if data changes

Drawback:

• ❌ Can’t jump to arbitrary page number directly (only “next page”)

✅ 3. Window Function for Pagination + Total Count

If you need page results and the total number of rows:

SELECT *, COUNT(*) OVER() AS total_count
FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

This gives:

• 10 rows for current page
• A total_count column for UI (e.g., “Page 3 of 100”)

🔍 Indexing Tips

• Always add indexes on ORDER BY columns used in pagination:

CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

For keyset pagination, the column used in WHERE should be indexed

🧠 Summary

Method	Use Case	Pros	Cons
LIMIT OFFSET	Simple UI paging	Easy to implement	Slow for deep pages
Keyset (Seek)	Feeds, infinite scroll	Fast, stable	Can’t jump to arbitrary page
Window Functions	Pagination + total count	Accurate, feature-rich	Slightly slower