The Day My API Hit a Wall
I remember a specific Tuesday when our e-commerce platform finally hit a wall. In staging, with a modest 10,000 products, the ‘infinite scroll’ feature was flawless. But once we crossed the 2.5 million record mark in production, users started complaining. Monitoring tools told a grim story: queries for the first page took a crisp 15ms, but fetching ‘Page 500’ was dragging for 2.4 seconds and spiking CPU usage to 85%.
Throughout my time building with MySQL, PostgreSQL, and MongoDB, I’ve seen this pattern repeat. These engines have different strengths, but they share a fatal flaw: OFFSET. If your system is designed to scale beyond a few thousand rows, you need to abandon traditional skip-based logic immediately.
The Math of the Stall: Why OFFSET Fails
Most developers reach for LIMIT and OFFSET because it feels natural. If you need the fifth page of 20 items, the logic seems simple:
-- This works at 1k rows, but chokes at 1M
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 80;Here’s where the wheels fall off. To give you those 20 records starting at row 81, the database doesn’t just jump there. It must scan the first 80 records, sort them, and then discard them just to reach your starting point. When your offset is 1,000,000, the engine does the grunt work of reading a million rows into memory and sorting them, only to throw 99.9% of that work in the trash.
This creates O(N) complexity. Your query time grows linearly with your data. It’s a performance debt that stays hidden until your database is actually under heavy load.
Comparing the Strategies
1. The Traditional OFFSET/LIMIT
- The Good: It’s dead simple to write. It also allows users to jump to a specific page, like “Go to page 10.”
- The Bad: Performance tanks as users scroll deeper. It also suffers from “drifting” results—if a new item is added while a user is on page 1, the same item might appear again on page 2.
2. Cursor-based Pagination (Keyset Pagination)
Instead of telling the database how many rows to skip, we provide a specific coordinate where we left off. We use a unique, ordered column—like a primary key or a high-precision timestamp—as our pointer.
-- The high-performance way
SELECT * FROM orders
WHERE id < 12345
ORDER BY id DESC
LIMIT 20;- The Good: It offers constant O(1) performance with proper indexing. It also handles real-time data gracefully without skipping or duplicating records.
- The Bad: You can’t “jump” to page 500. It only supports “Next” and “Previous” navigation.
Implementing Cursors in Production
If you want snappy responses, cursors are the standard. This is how platforms like Slack and Stripe handle massive data streams. Here is the workflow I use in production environments.
Step 1: Identify Your Pointer
Your cursor must be unique and consistently ordered. A standard id works, but if you need to sort by created_at, you must combine it with the id. Using a timestamp alone is risky; if two records share the same millisecond, one might be skipped entirely.
Step 2: The Optimized Query
In PostgreSQL or MySQL, your initial request looks like this:
-- Fetching the first page
SELECT id, title, created_at
FROM posts
ORDER BY id DESC
LIMIT 21; -- We fetch 21 to see if a 'next' page existsWhen the user requests the next batch, the frontend sends back the ID of the last item (let’s say ID 500). The next query becomes:
-- Jumping straight to the next set
SELECT id, title, created_at
FROM posts
WHERE id < 500
ORDER BY id DESC
LIMIT 21;Step 3: Structuring the API Response
Avoid exposing internal database IDs in your URLs. Encoding your cursor into an opaque string gives you the freedom to change your underlying logic later without breaking the frontend. Here is a clean implementation using Python and FastAPI:
import base64
def encode_cursor(record_id):
return base64.b64encode(str(record_id).encode()).decode()
def decode_cursor(cursor_string):
return int(base64.b64decode(cursor_string).decode())
@app.get("/posts")
def get_posts(cursor: str = None, limit: int = 20):
# Base query
query = "SELECT * FROM posts"
if cursor:
last_id = decode_cursor(cursor)
query += f" WHERE id < {last_id}"
query += " ORDER BY id DESC LIMIT 21"
# ... execute query ...
has_next = len(results) > limit
data = results[:limit]
# Generate the pointer for the next request
next_cursor = encode_cursor(data[-1]['id']) if has_next else None
return {
"data": data,
"paging": {
"next_cursor": next_cursor,
"has_next": has_next
}
}Field Notes: Hard-Won Lessons
1. Indexing is Non-Negotiable
Cursor pagination is only fast because it uses an index to leapfrog directly to the starting row. If your WHERE and ORDER BY clauses don’t align with a composite index, the database falls back to a full table scan. In the example above, an index on id is your lifeline.
2. Sorting by Non-Unique Columns
If you need to sort by price, your cursor must include the id to maintain a stable order. The SQL looks like this:
-- Sorting by price with an ID tie-breaker
SELECT * FROM products
WHERE (price, id) < (99.99, 500)
ORDER BY price DESC, id DESC
LIMIT 20;PostgreSQL handles these row-value comparisons beautifully. MySQL 5.7+ also supports this, though older versions might need a more verbose OR logic to achieve the same result.
3. Be Honest About the UI
You’ll need to manage stakeholder expectations. Cursor-based pagination means you can’t have a footer showing “Page 1 of 10,000.” You are trading page numbers for speed. For modern apps using infinite scroll or “Load More” buttons, this is almost always the right move.
The Takeaway
Ditching OFFSET was one of the most effective optimizations I’ve ever implemented. It turned sluggish, unpredictable endpoints into reliable, sub-30ms responses, even as our tables grew into the tens of millions. While it takes a little more effort to set up your API contracts, the performance dividends are massive. If you’re starting a new service today, build with cursors from the first commit.
