API Pagination

Mental Model

Pagination is the simple idea of breaking a long list into smaller chunks instead of returning everything at once.

The important beginner model is that a page is not only a UI convenience. It is a contract between the client, gateway, query plan, and storage layer about how much work one request is allowed to create. A good pagination design keeps response size bounded, keeps database work predictable, and gives the client a stable way to ask for the next slice.

Offset pagination counts positions and can drift when rows are inserted or deleted. Keyset and cursor pagination move from a known item or encoded position, preserving a more stable boundary. Time-based pagination moves through chronological windows. The visualization shows the same request path for each style so you can compare the cost, consistency, and safety tradeoffs without memorizing a long step list in the article.

Components and Why They Exist

Client / Consumer

The client represents the app, feed, dashboard, or API consumer that wants a small slice of a much larger dataset. It sends pagination parameters such as offset, limit, page, after_id, cursor, or time bounds, then renders the returned records and next-page metadata. In the demo, notice that the client panel shows the request shape and whether the response returns a bounded set, a duplicate from drift, or a rewritten safe limit.

API Gateway

The gateway protects downstream systems from unbounded page sizes and translates client-friendly pagination inputs into safe backend query parameters. It validates limits, routes the request to the right query plan, decodes opaque cursors, and can rewrite unsafe values before storage is touched. In the demo, watch for the gateway changing page into offset, decoding a cursor into a seek key, or overriding an abusive limit=100000 request.

SQL Volcano Executor

Offset-style pagination needs a sequential executor to skip rows before it can return the requested page. It scans rows in order, discards the skipped portion, and returns the next limit rows. That is simple, but deep offsets make the database do work the client never sees. In the demo, notice that yellow scanned rows are work that was paid for but discarded; green matched rows are the records returned to the client.

B-Tree Index Search

Keyset, cursor, and time-based pagination need a way to jump near the next records without counting every earlier row. It uses ordered keys such as IDs or timestamps to seek into the dataset and read forward from that point. In the demo, notice that the index path should light up when the request uses after_id, a cursor, or a time range, and scan cost should stay lower than deep offset pages.

Storage Pages

Storage is where pagination becomes real data. Insertions, deletions, ordering, and timestamps decide whether a page is stable or drifts. It holds the ordered record table, highlights scanned and matched rows, and shows how mutations can shift offset positions. In the demo, notice that in drift steps, the storage table shows why relative offsets can duplicate or skip items after new records appear.

Metrics and Inspector

Pagination bugs are easier to understand when you can connect the request to database cost, returned rows, and component state. Metrics expose scan cost and seek count. The click Inspector explains what the selected component is doing now, why it matters, and how it works with the rest of the request path. In the demo, compare offset scan cost against keyset seek count, then inspect the gateway, executor, index, or storage card to see which component owns the current behavior.

Edge Cases and Misconceptions

• Page numbers are friendly but not free: page=50 usually becomes a deep offset query, so the backend may still scan and discard many rows.
• Cursors are not automatically secure: Base64 is only encoding. If a cursor contains raw IDs or timestamps, sign or encrypt it when tampering would be harmful.
• Drift is a consequence of relative position: Offset pagination asks for “the item currently at this position.” If inserts or deletes shift the table, the same offset can point at a different record later.
• Keyset pagination trades random access for stability: It is fast and drift-resistant, but it usually supports next/previous traversal rather than arbitrary page jumps.
• A max limit is part of reliability: Page-size caps protect memory, network bandwidth, query time, and other users sharing the same database.