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. The client panel displays the outgoing request shape alongside 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. When requests arrive, the gateway card shows page converting into offset, an opaque cursor decoding into a seek key, or an abusive limit=100000 getting clamped to a safe maximum.

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. Yellow scanned rows represent work the executor paid for and discarded, whereas green matched rows are the records actually 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. When the request supplies after_id, a cursor, or a time window, the B-Tree index path lights up and keeps scan cost much 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. During data drift steps, the storage table reveals how concurrent inserts shift relative row offsets and cause the next page to duplicate or skip items.

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, while 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. Comparing offset scan cost against keyset seek count in the metrics panel makes the database work behind each page immediately visible.

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.