The Offset Massacre — Why Cursor Pagination is Mandatory (2026)

Context: Yesterday we saved Postgres from connection exhaustion. Today, we save it from CPU exhaustion. We are going to kill the most common, most destructive SQL pattern taught in every coding bootcamp.

$Infographic comparing OFFSET vs. CURSOR pagination. It shows OFFSET causes performance crashes (scanning 4M rows) and UI glitches (duplicates/gaps). CURSOR wins using indexed "bookmarks" for $O(\log N)$ speed, constant performance, and bot resistance in infinite scrolls.$

The Deep Scrape

A few years ago, the CPU alerts on my read-replicas started screaming at 3 AM. A single IP address in Russia had figured out the URL structure of our product catalog. They wrote a dumb Python script.

requests.get("api/products?page=1")
requests.get("api/products?page=2")

They left it running. By the time I woke up, the bot was requesting page=80000. That single request translated to a SQL query hitting a table with 20 million rows. To serve page 80,000, Postgres was spending two full seconds reading and discarding four million rows in RAM, just to return the final 50 rows. The bot was firing five requests a second. The database couldn't keep up. The catalog went down.

I didn't block the IP. I fixed the architecture. I deleted the OFFSET command from the codebase forever.

1. The Dictionary Problem

Imagine I hand you a massive dictionary and say: "Read me the 50 words on page 900."

How does a junior developer (and standard SQL) handle this? They start at page 1. They read every single word, counting them one by one, until they reach the 450,000th word. They throw all those counted words in the trash. Then they read the next 50 words to me.

This is exactly what happens when you write OFFSET 450000 LIMIT 50. Postgres does not jump. It reads from the disk. It counts. It discards. It scales in O(N) time. Page 1 takes a millisecond. Page 10,000 takes 500 milliseconds. As your app scales, your deep pages become database killers.

2. The UI Shifting Glitch

It's not just a database problem. It's a UI disaster.

Have you ever scrolled down a busy Reddit feed, clicked "Next Page", and seen a meme at the top of Page 2 that you already saw at the bottom of Page 1? That is the Offset failure.

While you were reading Page 1, five new memes were posted to the database. They bumped everything down. The meme that was at position 48 shifted to position 53. When your browser requests OFFSET 50, it grabs that shifted meme again. You duplicate data. Or worse, if items were deleted, you skip data entirely.

3. The Solution: Cursor Pagination

I hand you the dictionary again. I say: "Read me the 50 words that come immediately after the word 'Xylophone'."

Because the dictionary is sorted alphabetically (it has an Index), you don't start at page 1. You flip instantly to 'X'. You find 'Xylophone'. You read the next 50 words. It takes you exactly the same amount of time whether I ask for 'Apple' or 'Zebra'.

This is Cursor Pagination. We stop using page numbers. We use a bookmark. We pass an ID or a timestamp to the backend. The database uses its B-Tree Index to instantly teleport to that ID in O(log N) time, completely bypassing the million rows that came before it.

4. The Implementation

Here is the exact code pattern you drop into your FastAPI and SQLAlchemy architecture. No external magic libraries required.

The B-Tree Teleport (FastAPI)

from fastapi import APIRouter, Query

router = APIRouter()

@router.get("/api/v1/feed")
def get_infinite_feed(
    # Default to 0 for the very first page load
    last_id: int = Query(0, description="The ID of the last item you saw."),
    limit: int = Query(50)
):
    # 🚨 THE O(1) MAGIC 🚨
    # SELECT * FROM feed WHERE id > {last_id} ORDER BY id ASC LIMIT {limit};
    
    # Pretend this is your SQLAlchemy call
    results = database.query(FeedItem).filter(FeedItem.id > last_id).limit(limit).all()
    
    # We must tell the frontend what their NEXT bookmark should be.
    # We grab the ID of the very last item in this batch.
    next_cursor = results[-1].id if results else None
    
    return {
        "data": results,
        "next_cursor": next_cursor
    }

When the user scrolls to the bottom of the app, the frontend looks at the next_cursor (let's say it's 942), and fires an API call to /api/v1/feed?last_id=942. The database jumps straight to 942. No rows are discarded. No rows are shifted.

5. The Architectural Tradeoff

If Cursor pagination is so fast, why doesn't everyone use it?

Because it strips power from the UI. You cannot build a "Page 45" button at the bottom of the screen. A cursor only knows what comes directly after it. You can only build "Next" and "Previous" buttons.

This is why Facebook, Instagram, and Twitter don't have page numbers. They use Infinite Scroll. It is a brilliant UI pattern that was forced into existence by a brutal backend architectural limitation. You sacrifice random-access page jumping to guarantee the survival of the database.

🛠️ Day 22 Project: Mathematical Proof

Don't take my word for it. I built a raw SQLite backend in the official repository to prove the math.

Run the pagination_engine.py file. It generates a local database with 1,000,000 rows.
Hit the /api/v1/offset?page=20000 endpoint. Watch the terminal timing. It will take roughly 15-20 milliseconds because SQLite has to physically scan and discard 999,950 rows just to find your 50.
Hit the /api/v1/cursor?last_id=999950 endpoint. It will return the exact same data in 0.1 milliseconds. It used the B-Tree index to teleport. The proof is in the execution.

View the Pagination Engine on GitHub →

Search This Blog

The Dharma of Development: Finding Purpose in Every Line of Code

Featured