In the endless quest to optimize my queries, one of the things that I had previously overlooked turned out to be pagination. It is easy to focus on indexes, joins, caching, and query plans, while treating pagination as just a frontend detail. But once tables grow and traffic increases, pagination choices can have a real impact on database load and response times.
A lot of APIs begin with the easiest version:
LIMIT 20 OFFSET 0
It works, it is readable, and it is often fine early on. But once datasets grow, the tradeoffs become obvious. Here are some pagination patterns to consider when basic LIMIT / OFFSET starts showing its limits.
1. Offset Pagination
Offset pagination works by skipping a fixed number of rows and returning the next batch. It maps directly to page numbers, which makes it intuitive for many traditional UIs.
SELECT *
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;
Pros
- Very simple to implement
- Supports page numbers naturally
- Easy to understand and debug
Cons
- Slow for large offsets (DB still scans skipped rows)
- Performance degrades as page number increases
- Can return inconsistent results if data changes during paging
2. Cursor Pagination
Cursor pagination uses a reference point (usually an indexed column) instead of skipping rows. Each request continues from the last seen record.
SELECT *
FROM posts
WHERE id < 8452
ORDER BY id DESC
LIMIT 20;
Pros
- Fast even on large datasets
- Uses indexes efficiently
- Stable under inserts/deletes
- Ideal for infinite scroll
Cons
- No natural page numbers
- Slightly more complex API logic
- Requires consistent ordering field(s)
3. Composite Cursor Pagination
Composite cursor pagination extends cursor pagination by using multiple columns to ensure uniqueness in ordering. This is useful when a single column is not sufficient for stable sorting.
SELECT *
FROM posts
WHERE (created_at, id) < ('2026-04-29 10:00:00', 8452)
ORDER BY created_at DESC, id DESC
LIMIT 20;
Pros
- Avoids duplicates when ordering by timestamps
- More accurate pagination in real-world datasets
- Still index-friendly when designed properly
Cons
- More complex query structure
- Requires composite indexes for best performance
- Harder to reason about manually
4. Has-More Pagination
Has-more pagination fetches one extra record to determine if more data exists. It avoids the need for expensive total counts.
SELECT *
FROM posts
ORDER BY id DESC
LIMIT 21;
Pros
- Avoids expensive COUNT queries
- Simple and fast
- Works well for infinite scroll UX
Cons
- Cannot show exact total pages
- Slightly awkward API response shaping
- Requires client-side handling of "extra row"
Additional Tip: Approximate Count
Exact counts require scanning or aggregating all matching rows, which can be expensive on large datasets. In many cases, an estimated count from the query planner is sufficient for UI purposes.
EXPLAIN
SELECT *
FROM posts
WHERE status = 'published';
Example output:
Seq Scan on posts (cost=0.00..1820.00 rows=12450 ...)
Pros
- Very fast compared to COUNT(*)
- Good enough for “about X results” UI
- Reduces load on large datasets
Cons
- Not exact
- Depends on up-to-date table statistics
- Can be misleading if stats are stale
