Pagination: offset vs cursor, and why page 9,500 is slow

Any list that can grow, such as posts, orders or notifications, needs pagination: send a page at a time instead of everything. The obvious way is LIMIT 20 OFFSET 40. It works until real life shows up: new rows arrive while someone is reading, and users scroll deep. The code and outputs below were run with Python 3 and SQLite (200,000 rows) before publishing.

Two approaches

• Offset pagination: "skip N rows, give me the next 20." The URL looks like ?page=3 or ?limit=20&offset=40.
• Cursor (keyset) pagination: "give me 20 rows after this one." The client sends a marker from the last row it saw, e.g. ?after=199996.

A working example

Save as pagination.py:

import sqlite3
import time

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT)")
db.executemany("INSERT INTO posts (title) VALUES (?)", [(f"post {i}",) for i in range(1, 200_001)])


def offset_page(limit, offset):
    return db.execute(
        "SELECT id FROM posts ORDER BY id DESC LIMIT ? OFFSET ?", (limit, offset)
    ).fetchall()


def cursor_page(limit, before_id=None):
    """Keyset pagination: 'give me the next rows after this id'."""
    if before_id is None:
        rows = db.execute("SELECT id FROM posts ORDER BY id DESC LIMIT ?", (limit,)).fetchall()
    else:
        rows = db.execute(
            "SELECT id FROM posts WHERE id < ? ORDER BY id DESC LIMIT ?", (before_id, limit)
        ).fetchall()
    ids = [r[0] for r in rows]
    next_cursor = ids[-1] if len(ids) == limit else None
    return ids, next_cursor


print("== 1. A new post arrives while the user reads ==")
p1 = [r[0] for r in offset_page(5, 0)]
db.execute("INSERT INTO posts (title) VALUES ('brand new')")  # id 200001
p2 = [r[0] for r in offset_page(5, 5)]
print("offset page 1:", p1)
print("offset page 2:", p2, "<- repeats", sorted(set(p1) & set(p2)))

db.execute("DELETE FROM posts WHERE id = 200001")  # reset
c1, nxt = cursor_page(5)
db.execute("INSERT INTO posts (title) VALUES ('brand new')")
c2, _ = cursor_page(5, nxt)
print("cursor page 1:", c1)
print("cursor page 2:", c2, "<- no repeats:", not set(c1) & set(c2))

print()
print("== 2. Cost of going deep ==")
print("OFFSET plan :", db.execute(
    "EXPLAIN QUERY PLAN SELECT id FROM posts ORDER BY id DESC LIMIT 20 OFFSET 190000").fetchall()[0][3])
print("cursor plan :", db.execute(
    "EXPLAIN QUERY PLAN SELECT id FROM posts WHERE id < 10000 ORDER BY id DESC LIMIT 20").fetchall()[0][3])


def timed(fn, repeat=20):
    t = time.perf_counter()
    for _ in range(repeat):
        fn()
    return (time.perf_counter() - t) / repeat * 1000


print(f"offset page 1     : {timed(lambda: offset_page(20, 0)):.3f} ms")
print(f"offset page 9500  : {timed(lambda: offset_page(20, 190000)):.3f} ms")
print(f"cursor deep page  : {timed(lambda: cursor_page(20, 10000)):.3f} ms")

Output of python3 pagination.py:

== 1. A new post arrives while the user reads ==
offset page 1: [200000, 199999, 199998, 199997, 199996]
offset page 2: [199996, 199995, 199994, 199993, 199992] <- repeats [199996]
cursor page 1: [200000, 199999, 199998, 199997, 199996]
cursor page 2: [199995, 199994, 199993, 199992, 199991] <- no repeats: True

== 2. Cost of going deep ==
OFFSET plan : SCAN posts
cursor plan : SEARCH posts USING INTEGER PRIMARY KEY (rowid<?)
offset page 1     : 0.016 ms
offset page 9500  : 3.563 ms
cursor deep page  : 0.019 ms

Problem 1: items repeat or vanish

A new post arrived between page 1 and page 2. With offset, everything shifted down by one, so page 2 started with 199996, which the user already saw on page 1. A deletion would do the opposite and make an item disappear from the sequence. With a cursor, page 2 means "rows older than 199996", so the new row does not disturb it: no repeat, no gap.

For feeds, timelines and anything that changes often, this alone is a strong reason to prefer cursors.

Problem 2: deep pages are slow

The database cannot jump to row 190,000. For OFFSET 190000 it must walk past 190,000 rows and throw them away. The plan shows SCAN. The cursor query does a SEARCH on the primary key and goes straight to the right place.

Measured on this machine (average of 20 runs; numbers vary by machine, the pattern does not):

• offset, page 1: about 0.016 ms
• offset, page 9,500: about 3.6 ms, hundreds of times slower
• cursor, same depth: about 0.019 ms

SQLite in memory with 200,000 rows hides the pain. On a real database with millions of rows, joins and disk reads, the gap becomes seconds. Offset cost grows with how deep you go.

What cursor pagination costs you

• No jumping to "page 47". You can go next (and previous, if you build it), but not to an arbitrary page. For feeds and infinite scroll that is fine. For an admin table where people want page numbers, offset may be the better fit.
• A stable, unique order is required. If you sort by created_at and two rows share the same value, a cursor on created_at alone can skip or repeat rows. Add a tie-breaker: order by (created_at, id) and compare both. Row-value comparisons such as (created_at, id) < (?, ?) are supported by PostgreSQL and recent SQLite; check your database.
• The sort column must be indexed, or the cursor query is slow too.
• No cheap total count. COUNT(*) on a big table is itself costly. Many APIs drop the exact total, or return an approximate one.

Make the cursor opaque

Return the cursor as an opaque string (for example base64 of the values) instead of exposing 199996. Clients then do not build their own, and you can change the internals later. Do not trust it blindly: validate it, and sign it if it contains anything sensitive. Never put it straight into SQL; use parameters, as the example does.

A good response shape

{
  "data": [ ... 20 items ... ],
  "next_cursor": "MTk5OTk1",
  "has_more": true
}

The client passes next_cursor back to get the next page. When next_cursor is null, there is nothing more. In the code, that is the case when a page returns fewer rows than the limit.

Tests

Save as test_pagination.py:

import unittest

import pagination as p


class PageTests(unittest.TestCase):
    def test_cursor_walks_everything_once(self):
        seen, cur = [], None
        while True:
            ids, cur = p.cursor_page(5000, cur)
            seen += ids
            if cur is None:
                break
        self.assertEqual(len(seen), len(set(seen)))
        self.assertEqual(seen, sorted(seen, reverse=True))

    def test_last_page_has_no_cursor(self):
        _, cur = p.cursor_page(5, 3)
        self.assertIsNone(cur)


if __name__ == "__main__":
    unittest.main()

$ python3 -m unittest
Ran 2 tests in 0.224s

OK

The first test walks all 200,000 rows by cursor and checks that each id appears exactly once, in order.

Which should you use?

• Cursor: feeds, timelines, logs, notifications, "load more", big or fast-changing tables, public APIs where clients may scrape everything.
• Offset: small tables, admin screens with page numbers, data that rarely changes.
• Always set a maximum limit (for example 100) so a client cannot ask for a million rows in one go.

Common mistakes

• OFFSET on huge tables.
• Paginating without an ORDER BY. The order is then undefined and pages can overlap.
• Ordering by a non-unique column with no tie-breaker.
• No index on the sort column.
• No cap on page size.
• Building SQL for the cursor by string concatenation (injection risk).

Main idea: offset is easy but gives repeats and gaps when data changes and gets slower the deeper you go. Cursor (keyset) pagination uses an indexed, unique ordering to start exactly after the last row seen, so it stays correct and fast.

---

Backend series, Part 3 (reliable APIs): 5 of 5. Previous: API versioning: how to ship breaking changes without breaking your users.