Why Your Database Crawls at Page 5000 — And How to Fix It Forever + Video

Listen to this Post

🐢▶️ Listen🚀

Introduction:

Pagination seems trivial when your database holds a few thousand rows — but scale that to millions, and the naive `OFFSET` approach becomes a performance nightmare that can cripple your entire application. As systems grow to handle billions of records, choosing the right pagination strategy isn’t just an optimization; it’s a fundamental architectural decision that impacts database load, API response times, and user experience. This article breaks down four battle-tested pagination patterns used by tech giants like YouTube and AWS S3, complete with implementation guides, security considerations, and the trade-offs every system designer must understand.

Learning Objectives:

• Understand the performance pitfalls of offset-based pagination at scale and when it’s acceptable to use it
• Master keyset (cursor-based) pagination with practical SQL and API implementation examples
• Implement continuation tokens for stateless, secure large-scale API pagination
• Apply time-based pagination for real-time feeds and log streaming use cases
• Secure your pagination endpoints against data exposure, injection, and DoS attacks

1. Offset Pagination — The Deceptive Simplicity

Offset pagination is the default choice for most developers. You’ve seen it everywhere: ?page=3&size=10. The database skips 20 rows and returns the next 10. Simple to implement, easy to explain, and works fine for small datasets.

The Hidden Cost: The database still reads every row it skips. Page 5 is fast. Page 5,000? That’s where the wheels fall off. Behind the scenes, the database executes something like:

SELECT  FROM users ORDER BY created_at LIMIT 10 OFFSET 4990;

The database must scan and discard 4,990 rows before returning the result. As the offset grows, so does the I/O cost — linearly.

When to Use It:

• Admin panels with fixed, shallow pagination
• Internal tools where total record count is small (< 100,000)
• Situations where you genuinely need to jump to arbitrary pages

Security Consideration: Large offset values can be exploited for DoS attacks. Always enforce a maximum `limit` (e.g., 100) and reject excessive `page` values.

 Python (Flask) — Offset pagination with safety limits
@app.route('/api/users')
def get_users():
page = min(int(request.args.get('page', 1)), 100)  Cap at 100
size = min(int(request.args.get('size', 10)), 100)  Cap at 100
offset = (page - 1)  size
users = db.execute('SELECT  FROM users LIMIT ? OFFSET ?', (size, offset))
return jsonify(users)

2. Keyset (Cursor-Based) Pagination — The Scalability Champion

Keyset pagination eliminates the offset problem entirely. Instead of counting rows, you tell the database: “give me everything after ID 101”. The query uses an index to jump directly to that point, making page 1 and page 10,000 equally fast.

How It Works:

The client receives a cursor — typically the last record’s unique identifier or a composite of sort keys. The next request includes this cursor.

SQL Implementation:

-- First page
SELECT  FROM users ORDER BY created_at DESC, id DESC LIMIT 10;

-- Next page (cursor = last record's created_at and id)
SELECT  FROM users 
WHERE (created_at, id) < ('2026-01-15 10:30:00', 12345)
ORDER BY created_at DESC, id DESC LIMIT 10;

Composite Keys: When sorting by non-unique fields (like created_at), include a secondary unique field to prevent missing records that share the same timestamp.

API Design (REST):

GET /api/users?limit=10&cursor=eyJjcmVhdGVkX2F0IjoiMjAyNi0wMS0xNSAxMDozMDowMCIsImlkIjoxMjM0NX0=

The cursor is typically a Base64-encoded JSON object containing the sort key values.

Security Hardening: Never trust client-provided cursors blindly. Implement HMAC signatures to prevent cursor tampering. The cursor should be tied to the original filter and sort parameters — if the client changes filters mid-pagination, the cursor must be invalidated.

import hmac
import hashlib
import json
import base64

SECRET_KEY = b'supersecret'

def encode_cursor(created_at, id):
data = json.dumps({'created_at': str(created_at), 'id': id})
signature = hmac.new(SECRET_KEY, data.encode(), hashlib.sha256).hexdigest()
token = base64.urlsafe_b64encode(f"{data}|{signature}".encode()).decode()
return token

def decode_cursor(token):
decoded = base64.urlsafe_b64decode(token.encode()).decode()
data, signature = decoded.rsplit('|', 1)
expected = hmac.new(SECRET_KEY, data.encode(), hashlib.sha256).hexdigest()
if not hmac.compare_digest(signature, expected):
raise ValueError("Invalid cursor")
return json.loads(data)

When to Use It:

• Any API serving large datasets (> 100,000 records)
• Infinite scroll interfaces (social feeds, search results)
• Systems where consistent performance is critical

3. Continuation Tokens — The Stateless API Standard

Continuation tokens are the industry standard for large-scale APIs like AWS S3, YouTube, and Stripe. The server returns an opaque token with each response; the client sends it back on the next request, and the server resumes from where it left off.

Why Opaque? The token hides the cursor logic from the client entirely. This gives the server complete flexibility to change pagination strategies, database schemas, or even shard keys without breaking clients.

Implementation Pattern:

 Server-side continuation token generation
import base64
import json
from datetime import datetime

def generate_token(last_id, last_timestamp, filters_hash):
payload = {
'last_id': last_id,
'last_ts': str(last_timestamp),
'filters': filters_hash,  Prevents filter changes mid-pagination
'expires': (datetime.utcnow() + timedelta(hours=1)).isoformat()
}
 Sign to prevent tampering
return base64.urlsafe_b64encode(
json.dumps(payload).encode()
).decode()

Client request
GET /api/videos?limit=50&token=eyJsYXN0X2lkIjoiMTIzIi...

Server response
{
"data": [...],
"next_token": "eyJsYXN0X2lkIjoiNDU2Ii...",
"has_more": true
}

Critical Security Rules:

1. Token Expiry: Continuation tokens must expire (e.g., 1 hour) to prevent replay attacks
2. Filter Binding: The token must be cryptographically tied to the original filters and sort order
3. Authorization Re-check: Always re-validate user permissions on every request — never assume the token implies authorization
4. Rate Limiting: Apply per-user rate limits to prevent pagination-based DoS attacks

When to Use It:

• Public-facing APIs with millions of clients
• Systems requiring backwards-compatible schema evolution
• Microservices where the pagination implementation is internal

4. Time-Based Pagination — For Feeds and Logs

Time-based pagination is the go-to pattern for real-time feeds, log streams, and any dataset ordered chronologically. You ask for items before a specific timestamp and read backwards.

SQL Implementation:

-- Fetch 20 posts before a given timestamp
SELECT  FROM posts 
WHERE created_at < '2026-01-15T10:30:00Z'
ORDER BY created_at DESC 
LIMIT 20;

The Pitfall: Multiple records can share the exact same timestamp. Without a tie-breaker, you’ll lose records.

The Fix: Always include a secondary unique field:

SELECT  FROM posts 
WHERE (created_at, id) < ('2026-01-15T10:30:00Z', 12345)
ORDER BY created_at DESC, id DESC 
LIMIT 20;

Real-World Use Case: Log aggregation systems like ELK stack use time-based pagination to stream logs without offset overhead. For compliance and security auditing, this pattern ensures you can page through millions of log entries without performance degradation.

Log Streaming Example (Linux):

 Stream logs with timestamp-based filtering using jq
curl -s "https://api.logs.example.com/entries?before=2026-01-15T10:30:00Z&limit=100" \
| jq '.entries[] | {timestamp: .timestamp, message: .message}'

Windows PowerShell Equivalent:

Invoke-RestMethod -Uri "https://api.logs.example.com/entries?before=2026-01-15T10:30:00Z&limit=100" `
| Select-Object -ExpandProperty entries `
| Format-Table timestamp, message

5. Security Deep Dive — Pagination Attack Vectors

Pagination endpoints are frequently overlooked in security reviews, yet they present multiple attack surfaces:

1. Data Exposure via Predictable IDs:

If your cursors or offsets expose sequential IDs, attackers can enumerate all records. Always use UUIDs or hashids for publicly exposed identifiers.

1. SQL Injection in ORDER BY and Filter Clauses:
   Dynamic `ORDER BY` and `WHERE` clauses are prime injection vectors. Never interpolate user input directly:

 VULNERABLE — DO NOT DO THIS
query = f"SELECT  FROM users ORDER BY {request.args.get('sort')} LIMIT 10"

SAFE — Whitelist allowed columns
ALLOWED_SORT = {'id', 'name', 'created_at'}
sort_col = request.args.get('sort', 'id')
if sort_col not in ALLOWED_SORT:
raise ValueError("Invalid sort column")
query = "SELECT  FROM users ORDER BY ? LIMIT 10"

3. Large Payload Attacks:

Attackers can request massive `limit` values to exhaust server memory. Always enforce upper bounds.

MAX_LIMIT = 100
limit = min(int(request.args.get('limit', 20)), MAX_LIMIT)

4. Cursor Replay and Tampering:

Unsigned cursors can be forged to access arbitrary pages. Sign all tokens with HMAC-SHA256.

5. Authorization Bypass:

Never assume that because a client has a valid cursor, they’re authorized to see the data it points to. Re-validate permissions on every request.

6. Database-Specific Optimizations

PostgreSQL — Indexing for Keyset Pagination:

CREATE INDEX idx_users_created_at_id ON users (created_at DESC, id DESC);

MySQL — Using Covering Indexes:

CREATE INDEX idx_users_created_at_id ON users (created_at, id);

MongoDB — Cursor-Based Pagination:

// First page
db.users.find().sort({created_at: -1, _id: -1}).limit(10)

// Next page (cursor = last document)
db.users.find({
$or: [
{created_at: {$lt: last_created_at}},
{created_at: last_created_at, _id: {$lt: last_id}}
]
}).sort({created_at: -1, _id: -1}).limit(10)

Elasticsearch — Search After:

GET /users/_search
{
"size": 10,
"sort": [{"created_at": "desc"}, {"id": "desc"}],
"search_after": ["2026-01-15T10:30:00Z", 12345]
}

7. System Design Interview — Pagination Questions

In system design interviews, pagination is almost always part of the discussion. Here’s how to ace it:

Common Questions:

• “How would you design a news feed API that supports infinite scroll?”
• “Your database has 100 million user records. How do you implement user search with pagination?”
• “Design an API for a logging system that handles 1 million log entries per second.”

The Framework:

1. Clarify Requirements: What’s the dataset size? Expected QPS? Need to jump to arbitrary pages or just infinite scroll?

2. Estimate: Calculate storage, bandwidth, and database load.

1. Choose the Pattern: Offset for admin panels (<100k records), keyset for infinite scroll, continuation tokens for public APIs, time-based for logs.
2. Discuss Trade-offs: Offset is simple but doesn’t scale; keyset is fast but can’t jump to arbitrary pages; tokens are flexible but add complexity.
3. Security: Mention cursor signing, rate limiting, and authorization checks.

Sample Answer Structure:

“For a Twitter-like feed with 500 million daily active users, I’d use keyset pagination with continuation tokens. The cursor would be a composite of `(tweet_id, created_at)` with HMAC signing. I’d enforce a max limit of 50 and implement Redis-based rate limiting per user. The token would expire after 15 minutes and be invalidated if the user changes filters.”

What Undercode Say:

• Pagination is not a one-size-fits-all problem. The choice between offset, keyset, continuation tokens, and time-based pagination depends on your dataset size, access patterns, and whether users need to jump to arbitrary pages. Most systems overuse offset pagination and pay the performance price at scale.

• Security is non-1egotiable. Pagination endpoints are often left unprotected. Signed cursors, input validation, rate limiting, and re-authorization on every request are not optional — they’re mandatory for production-grade systems. A single pagination vulnerability can expose millions of records.

Analysis:

The post by Alex Xu (@alexxubyte) succinctly captures the essence of a problem that plagues many growing systems — the naive assumption that `OFFSET` works forever. What makes this content particularly valuable is the practical comparison of four distinct strategies, each with clear use cases and trade-offs. The inclusion of continuation tokens as used by AWS S3 and YouTube grounds the discussion in real-world architecture. For system design interviews, this knowledge is gold — interviewers routinely probe candidates on pagination to assess their understanding of database indexing, API design, and performance at scale. The hidden insight here is that pagination isn’t just about performance; it’s about security, maintainability, and graceful degradation under load. The mention of time-based pagination with secondary fields reveals an attention to edge cases that separates senior engineers from juniors. For anyone preparing for a system design interview or building a data-intensive application, mastering these four patterns is non-1egotiable.

Prediction:

• +1 The adoption of cursor-based pagination will become the default standard for all new APIs within the next 2-3 years, as ORMs and database drivers increasingly bake keyset pagination into their core abstractions. This will reduce the number of performance incidents caused by large offset queries.

• +1 AI-powered code assistants will soon auto-detect offset pagination in code reviews and suggest cursor-based alternatives, dramatically reducing the knowledge gap between junior and senior engineers.

• -1 As pagination becomes more sophisticated, attackers will shift focus to cursor tampering and token replay attacks. Organizations that fail to implement HMAC-signed cursors and short-lived tokens will face data breach incidents specifically targeting pagination endpoints.

• -1 The rise of serverless and edge computing will expose a new class of pagination vulnerabilities — stateless functions that don’t maintain session context may inadvertently expose data across tenants if cursors aren’t properly bound to user permissions.

• +1 The ByteByteGo System Design PDF and similar free resources will continue to democratize system design knowledge, leveling the playing field for engineers from non-traditional backgrounds and reducing the “interview prep” advantage of elite institutions.

• +1 We’ll see the emergence of “pagination-as-a-service” libraries that handle cursor signing, token expiry, filter binding, and rate limiting out-of-the-box, reducing boilerplate and security errors across the industry.

This article was inspired by Alex Xu’s post on pagination strategies and the comprehensive System Design resources available through ByteByteGo. For a deeper dive, subscribe to the ByteByteGo newsletter and download the free 368-page System Design PDF.

▶️ Related Video (80% Match):

🎯Let’s Practice For Free:

🎓 Live Courses & Certifications:

Join Undercode Academy for Verified Certifications

🚀 Request a Custom Project:

Secure, high-velocity infrastructure and disruptive technological engineering. Contact our engineering team for high-tier development and proprietary systems:
[email protected]
💎 Smart Architecture | 🛡️ Secure by Design | ⭐ Trusted by Thousands

IT/Security Reporter URL:

Reported By: Alexxubyte Systemdesign – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅

🔐JOIN OUR CYBER WORLD [ CVE News • HackMonitor • UndercodeNews ]

💬 Whatsapp | 💬 Telegram

📢 Follow UndercodeTesting & Stay Tuned:

𝕏 formerly Twitter 🐦 | @ Threads | 🔗 Linkedin | 🦋BlueSky