Pagination #
Almost every application has a list page with pagination — order lists, activity logs, product lists, transaction histories. The most common pattern used is LIMIT and OFFSET: take 20 rows, skip some rows. On page one everything feels fast. Page 5 is still fine. But on page 500, when OFFSET is already 10,000, the query suddenly feels heavy — and the most diligent scrollers or the developers who most often export data to the last page are the first to feel it. The problem isn’t LIMIT — LIMIT is efficient. The problem is OFFSET, whose real cost in the database is far higher than it looks. This article covers why OFFSET is fundamentally problematic, two more scalable pagination approaches, and how to design pagination queries that stay fast even with millions of rows.
Why OFFSET Gets Slower As It Grows #
Most developers assume OFFSET 10000 LIMIT 20 means “jump to row 10,000, take 20 rows”. This is a very common misconception. What actually happens is far more expensive:
-- Page 500 pagination query (20 items per page)
SELECT id, title, price, created_at
FROM products
WHERE category_id = 5
ORDER BY created_at DESC
LIMIT 20 OFFSET 9980;
-- What happens in the database:
-- 1. Scan the index (category_id, created_at) from the start
-- 2. Read and process the first 10,000 rows matching the WHERE
-- 3. Discard the first 9,980 rows (OFFSET)
-- 4. Return the remaining 20 rows (LIMIT)
-- The database can't "jump" to row 9,981.
-- It must pass through every preceding row.
Visualizing the growing cost of OFFSET:
OFFSET query costs on the products table (500,000 rows):
──────────────────────────────────────────────────────────────
OFFSET 0 LIMIT 20 → reads 20 rows → ~1ms
OFFSET 200 LIMIT 20 → reads 220 rows → ~2ms
OFFSET 2000 LIMIT 20 → reads 2,020 rows → ~8ms
OFFSET 10000 LIMIT 20 → reads 10,020 rows → ~35ms
OFFSET 50000 LIMIT 20 → reads 50,020 rows → ~180ms
OFFSET 200000 LIMIT 20 → reads 200,020 rows → ~700ms
──────────────────────────────────────────────────────────────
Every "next page" costs more.
Page 500 consumes 700× more resources than page 1.
Cost = O(offset + limit), not O(limit).
EXPLAIN output for a large OFFSET also reveals the problem:
EXPLAIN SELECT id, title, price, created_at
FROM products
WHERE category_id = 5
ORDER BY created_at DESC
LIMIT 20 OFFSET 9980;
-- +-------+----------------------------+-------+--------------------+
-- | type | key | rows | Extra |
-- +-------+----------------------------+-------+--------------------+
-- | range | idx_products_cat_created | 10000 | Using index cond. |
-- +-------+----------------------------+-------+--------------------+
-- rows = 10,000 → the database reads 10,000 rows to return 20
Offset-Based Pagination: Strengths and Fundamental Weaknesses #
Even though OFFSET has performance problems on deep pages, it has strengths that keep it relevant for certain contexts.
Offset-Based Pagination (LIMIT + OFFSET):
──────────────────────────────────────────────────────────────
Strengths:
✓ Can jump to any page at random
("Go to page 47" directly without sequential navigation)
✓ Total page count can be calculated (if a COUNT is available)
✓ Familiar to users — "Page 1 of 23"
✓ Easy to implement in both backend and frontend
✓ Supports URL sync (?page=5)
Weaknesses:
✗ Cost O(offset + limit) — deeper pages get slower
✗ Data can shift on inserts/deletes
(a user on page 2 may have moved to page 1
when new data is added)
✗ Not suitable for frequently changing data
✗ Not scalable for deep pagination (pages > 100)
When it's still appropriate:
✓ Small datasets (< 10,000 rows)
✓ Admin pages with filters that significantly limit results
✓ Users truly need access to arbitrary pages ("go to page 47")
✓ Static or rarely changing data
──────────────────────────────────────────────────────────────
Mitigating OFFSET Problems with Keyed Pagination (Deferred Join) #
There’s a technique for making offset-based pagination more efficient without drastically changing the UX: instead of reading all columns at once, use a subquery or JOIN to read only the IDs first (via the index), then fetch the full rows based on those IDs.
-- ANTI-PATTERN: reading all columns directly with a large OFFSET
SELECT id, title, description, price, stock, image_url, created_at
FROM products
WHERE category_id = 5
ORDER BY created_at DESC
LIMIT 20 OFFSET 9980;
-- → Reads 10,000 full rows (including all columns) then discards 9,980
-- CORRECT: deferred join — get IDs first via the index, fetch full data later
SELECT p.id, p.title, p.description, p.price, p.stock, p.image_url, p.created_at
FROM (
SELECT id
FROM products
WHERE category_id = 5
ORDER BY created_at DESC
LIMIT 20 OFFSET 9980
) AS paged
JOIN products p ON paged.id = p.id;
-- → Subquery: reads 10,000 IDs only from the index (far smaller)
-- → JOIN: fetches only 20 full rows based on the 20 selected IDs
-- → Total I/O far smaller if rows have many large columns
A deferred join doesn’t eliminate the fundamental OFFSET problem (still O(offset)), but it significantly reduces the data volume read on tables with many or large columns.
Cursor-Based Pagination: Scalable at Every Depth #
Cursor-based pagination (also called keyed pagination or seek pagination) is an approach that replaces OFFSET with a value-based filter from the last seen row. Instead of “skip 9,980 rows”, the database directly continues from the exact point in the index.
How It Works #
-- First page: no cursor
SELECT id, title, price, created_at
FROM products
WHERE category_id = 5
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- → Returns the first 20 rows
-- → Note the last values: created_at = '2026-01-15 10:30:00', id = 8421
-- Next page: use the last values as the cursor
SELECT id, title, price, created_at
FROM products
WHERE category_id = 5
AND (created_at < '2026-01-15 10:30:00'
OR (created_at = '2026-01-15 10:30:00' AND id < 8421))
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- → The database directly seeks to the point (created_at='2026-01-15 10:30:00', id=8421)
-- in the index (category_id, created_at DESC, id DESC)
-- → No OFFSET, no "read then discard"
-- → Cost stays O(limit), whatever the "page"
Why is id included in the cursor and ORDER BY? Because created_at can be equal for several rows — without a tiebreaker, pagination can skip or duplicate rows at page boundaries.
Cursor-based vs offset-based cost comparison:
──────────────────────────────────────────────────────────────
Table products: 500,000 rows, filter category_id = 5 → 80,000 rows
"Page 500" (items 9,981 to 10,000):
Offset-based:
→ OFFSET 9980 LIMIT 20
→ Reads 10,000 rows, discards 9,980
→ ~180ms
Cursor-based:
→ WHERE created_at < [cursor] AND id < [cursor_id]
→ The database seeks directly to the cursor position in the index
→ Reads exactly 20 rows
→ ~1ms
Difference: offset-based is 180× slower at this depth.
At page 5,000: the difference can be 1,000× or more.
──────────────────────────────────────────────────────────────
The Index Needed for Cursor-Based Pagination #
Cursor-based pagination is only efficient if the columns used as the cursor are properly indexed, covering all WHERE filters and the cursor columns:
-- Pagination query with a status filter and a created_at + id cursor
SELECT id, title, price, status, created_at
FROM orders
WHERE user_id = 42
AND status = 'paid'
AND (created_at < ? OR (created_at = ? AND id < ?))
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- The index needed:
CREATE INDEX idx_orders_cursor
ON orders (user_id, status, created_at DESC, id DESC);
-- → All WHERE + ORDER BY/cursor columns covered in one index
-- → The query can use the index to seek directly to the cursor position
Complete Implementation: Cursor-Based Pagination in Go #
Here’s a complete cursor-based pagination implementation, including cursor encoding, decoding, and the response format commonly used in REST APIs:
// The cursor stores two values: created_at and id of the last row
type PageCursor struct {
CreatedAt time.Time `json:"created_at"`
ID int64 `json:"id"`
}
// Encode the cursor to a Base64 string to send to the client
func encodeCursor(c PageCursor) string {
data, _ := json.Marshal(c)
return base64.StdEncoding.EncodeToString(data)
}
// Decode the cursor from the Base64 string received from the client
func decodeCursor(s string) (*PageCursor, error) {
data, err := base64.StdEncoding.DecodeString(s)
if err != nil {
return nil, err
}
var c PageCursor
if err := json.Unmarshal(data, &c); err != nil {
return nil, err
}
return &c, nil
}
type Order struct {
ID int64
UserID int64
Total float64
Status string
CreatedAt time.Time
}
type OrderPage struct {
Data []Order `json:"data"`
NextCursor string `json:"next_cursor,omitempty"` // empty on the last page
HasMore bool `json:"has_more"`
}
func GetUserOrders(ctx context.Context, userID int64, status string, cursorStr string, limit int) (*OrderPage, error) {
var args []interface{}
var query string
if cursorStr == "" {
// First page — no cursor
query = `
SELECT id, user_id, total, status, created_at
FROM orders
WHERE user_id = ? AND status = ?
ORDER BY created_at DESC, id DESC
LIMIT ?
`
args = []interface{}{userID, status, limit + 1}
} else {
// Next page — decode and use the cursor
cursor, err := decodeCursor(cursorStr)
if err != nil {
return nil, fmt.Errorf("invalid cursor: %w", err)
}
query = `
SELECT id, user_id, total, status, created_at
FROM orders
WHERE user_id = ?
AND status = ?
AND (created_at < ? OR (created_at = ? AND id < ?))
ORDER BY created_at DESC, id DESC
LIMIT ?
`
args = []interface{}{
userID, status,
cursor.CreatedAt, cursor.CreatedAt, cursor.ID,
limit + 1,
}
}
rows, err := db.QueryContext(ctx, query, args...)
if err != nil {
return nil, err
}
defer rows.Close()
var orders []Order
for rows.Next() {
var o Order
rows.Scan(&o.ID, &o.UserID, &o.Total, &o.Status, &o.CreatedAt)
orders = append(orders, o)
}
// Check whether there's a next page (fetch limit+1, check the remainder)
hasMore := len(orders) > limit
if hasMore {
orders = orders[:limit]
}
// Build the next page cursor from the last row
var nextCursor string
if hasMore {
last := orders[len(orders)-1]
nextCursor = encodeCursor(PageCursor{
CreatedAt: last.CreatedAt,
ID: last.ID,
})
}
return &OrderPage{
Data: orders,
NextCursor: nextCursor,
HasMore: hasMore,
}, nil
}
The API response:
{
"data": [...],
"next_cursor": "eyJjcm...yMX0=",
"has_more": true
}
The client sends next_cursor as a query parameter in the next request: GET /api/orders?cursor=eyJj...
A Base64-encoded cursor isn’t encrypted — users can decode it and see its values. If the cursor contains sensitive information (e.g. internal IDs), consider encrypting the cursor with HMAC or AES before sending it to the client.
Pagination with Varying Filters and Sorts #
One of the challenges of cursor-based pagination is when users can choose different sort columns. The cursor must contain values from the column currently being used as the sort basis.
-- Scenario: users can sort by price or created_at
-- Sort by price:
SELECT id, title, price, created_at
FROM products
WHERE category_id = ?
AND (price > ? OR (price = ? AND id > ?)) -- cursor: price + id
ORDER BY price ASC, id ASC
LIMIT 20;
-- Cursor: {price: 149900, id: 5821}
-- Sort by created_at:
SELECT id, title, price, created_at
FROM products
WHERE category_id = ?
AND (created_at < ? OR (created_at = ? AND id < ?)) -- cursor: created_at + id
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Cursor: {created_at: "2026-01-15T10:30:00Z", id: 8421}
Each sort combination needs a different index. This is cursor-based’s trade-off: more sort columns = more indexes to create and manage.
Indexes needed for each sort option:
──────────────────────────────────────────────────────────────
Sort by created_at DESC:
→ INDEX (category_id, created_at DESC, id DESC)
Sort by price ASC:
→ INDEX (category_id, price ASC, id ASC)
Sort by name ASC:
→ INDEX (category_id, name ASC, id ASC)
If there are too many sort options:
→ Consider offset-based for the early pages (1-10)
→ Limit sort options to the 2-3 most used
→ Or accept that cursor-based doesn't fit this use case
──────────────────────────────────────────────────────────────
Pagination in Admin Panels: Specific Guidance #
Admin panels have different characteristics from public endpoints — less frequent access, more complex filters, and a more frequent need to access specific pages. This affects the pagination strategy choice.
The Often Unnoticed Double COUNT Problem #
Modern admin panels using libraries like React Admin, Ant Design Table, or DataTables often silently perform two COUNT queries:
An admin order list request (unknowingly executing):
──────────────────────────────────────────────────────────────
1. SELECT COUNT(*) FROM orders WHERE status = 'paid'
→ To display "12,483 records"
→ Executed by the ORM/pagination library
2. SELECT COUNT(*) FROM orders WHERE status = 'paid'
→ To validate whether the requested page number is valid
→ Executed again by the validation layer
3. SELECT * FROM orders WHERE status = 'paid'
ORDER BY created_at DESC LIMIT 20 OFFSET 40
→ This one finally fetches the data
Three queries for one page — two of them expensive COUNTs.
──────────────────────────────────────────────────────────────
Solutions for admin panels:
// Avoid double COUNT — count once, store it in the response
type AdminOrderListResponse struct {
Data []Order `json:"data"`
TotalCount *int64 `json:"total_count,omitempty"` // optional
HasNextPage bool `json:"has_next_page"`
Page int `json:"page"`
PerPage int `json:"per_page"`
}
// Strategy: count the total only on page 1, cache it for subsequent pages
// Or: drop the total count entirely, use LIMIT+1
func GetAdminOrders(page, perPage int, status string) (*AdminOrderListResponse, error) {
offset := (page - 1) * perPage
// Fetch the data with one extra row to detect has_next_page
rows, _ := db.Query(`
SELECT id, user_id, total, status, created_at
FROM orders
WHERE status = ?
ORDER BY created_at DESC
LIMIT ? OFFSET ?
`, status, perPage+1, offset)
var orders []Order
for rows.Next() {
var o Order
rows.Scan(&o.ID, &o.UserID, &o.Total, &o.Status, &o.CreatedAt)
orders = append(orders, o)
}
hasNextPage := len(orders) > perPage
if hasNextPage {
orders = orders[:perPage]
}
return &AdminOrderListResponse{
Data: orders,
HasNextPage: hasNextPage,
Page: page,
PerPage: perPage,
// TotalCount left empty — no COUNT needed
}, nil
}
Limit OFFSET Depth in Admin Panels #
If OFFSET can’t be avoided (because of the need for arbitrary page access), limit the maximum depth:
// Reject requests with too-large OFFSETs
const MaxOffset = 10000
func validatePaginationParams(page, perPage int) error {
offset := (page - 1) * perPage
if offset > MaxOffset {
return fmt.Errorf("page too deep — maximum page %d for %d items per page",
MaxOffset/perPage+1, perPage)
}
if perPage > 100 {
return fmt.Errorf("maximum %d items per page", 100)
}
return nil
}
Offset-Based vs Cursor-Based Comparison #
Comparison matrix of the two pagination methods:
──────────────────────────────────────────────────────────────────────
Aspect │ Offset-Based │ Cursor-Based
──────────────────────────────────────────────────────────────────────
Early page performance │ Fast │ Fast
Deep page performance │ Gets slower │ Stays fast (O(limit))
Arbitrary page access │ ✓ Possible │ ✗ Not possible
Stability when data │ ✗ Can shift/ │ ✓ Stable
changes │ duplicate │
Backend implementation │ Easy │ Medium
Frontend implementation │ Easy │ Medium (prev/next only)
Bookmarkable URLs │ ✓ (?page=5) │ ✗ (cursors aren't permanent)
Fits infinite │ ✗ No │ ✓ Very well
scroll / load more │ │
Index requirements │ Standard │ Specific composite indexes
──────────────────────────────────────────────────────────────────────
Choosing guidance:
Use Offset-Based if:
✓ Small datasets or pagination no deeper than ~20 pages
✓ Users need direct access to specific pages
✓ Bookmarkable or shareable URLs
✓ Relatively static data
Use Cursor-Based if:
✓ Large datasets (> 100,000 rows)
✓ Feeds, activity streams, or infinite scroll
✓ Frequently changing data (active inserts/deletes)
✓ Unbounded page depth
✓ Mobile or real-time APIs needing consistency
Anti-Patterns to Avoid #
-- ✗ Anti-pattern 1: very large OFFSETs without limits
SELECT * FROM logs ORDER BY created_at DESC LIMIT 20 OFFSET 500000;
-- → Reads 500,020 rows, discards 500,000, returns 20
-- ✓ Solution: cursor-based, or limit the maximum OFFSET in the app
-- ✗ Anti-pattern 2: no ORDER BY in pagination
SELECT * FROM products WHERE category_id = 5 LIMIT 20 OFFSET 40;
-- → Non-deterministic order — the same row can appear on different pages
-- ✓ Solution: always include an ORDER BY with a unique column (e.g. id)
-- ✗ Anti-pattern 3: ORDER BY on a column without the right index
SELECT * FROM orders WHERE user_id = 42 ORDER BY total DESC LIMIT 20;
-- If no index on (user_id, total): filesort over all of user 42's rows
-- ✓ Solution: CREATE INDEX idx_orders_user_total ON orders (user_id, total DESC)
-- ✗ Anti-pattern 4: cursors from columns that aren't unique or monotonic
-- For example, a cursor only from a status column (many duplicates)
WHERE status > ? ORDER BY status
-- → Many rows with the same status → rows skipped or duplicated
-- ✓ Solution: always use a unique tiebreaker (id) as the secondary cursor
-- ✗ Anti-pattern 5: per_page without a maximum limit
GET /api/products?per_page=100000
-- → A user requests all data at once via pagination
-- → Can drain memory and database connections
-- ✓ Solution: cap per_page on the server (e.g. maximum 100), ignore higher values
Pagination Design Checklist #
WHEN IMPLEMENTING NEW PAGINATION:
□ Is there a deterministic ORDER BY (including a unique column)?
□ Are all ORDER BY columns indexed together with the WHERE columns?
□ Is there a maximum per_page limit on the server?
□ Is OFFSET limited or replaced with cursor-based?
□ Is a total count needed? Can LIMIT+1 be used instead?
□ Verified with EXPLAIN (no filesort)?
FOR CURSOR-BASED:
□ Does the cursor contain all the columns needed for the seek?
□ Is there a unique tiebreaker (id) in the cursor?
□ Is the cursor encoded (Base64 or encrypted) before sending to the client?
□ Is there validation for invalid or expired cursors?
□ Does the index cover (WHERE columns, ORDER BY/cursor columns)?
FOR OFFSET-BASED:
□ Small dataset or reasonably limited pages?
□ Is there a maximum OFFSET limit on the server?
□ Is a deferred join used if rows have many large columns?
□ If there's a COUNT: can it be cached or use an approximate count?
Summary #
- OFFSET isn’t “jump to row N” — the database must read and discard all rows before the OFFSET. Its cost is O(offset + limit), not O(limit). The deeper the page, the slower it gets.
- Cursor-based pagination is always O(limit) — whatever the “page”, the cost stays the same because the database seeks directly to the cursor position in the index. This is the solution for deep pagination.
- ORDER BY without a unique tiebreaker causes shifted or duplicated data — always include a unique column (usually
id) as the last ORDER BY column and as part of the cursor.- Composite indexes must cover the WHERE columns and the ORDER BY/cursor columns — without this, cursor-based pagination isn’t faster than offset-based.
- Deferred joins reduce I/O costs in offset-based pagination — fetch IDs first via the index, then JOIN for the full columns. Can be 2-5× more efficient for tables with many large columns.
- Double COUNTs in admin panels often go unnoticed — ORMs and frontend pagination libraries often send two COUNT queries per request. Audit and replace them with LIMIT+1 or caching.
- Limit per_page and maximum OFFSET on the server — don’t trust this validation to the client. Users or attackers can request per_page=1000000 or OFFSET=99999999.
- Cursor-based doesn’t fit every case — if users need to jump to specific pages or pagination URLs must be bookmarkable, offset-based with a bounded dataset remains the right choice.