KAHIBARO
Discord Login Register ☰
☰

Pagination

Table of Contents

Why Pagination Matters

When an API returns a list of items, the number of results can grow very large over time. Imagine:

If the API tried to return all items in a single response:

Pagination solves this by splitting large result sets into smaller pieces called pages, and letting the client request one page at a time.

Core idea of pagination:
Never return an unbounded list of items. Always limit how many items you return, and let the client control which subset (page) they want.

Basic Pagination Concepts

Before looking at concrete strategies, it helps to know the common concepts.

ConceptMeaning
Page / sliceA subset of the full result set, usually limited to a fixed size
Page size / limitMaximum number of items in a page, for example 10, 20, or 100
OffsetNumber of items to skip at the start of the result set
CursorA pointer or token that represents a position in the result set
Total countTotal number of items matching the query, for example 12,345

Every pagination strategy combines one way to specify which page with one way to limit how many items are returned.

Offset-based Pagination

Offset-based pagination is the simplest and most common style. It uses two main query parameters:

For example, for a products list:

http 
GET /products?limit=20&offset=0   # first 20 products
GET /products?limit=20&offset=20  # next 20 products (items 21-40)
GET /products?limit=20&offset=40  # items 41-60

Or using page and page_size:

http 
GET /products?page=1&page_size=20  # items 1-20
GET /products?page=2&page_size=20  # items 21-40
GET /products?page=3&page_size=20  # items 41-60

Internally, for a relational database, offset-based pagination often becomes:

sql 
SELECT *
FROM products
ORDER BY id
LIMIT :limit OFFSET :offset;

Example JSON Response

A common response structure for offset-based pagination:

json 
{
  "items": [
    {"id": 21, "name": "Product 21"},
    {"id": 22, "name": "Product 22"}
  ],
  "limit": 2,
  "offset": 20,
  "total": 45
}

You can also include URLs for convenience:

json 
{
  "items": [...],
  "limit": 20,
  "offset": 20,
  "total": 200,
  "links": {
    "self": "/products?limit=20&offset=20",
    "next": "/products?limit=20&offset=40",
    "prev": "/products?limit=20&offset=0"
  }
}

Pros and Cons

AspectOffset-based pagination
SimplicityVery easy to understand and implement
FlexibilityCan jump directly to any offset (for example offset = 2000)
PerformanceGets slower with large offsets, databases need to skip many rows
ConsistencyCan show duplicates or missing items if data changes while paging

Example of inconsistency:

  1. Client loads offset=0&limit=10 and sees items with IDs 1 to 10.
  2. A new item with ID 0 is inserted at the beginning.
  3. Client loads offset=10&limit=10 and now gets items with IDs 11 to 20.

The client has missed the new item with ID 0, and depending on sorting could see shifts or duplicates.

Offset-based pagination is good for:

It is not ideal for:

Page-based Pagination

Page-based pagination is just a friendly layer over offset pagination. Instead of offset, you use:

Example:

http 
GET /articles?page=1&page_size=10   # items 1-10
GET /articles?page=2&page_size=10   # items 11-20
GET /articles?page=3&page_size=10   # items 21-30

Conversion between page and offset is:

For example, page=3, page_size=10 gives offset = (3 - 1) * 10 = 20.

Formula:
For page-based pagination, with 1-based page numbering,
$$\text{offset} = (\text{page} - 1) \times \text{page\_size}$$

Example JSON Response

json 
{
  "items": [
    {"id": 11, "title": "Article 11"},
    {"id": 12, "title": "Article 12"}
  ],
  "page": 2,
  "page_size": 2,
  "total": 5,
  "total_pages": 3
}

total_pages can be computed as:

$$
\text{total\_pages} = \left\lceil \frac{\text{total}}{\text{page\_size}} \right\rceil
$$

For example, total=5, page_size=2 gives ceil(5 / 2) = 3.

Pros and Cons

Page-based pagination:

It is often a good default for admin interfaces and moderate data sizes.

Cursor-based Pagination

Cursor-based pagination uses a cursor to mark where the next page starts, instead of a numeric offset or page number.

A cursor is usually:

The API uses parameters like:

For example:

http 
GET /events?limit=3              # first page, no cursor
GET /events?limit=3&after=167    # next page, after id 167

Imagine events:

idmessage
101"User A logged in"
102"User B logged out"
167"User C posted"
200"User D deleted post"
205"User E created post"

First request:

http 
GET /events?limit=3

Server response:

json 
{
  "items": [
    {"id": 101, "message": "User A logged in"},
    {"id": 102, "message": "User B logged out"},
    {"id": 167, "message": "User C posted"}
  ],
  "limit": 3,
  "next_cursor": "167",
  "has_next": true
}

Client then uses next_cursor:

http 
GET /events?limit=3&after=167

Server response:

json 
{
  "items": [
    {"id": 200, "message": "User D deleted post"},
    {"id": 205, "message": "User E created post"}
  ],
  "limit": 3,
  "next_cursor": null,
  "has_next": false
}

Here the cursor is simply the id. Internally you can run:

sql 
SELECT *
FROM events
WHERE id > :cursor
ORDER BY id
LIMIT :limit;

Encoded Cursors

Often, you do not want to expose direct IDs or timestamps, or you want more complex state. You can encode a cursor like this:

json 
{
  "items": [...],
  "next_cursor": "eyJsYXN0X2lkIjoxNjcsIm9yZGVyIjoiYXNjIn0="
}

This can be a Base64 encoded JSON object such as:

json 
{"last_id":167,"order":"asc"}

On each request:

  1. Decode the cursor.
  2. Extract necessary values, for example last_id.
  3. Use them in your database query.

Pros and Cons

AspectCursor-based pagination
PerformanceVery good for large datasets, can use index-based queries
ConsistencyMore stable with changing data, less risk of duplicates or skipped items
FlexibilityGood for "infinite scroll" and real-time feeds
ComplexityHarder to implement and to debug than simple page or offset
Jump to pageHard to jump directly to "page 100", often impossible or expensive

Cursor-based pagination fits best when:

Designing Pagination Parameters

Pagination fits naturally into query parameters in REST APIs. You typically choose one of these styles:

StyleExample request
Offset-basedGET /users?limit=20&offset=40
Page-basedGET /users?page=3&page_size=20
Cursor-basedGET /users?limit=20&after=abc123

Some design tips:

Example of a backend using defaults and maximums (in pseudocode):

python 
MAX_LIMIT = 100
DEFAULT_LIMIT = 20
def get_limit(request):
    # read from query parameter
    limit = int(request.query_params.get("limit", DEFAULT_LIMIT))
    # enforce minimum and maximum
    if limit < 1:
        limit = 1
    if limit > MAX_LIMIT:
        limit = MAX_LIMIT
    return limit

Important rule:
Always validate and clamp pagination parameters.
Never trust the client to send reasonable limit, page, or offset values.

Including Pagination Metadata

A common design decision is: What information do you put in the response besides the items themselves?

Useful fields:

Example: Page-based Response with Metadata

json 
{
  "items": [
    {"id": 101, "name": "Alice"},
    {"id": 102, "name": "Bob"}
  ],
  "page": 1,
  "page_size": 2,
  "total": 5,
  "total_pages": 3,
  "has_next": true,
  "has_prev": false,
  "links": {
    "self": "/users?page=1&page_size=2",
    "next": "/users?page=2&page_size=2"
  }
}

Example: Cursor-based Response with Minimal Metadata

json 
{
  "items": [
    {"id": 101, "message": "First"},
    {"id": 102, "message": "Second"}
  ],
  "limit": 2,
  "next_cursor": "102",
  "has_next": true
}

You can choose how much metadata you want to return. More metadata helps clients, but costs more CPU and queries.

Counting Total Items

Sometimes you want to show "Page 1 of 50" or "Showing 1-20 of 963 results". To do that, you need the total count of items.

You usually compute this in SQL with COUNT(*):

sql 
SELECT COUNT(*) FROM products WHERE category_id = :category;

Then your backend can include it in the response.

However, counting can be expensive on huge tables. Some strategies:

Example of optional count in a request:

http 
GET /products?limit=20&offset=0&include_total=true

Response:

json 
{
  "items": [...],
  "limit": 20,
  "offset": 0,
  "total": 1234
}

Without include_total, you may omit the total field to save work.

Pagination and Sorting

Pagination always depends on ordering. You need a consistent ORDER BY in your queries, or the pages will not be stable.

For example, you might allow clients to control the sort:

http 
GET /users?sort=created_at&order=desc&limit=20

Internally:

sql 
SELECT *
FROM users
ORDER BY created_at DESC
LIMIT :limit OFFSET :offset;

Stable Ordering

To avoid random ordering, always include a tie-breaker in your order:

sql 
ORDER BY created_at DESC, id DESC

If two rows have the same created_at, the id ensures they appear in a consistent order.

For cursor-based pagination with timestamps, a common trick:

sql 
SELECT *
FROM messages
WHERE (created_at, id) > (:cursor_created_at, :cursor_id)
ORDER BY created_at, id
LIMIT :limit;

This uses a composite condition to handle duplicate timestamps:

Key rule:
Pagination without a deterministic and stable sort order can cause duplicates and missing items.
Always define and document the ordering used by your paginated endpoints.

Combining Pagination with Filtering

In real APIs, lists are almost always filtered and sorted.

Example:

http 
GET /orders?status=pending&sort=created_at&order=desc&limit=20&offset=0

Your backend will:

  1. Apply filters (status=pending).
  2. Apply sorting (ORDER BY created_at DESC, id DESC).
  3. Apply pagination (LIMIT 20 OFFSET 0).

If you include total, it should respect the same filters:

sql 
SELECT COUNT(*)
FROM orders
WHERE status = 'pending';

This means "total number of pending orders," not total orders overall.

The important rule: Pagination is always applied after filters and sorting. The full process is:

  1. Start with all records.
  2. Filter by query parameters.
  3. Sort by requested order.
  4. Page the results, for example first 20 items.

Limits, Defaults, and Protection

Poorly controlled pagination can cause load problems. For example, if a client does:

http 
GET /logs?limit=1000000

The server may:

To avoid that, you should:

Example of a validation error response:

json 
{
  "detail": "Invalid limit. Must be between 1 and 100."
}

If the client sends a negative or zero page or offset, you can:

Either behavior is OK, but be consistent and document it.

Example: Designing a Paginated Endpoint

Imagine a REST endpoint for listing blog posts.

Request format

http 
GET /posts?tag=python&sort=published_at&order=desc&page=2&page_size=5

Meaning:

Backend logic outline

  1. Read query parameters, with defaults:
    • tag: optional.
    • sort: default published_at.
    • order: default desc.
    • page: default 1.
    • page_size: default 10, max 100.
  2. Validate parameters.
  3. Build SQL query:
sql 
   SELECT *
   FROM posts
   WHERE (:tag IS NULL OR :tag = ANY(tags))
   ORDER BY published_at DESC, id DESC
   LIMIT :page_size
   OFFSET (:page - 1) * :page_size;
  1. Optionally, count total:
sql 
   SELECT COUNT(*)
   FROM posts
   WHERE (:tag IS NULL OR :tag = ANY(tags));
  1. Build JSON response:
json 
   {
     "items": [...],
     "page": 2,
     "page_size": 5,
     "total": 23,
     "total_pages": 5,
     "has_next": true,
     "has_prev": true,
     "links": {
       "self": "/posts?tag=python&sort=published_at&order=desc&page=2&page_size=5",
       "next": "/posts?tag=python&sort=published_at&order=desc&page=3&page_size=5",
       "prev": "/posts?tag=python&sort=published_at&order=desc&page=1&page_size=5"
     }
   }

This is a typical pattern for many REST APIs.

Choosing a Pagination Strategy

Different applications benefit from different strategies.

SituationRecommended approach
Admin dashboard, small datasetsPage-based or offset-based
Public API with search resultsPage-based, maybe cursor for heavy use
Real-time feeds, chat, timelinesCursor-based
Very large tables (millions of rows)Cursor-based, or keyset pagination
Simple internal toolsOffset-based

For many beginner backend projects, starting with page-based pagination is enough. As data grows or you build more advanced systems, you can introduce cursor-based pagination where needed.

Summary

With these concepts, you can design REST endpoints that remain fast and usable, even as your data grows.

Views: 7

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3102 / 24h
Impressum / Imprint
Datenschutz / Data Policy