1. Home
  2. Blog
  3. Software Development

Software Development

API Pagination: Offset vs Cursor vs Keyset, Tested on 1 Million Rows

API pagination splits large result sets into pages so clients and servers are not overwhelmed. The two main designs, offset and cursor (keyset), behave very differently as data grows and changes. We timed both on a million rows, showed the duplicate item offset pagination produced when a new record arrived, a skipped row caused by ties, and built an opaque cursor.

9 min read
API pagination: offset vs cursor vs keyset, tested on 1 million rows

Key takeaways

  • API pagination with offsets is simple, but page 49,999 took 7.67 ms against 0.01 ms with keyset pagination.
  • When a new post arrived between two requests, offset paging returned the same item on page 1 and page 2; keyset paging did not.
  • Keyset paging on a non-unique column (a date) silently skipped a row until we added the ID as a tiebreaker.
  • Wrapping the last position in an opaque, base64-encoded cursor hides the implementation and lets it change later.

No API should return a million records in one response: it would be slow to build, slow to send and likely to crash the client. Pagination returns a page at a time, plus a way to get the next one. The choice of API pagination method looks like a small design detail, but it affects performance at scale, correctness when data changes and how easy the API is to use.

We used SQLite 3.50.4 from Python 3.13.6 for every query, with a 1,000,000-row table for timing and small tables to demonstrate correctness issues. The same SQL patterns apply to PostgreSQL and MySQL, where the gap is usually larger because data lives on disk.

Why paginate?

  • Performance: smaller queries, smaller responses, less memory on both sides.
  • Stability: one heavy request cannot overload the server, especially combined with rate limiting.
  • User experience: feeds and tables show results immediately and load more on demand.

Offset pagination

GET /items?limit=20&offset=40

SELECT id FROM items ORDER BY id LIMIT 20 OFFSET 40;

Offset pagination (or page-number pagination, ?page=3&per_page=20) skips a number of rows and returns the next batch. It is easy to implement, supports jumping to any page, and maps naturally onto numbered pages in a user interface. Query string handling is covered in our URL query parameters guide.

Keyset (cursor) pagination

GET /items?limit=20&after=999980

SELECT id FROM items WHERE id > 999980 ORDER BY id LIMIT 20;

Keyset pagination, also called seek pagination and the basis of most cursor pagination, remembers where the last page ended and asks for rows after that point. It cannot jump to page 500 directly, but each page is found with an index lookup instead of counting through all the earlier rows. Our SQL pagination guide covers the SQL side in more depth.

Speed on 1,000,000 rows

Page (20 rows each) OFFSET Keyset Same rows?
1 (offset 0) 0.02 ms 0.01 ms Yes
1,000 (offset 19,980) 0.52 ms 0.01 ms Yes
25,000 (offset 499,980) 3.94 ms 0.01 ms Yes
49,999 (offset 999,960) 7.67 ms 0.01 ms Yes

Both methods returned identical rows, but offset time grew in a straight line with the page number while keyset stayed flat. Our in-memory table makes every number small; on a disk-based database with wider rows, deep offset pages commonly take seconds, and crawlers or export jobs that walk every page make the total cost quadratic.

Why: the query plans

... ORDER BY id LIMIT 20 OFFSET 999980   -- SCAN items
... WHERE id > 999980 ORDER BY id LIMIT 20   -- SEARCH items USING INTEGER PRIMARY KEY (rowid>?)

OFFSET cannot jump: the database must produce and throw away all 999,980 earlier rows before returning 20. The keyset condition is an index search that starts directly at the right place. Checking plans like this is explained in our SQL EXPLAIN plan guide; the sort column must be indexed for keyset pagination to be fast, as described in SQL indexes explained.

When data changes between requests

-- newest-first feed of 10 posts, 3 per page
page 1:                [10, 9, 8]
-- a new post (11) is published before the next request
page 2 with OFFSET 3:  [8, 7, 6]     post 8 appears twice
page 2 with keyset:    [7, 6, 5]     WHERE id < 8

Offsets count positions, and positions shift when rows are inserted or deleted. The new post pushed everything down by one, so offset 3 started at post 8, which the user had already seen. Deletions cause the opposite problem: items are skipped. Keyset pagination anchors on the last item’s value, not its position, so new items at the top did not affect the next page. For live feeds, timelines and syncing data, this alone is reason to use keyset or cursor pagination.

The ties trap: always add a unique tiebreaker

-- 10 events: ids 1-5 on 2026-11-22, ids 6-10 on 2026-11-23; page size 4
page 1:                                           [1, 2, 3, 4]
next page WHERE created > '2026-11-22':           [6, 7, 8, 9]   id 5 skipped
next page WHERE (created, id) > ('2026-11-22', 4): [5, 6, 7, 8]   correct

Keyset pagination on a column that is not unique breaks when several rows share the boundary value. Five events had the same date; the first page ended in the middle of them, and asking for “created after that date” skipped the remaining one. The fix is to sort and filter on the column plus a unique key, here (created, id), using a row-value comparison (supported by PostgreSQL, MySQL 8 and SQLite) or the equivalent created > ? OR (created = ? AND id > ?). Index both columns together.

Opaque cursors

def enc(row):
    return base64.urlsafe_b64encode(json.dumps({"c": row[1], "i": row[0]}).encode()).decode().rstrip("=")

next_cursor = enc(last_row)   # 'eyJjIjogIjIwMjYtMTEtMjIiLCAiaSI6IDR9'
dec(next_cursor)              # ('2026-11-22', 4)

Instead of exposing after_id and after_created as separate parameters, many APIs return a single opaque cursor string that encodes the position. Clients treat it as a token and send it back unchanged. That lets the server change sort order or encoding later without breaking clients, and stops clients from fabricating positions. Base64 is encoding, not protection: anyone can decode our cursor, so validate it on the server, and sign it with an HMAC if tampering matters.

Response format

{
  "data": [ { "id": 5 }, { "id": 6 } ],
  "next_cursor": "eyJjIjogIjIwMjYtMTEtMjIiLCAiaSI6IDR9",
  "has_more": true
}

Return the items, a cursor or link for the next page, and a flag or null cursor when there are no more pages. Many APIs also put links in a Link header. Keep the page-size parameter, cap it (for example at 100) and document the default. Our REST API best practices cover consistent naming, and clients can loop over pages with the pattern from our fetch API guide.

Tip: fetch one more row than the page size. If you get it, there is a next page; return only the requested number of rows and you never need a separate count query to set has_more.

Total counts

SELECT count(*) FROM items;   -- 0.8 ms on 1,000,000 rows in SQLite

Clients often want “page 3 of 120”. SQLite counted our million rows in under a millisecond, but on large tables in other databases, especially with filters, an exact count can cost as much as the query itself, on every request. Options: make the count optional (?include_total=true), return an estimate, cache it, or drop it and show “load more”. Infinite scroll and cursor pagination usually do not need totals at all.

Offset or cursor: which to choose?

Offset / page number Keyset / cursor
Deep pages Slower and slower Constant speed
Data changes during paging Duplicates and gaps Stable
Jump to page N Yes No (next and previous only)
Total pages Natural Separate query if needed
Implementation Simplest Needs a unique sort key
Best for Small, stable data; admin tables Feeds, large or changing data, public APIs, exports

API pagination design checklist

  1. Always sort by a deterministic order that ends in a unique column.
  2. Index the sort columns together.
  3. Cap the page size and document the default.
  4. Use cursors for public, large or frequently changing collections.
  5. Return a clear end-of-results signal.
  6. Keep filters in the cursor or require them on every request, so a cursor cannot be reused with different filters.
  7. Consider HTTP caching for first pages, which are requested most often.

Final thoughts

API pagination is easy to start and hard to change later, so choose deliberately. Offset pagination is fine for small, stable data and numbered pages, but in our tests it slowed down on deep pages and showed a duplicate the moment a new item arrived. Keyset pagination, wrapped in an opaque cursor and backed by a unique, indexed sort key, stayed at 0.01 ms on any page and kept results stable. For most public APIs and feeds, cursors are the right default.

Frequently asked questions

What is API pagination?

A way to return a large list of results in smaller pages, with a mechanism such as an offset, page number or cursor that lets the client request the next page.

What is the difference between offset and cursor pagination?

Offset pagination skips a number of rows, which gets slower on deep pages and shifts when data changes. Cursor pagination continues after the last item seen, which stays fast and stable.

Why does offset pagination show duplicate items?

Offsets count positions. If a new row is inserted before the current page between two requests, every row shifts down by one and the next page repeats the last item of the previous one.

What is keyset pagination?

Pagination that filters on the sort key of the last row returned, such as id greater than the last id, so the database can use an index to start exactly where the previous page ended.

Should an API return the total count?

Only if clients need it. Exact counts can be expensive on large filtered tables, so consider making them optional, estimated or cached, or use a has more flag instead.

Keep learning

Work with Stack Quora

Need help with testing, WordPress or SEO?

We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Confusion Matrix Explained: Precision, Recall, F1 and Thresholds in Python

Confusion matrix explained with Python: TP, FP, FN, TN, precision, recall, F1, the accuracy trap, threshold choice by cost and…

Read article →
Software Development

API Pagination: Offset vs Cursor vs Keyset, Tested on 1 Million Rows

API pagination explained: offset vs keyset and cursor pagination timed on 1 million rows, duplicates when data changes, the ties…

Read article →
DevOps

Git Submodule Explained: Add, Clone, Update and Remove Safely

Git submodule explained: add, clone, update –init, update –remote, the detached HEAD trap, foreach and clean removal, tested step by…

Read article →
Test Automation

Pytest tmp path Explained: tmp_path, tmp_path_factory and Temp Files

Pytest tmp_path explained: per-test temporary folders, tmp_path_factory for shared files, monkeypatch.chdir, legacy tmpdir, retention and basetemp.

Read article →
Software Testing

Test Case Prioritization: 6 Techniques Compared With APFD

Test case prioritization compared: risk, coverage, cost, history and random orders scored with APFD and time to find all faults…

Read article →
Databases & SQL

Full Text Search in SQL: FTS5, Ranking, Phrases and Speed

Full text search in SQL with SQLite FTS5: MATCH queries, phrases, prefixes, NEAR, stemming, BM25 ranking, snippets, two gotchas and…

Read article →
Databases & SQL

Generated Columns in SQL: Virtual vs Stored, Indexes and Limits

Generated columns in SQL explained: virtual vs stored, automatic updates, write errors, storage cost, indexing, JSON fields and support in…

Read article →
Software Development

Object Freeze in JavaScript: Shallow vs Deep, Seal and const

Object freeze in JavaScript explained: freeze vs seal vs const, strict mode errors, shallow vs deep freeze, frozen arrays, Map…

Read article →
Software Development

Python hashlib: SHA-256, File Hashes, HMAC and Password Hashing

Python hashlib explained: SHA-256 for strings and files, algorithm speeds, why passwords need PBKDF2 or scrypt, safe verification and HMAC…

Read article →
Software Development

Python UUID Explained: uuid1, uuid4, uuid5, uuid7 and Database Keys

Python UUID explained: uuid4, uuid5, uuid1 privacy, time-ordered uuid7, parsing, storage formats and UUIDs as database keys, tested on Python…

Read article →
Artificial Intelligence

Random Forest in Python: Bagging, Feature Sampling and OOB From Scratch

Random forest in Python from scratch: bootstrap sampling, feature sampling, number of trees, out-of-bag evaluation and impurity vs permutation importance.

Read article →
Software Development

MIME Types Explained: Content-Type, nosniff and a Common Types List

MIME types explained and tested in a browser: Content-Type, nosniff, scripts and stylesheets with wrong types, a common MIME types…

Read article →
DevOps

Git Clean Explained: Remove Untracked Files Without Regret

Git clean explained with dry runs: -n, -f, -d, -x, -X, exclusions, paths, nested repositories and interactive mode, plus a…

Read article →
Test Automation

Pytest approx: Comparing Floats With rel, abs and Collections

Pytest approx explained: default tolerances, the near-zero trap, rel and abs, lists and dicts, NaN, NumPy, and how it differs…

Read article →
Software Testing

Accessibility Testing: WCAG Checklist, Automated Tools and Manual Checks

Accessibility testing explained: WCAG basics, an axe-core scan of a broken and a fixed page, what automation missed, contrast checks…

Read article →
Databases & SQL

SQL UNPIVOT: Turn Columns Into Rows, 4 Ways Tested

SQL UNPIVOT explained: turn columns into rows with UNION ALL, CROSS JOIN with CASE, JSON functions or native UNPIVOT, with…

Read article →
Software Development

Intl NumberFormat in JavaScript: Currency, Compact, Percent and Units

Intl.NumberFormat in JavaScript: locales, currency, compact notation, percent, units, rounding, formatToParts, the non-breaking space trap and formatter reuse.

Read article →
Databases & SQL

SQL EXPLAIN Plan: How to Read Query Plans and Fix Slow Queries

SQL EXPLAIN plan explained: read scans, index searches, covering indexes and temporary sorts, with before and after timings for five…

Read article →
Artificial Intelligence

Decision Tree in Python: Gini, Entropy and Overfitting From Scratch

Decision tree in Python from scratch: Gini impurity, entropy, information gain, best splits, readable rules, and how depth and leaf…

Read article →
DevOps

Git Blame Explained: Find Who Changed a Line, and Why

Git blame explained with a real repository: ignore whitespace, line ranges and functions, ignore-revs files for formatting commits, moved code…

Read article →
Test Automation

Pytest Benchmark: Measure Speed and Catch Performance Regressions

Pytest benchmark tutorial: the benchmark fixture, reading results, parametrized and pedantic benchmarks, saving baselines and failing the build on regressions.

Read article →
Software Testing

Defect Density: Formula, Worked Examples and How to Use It

Defect density explained: the formula, a five-module worked example, per KLOC vs per function point, severity weighting, defect removal efficiency…

Read article →
Databases & SQL

Case Insensitive Search in SQL: LOWER, LIKE, COLLATE and Indexes

Case insensitive search in SQL: LOWER, LIKE, COLLATE NOCASE and expression indexes compared with query plans, plus accented letters and…

Read article →
Databases & SQL

ON DELETE CASCADE in SQL: CASCADE vs SET NULL vs RESTRICT

ON DELETE CASCADE in SQL tested: CASCADE vs SET NULL vs RESTRICT, multi-level cascades, the SQLite foreign key pragma, row…

Read article →
Artificial Intelligence

Logistic Regression in Python: Sigmoid, Log Loss and Gradient Descent From Scratch

Logistic regression in Python from scratch: sigmoid, log loss, gradient descent, odds ratios, thresholds, sigmoid overflow and separable data, tested…

Read article →
DevOps

Git Worktree Explained: Work on Two Branches at Once

Git worktree explained: add, list, remove, prune, lock and detached worktrees, a hotfix workflow, and the errors you will hit,…

Read article →
Test Automation

Pytest ini Explained: addopts, markers, testpaths and Precedence

Pytest ini explained: testpaths, addopts, strict markers, xfail_strict, filterwarnings, minversion, config file precedence and command-line overrides, tested.

Read article →
Software Testing

Test Estimation Techniques: WBS, Three Point PERT and Monte Carlo

Test estimation techniques worked through: WBS, three point PERT, total uncertainty, a Monte Carlo check, percentage rules and Wideband Delphi.

Read article →
Artificial Intelligence

Linear Regression in Python: Least Squares From Scratch, With R Squared

Linear regression in Python from scratch: closed form, NumPy least squares and gradient descent, R squared, RMSE, outliers and curved…

Read article →
DevOps

Git Remote Explained: Add, Set-URL, Rename, Remove and Push URLs

Git remote explained: add, set-url, separate push URLs, rename, remove and prune, plus how to avoid leaking tokens in remote…

Read article →
Test Automation

Pytest Mock: mocker.patch, spy, side_effect and autospec Explained

Pytest mock explained: mocker.patch, return_value, side_effect, spy, autospec and PropertyMock, with tested examples for APIs, clocks and retries.

Read article →
Software Testing

System Testing Explained: Types, Process and a Real End-to-End Example

System testing explained: types, entry and exit criteria, the process, and a real end-to-end test suite run against a small…

Read article →
Artificial Intelligence

Naive Bayes Classifier Explained: Build a Spam Filter in Python

Naive Bayes classifier explained by building a spam filter in Python: Bayes rule, the naive assumption, Laplace smoothing, log probabilities…

Read article →
DevOps

Git Branch Explained: Create, Switch, Rename, Delete and Recover

Git branch explained with tested commands: list, create, switch, rename, delete, merged checks, recovering deleted branches, remote tracking and naming.

Read article →
Test Automation

Pytest raises: Testing Exceptions, match and excinfo

Pytest raises explained with tested examples: match regexes, excinfo, custom and chained exceptions, parametrized error cases, pytest.warns and common mistakes.

Read article →
Software Testing

Non Functional Testing: Types, Metrics and a Real Load Test

Non functional testing explained: 10 key types, measurable requirements, latency percentiles and what a small real load test revealed about…

Read article →