API Pagination at Scale: Offset vs Cursor Pagination

Why Page 5000 Becomes Slow

Imagine we have an article API:

GET /articles?page=5001&pageSize=20&category=database

If each page contains 20 items, page 5001 means PostgreSQL needs to skip:

5000 × 20
= 100,000 rows

The SQL may look like:

SELECT id, title, category, created_at
FROM articles
WHERE tenant_id = $1
  AND category = $2
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

At first glance this looks fine.

We only need 20 rows.

But PostgreSQL cannot simply jump directly to row 100,001.

It still needs to walk through the earlier matching rows and discard them.

Conceptually:

Find matching rows
 
A
B
C
D
...
100,000 rows
 
↓ discard them
 
Then return:
 
100001
100002
...
100020

So the deeper the page becomes, the more work the database may need to do.

This is the first major problem with offset pagination.

But performance is not the only problem.

The second problem is that rows can move between requests.

How Offset Pagination Works

Offset pagination usually looks like this:

GET /articles?page=1&pageSize=20

Then:

GET /articles?page=2&pageSize=20

Internally:

Page 1
OFFSET 0
LIMIT 20
 
Page 2
OFFSET 20
LIMIT 20
 
Page 3
OFFSET 40
LIMIT 20

The formula is:

offset = (page - 1) × pageSize

For example:

page = 5
pageSize = 20
 
offset = (5 - 1) × 20
       = 80

The query becomes:

LIMIT 20 OFFSET 80;

Offset pagination is easy to understand.

That is why it is used so often.

The Real Problem: Results Move

Suppose we have six articles ordered newest first:

A B C D E F

Page size is three.

Page 1:

A B C

The query is:

LIMIT 3 OFFSET 0;

Now a new article X is published.

The dataset becomes:

X A B C D E F

The client requests page 2:

LIMIT 3 OFFSET 3;

The first three rows are now:

X A B

so PostgreSQL skips them.

It returns:

C D E

The user has now seen:

Page 1:
A B C
 
Page 2:
C D E

C appears twice.

Deletions Can Cause Missing Rows

Now consider the opposite case.

Initial list:

A B C D E F

Page 1:

A B C

Before requesting page 2, B is deleted.

The list becomes:

A C D E F

Page 2 still uses:

OFFSET 3

So PostgreSQL skips:

A C D

and returns:

E F

The client never sees:

So with offset pagination, concurrent inserts and deletes can cause:

duplicates
or
missing rows

This happens because an offset represents:

"the row currently at position N"

and that position can move.

Offset Pagination Is Not Bad

This does not mean offset pagination is wrong.

It works very well for many applications.

Offset pagination is usually a good choice when:

dataset is not very large
users want page numbers
data changes slowly
deep pages are uncommon
admin tables need direct page jumps
simple implementation is important

Examples:

Admin dashboard
User management table
Audit history with a modest dataset
Product catalog with limited results
Internal reporting screen

If a table has 2,000 rows, offset pagination may be completely fine.

You do not need cursor pagination everywhere.

A Safe Offset Pagination Parser

Never trust:

page
pageSize

directly from the client.

For example:

GET /articles?page=-100&pageSize=999999999

We should validate them.

function parseOffsetPage(searchParams: URLSearchParams) {
  const page = Number(searchParams.get("page") ?? "1");
 
  const requestedSize = Number(searchParams.get("pageSize") ?? "20");
 
  if (!Number.isSafeInteger(page) || page < 1) {
    throw new InvalidPaginationError("page must be a positive integer");
  }
 
  if (!Number.isSafeInteger(requestedSize) || requestedSize < 1) {
    throw new InvalidPaginationError("pageSize must be a positive integer");
  }
 
  const pageSize = Math.min(requestedSize, 100);
 
  const offset = (page - 1) * pageSize;
 
  if (!Number.isSafeInteger(offset) || offset > 100_000) {
    throw new InvalidPaginationError("requested page is too deep");
  }
 
  return {
    page,
    pageSize,
    offset,
  };
}

Here we limit:

pageSize ≤ 100
offset ≤ 100,000

This protects the database from unreasonable requests.

Some systems do this:

small / normal pages
→ offset pagination
 
very large export
→ background export job

That is often better than allowing:

OFFSET 10,000,000

Cursor Pagination

Cursor pagination solves a different problem.

Instead of saying:

Skip the first 100,000 rows.

the client says:

Continue after this specific row.

This is also called:

Keyset pagination
Seek pagination
Cursor pagination

They are closely related ideas.

Offset vs Cursor Mental Model

Offset pagination:

Give me rows 100001 → 100020

Cursor pagination:

Give me the next 20 rows
after this exact article.

That difference is very important.

First Cursor Page

Suppose articles are ordered:

ORDER BY created_at DESC, id DESC

For the first page:

SELECT id, title, category, created_at
FROM articles
WHERE tenant_id = $1
  AND category = $2
ORDER BY created_at DESC, id DESC
LIMIT 21;

Why 21?

Because the requested page size is:

but we fetch:

20 + 1

The extra row tells us whether another page exists.

For example:

Database returned 21 rows
        ↓
Return first 20
        ↓
hasNextPage = true

If only 17 rows were returned:

hasNextPage = false

This avoids running:

COUNT(*)

just to know whether more rows exist.

The Next Page

Suppose the last row returned on page 1 is:

created_at = 2026-09-22T12:00:00Z
id         = article-500

Instead of:

OFFSET 20

we ask:

AND (created_at, id) <
    ($createdAt, $id)

Full query:

SELECT id, title, category, created_at
FROM articles
WHERE tenant_id = $1
  AND category = $2
  AND (created_at, id) < ($3, $4)
ORDER BY created_at DESC, id DESC
LIMIT 21;

This means:

Give me rows older than
this exact article.

The database does not need to walk through every previous page.

With the right index it can seek directly near the boundary.

Why Cursor Pagination Scales Better

Imagine:

Page 1
Page 100
Page 5000

Offset pagination may do increasingly more work:

Page 1
skip 0
 
Page 100
skip 1,980
 
Page 5000
skip 99,980

Cursor pagination stays closer to:

Find boundary
→ read next ~20 rows

Conceptually:

Offset
 
cost ≈ skipped rows + page size

while cursor pagination is closer to:

Cursor
 
cost ≈ finding boundary + page size

assuming the proper index exists.

Cursor Pagination Needs Stable Ordering

This is one of the most important rules.

Suppose we order only by:

ORDER BY created_at DESC

But several articles were created at exactly the same timestamp:

12:00:00 article-A
12:00:00 article-B
12:00:00 article-C

Now imagine the cursor contains only:

12:00:00

What does:

continue after 12:00:00

mean?

After A?

After B?

After C?

The boundary is ambiguous.

Add a Unique Tie-Breaker

Use:

ORDER BY created_at DESC, id DESC

Now every row has a deterministic position.

Example:

12:00 article-C
12:00 article-B
12:00 article-A
11:59 article-Z

The cursor contains:

created_at
+
id

So it can describe the exact boundary.

The important rule is:

Cursor ordering should be deterministic and unique.

A common pattern is:

created_at + id

Does the ID Need to Be Sequential?

No.

A UUID works as a tie-breaker.

For example:

created_at
↓
primary ordering
 
UUID
↓
unique tie-breaker

The UUID does not need to represent time.

Its job is simply to make the ordering deterministic when timestamps are equal.

Good Cursor Ordering Columns

Ordering columns should ideally be:

non-null
stable
comparable
indexed

Most importantly:

avoid changing them during pagination

For example:

created_at

is normally safer than:

updated_at

because updated_at changes often.

If the ordering value changes, the row can move to another part of the result set.

Ordering and Cursor Predicate Must Match

Suppose we use:

ORDER BY created_at DESC, id DESC

Then forward pagination can use:

(created_at, id) < ($createdAt, $id)

because we are moving toward older/smaller values.

Conceptually:

Newest
↓
Older
↓
Older

If we instead used ascending ordering:

ORDER BY created_at ASC, id ASC

the direction would reverse:

(created_at, id) > (...)

Your cursor predicate must exactly match your sort order.

Indexes Are Critical

Cursor pagination is not magically fast by itself.

The database still needs the right index.

Suppose the query is:

WHERE tenant_id = ?
ORDER BY created_at DESC, id DESC

A useful index is:

CREATE INDEX articles_tenant_feed_idx
ON articles (
  tenant_id,
  created_at DESC,
  id DESC
);

Now PostgreSQL can efficiently find:

tenant's rows
already ordered correctly

Filtering Changes the Index

Suppose we also filter by category:

WHERE tenant_id = ?
AND category = ?
ORDER BY created_at DESC, id DESC

Then another useful index is:

CREATE INDEX articles_tenant_category_feed_idx
ON articles (
  tenant_id,
  category,
  created_at DESC,
  id DESC
);

This index supports:

tenant
+
category
+
ordering

One Index Does Not Always Handle Every Query

Imagine this index:

(
  tenant_id,
  category,
  created_at DESC,
  id DESC
)

It works well when we query:

tenant + category

But what about:

tenant only

with no category filter?

The category column sits between:

tenant_id

and:

created_at

so PostgreSQL may not be able to use the index as efficiently for global tenant ordering.

This is why indexing should follow real query patterns.

Do not create indexes only because they "look right."

Test them.

Verify With EXPLAIN ANALYZE

For example:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, category, created_at
FROM articles
WHERE tenant_id = 'tenant-1'
  AND category = 'database'
  AND (created_at, id) < (
    '2026-09-22T12:00:00.000Z',
    '75bd0700-518a-4cc0-a7f2-8a4cda8cb56b'
  )
ORDER BY created_at DESC, id DESC
LIMIT 21;

Look at things such as:

rows visited
rows returned
buffer reads
sorting
execution time
actual rows
estimated rows

The goal is not just:

"An index exists."

The goal is:

"The database actually uses an efficient plan
for our production query."

What Is a Cursor?

A cursor is simply enough information to continue the query.

For our article API:

type ArticleBoundary = {
  createdAt: string;
  id: string;
};

A more complete cursor might contain:

type ArticleCursorV1 = {
  version: 1;
 
  direction: "after" | "before";
 
  boundary: ArticleBoundary;
 
  snapshot: ArticleBoundary;
 
  filterHash: string;
};

Let's understand each field.

boundary

Example:

boundary: {
  createdAt:
    "2026-09-22T12:00:00Z",
 
  id:
    "article-500",
}

Meaning:

Continue pagination from here.

direction

direction: "after";

means:

load older / next rows

while:

direction: "before";

means:

load newer / previous rows

This prevents accidentally using:

nextCursor

as:

previousCursor

version

version: 1;

Cursor formats may change later.

For example:

v1 cursor
createdAt + id
 
v2 cursor
createdAt + id + snapshot + filterHash

Versioning allows the server to understand how to decode old cursors.

snapshot

A snapshot boundary can help when we want a bounded traversal.

For example:

Export all articles that existed
when the export started.

We will return to this later.

filterHash

This protects against using a cursor with different filters.

Example:

The cursor was created for:

GET /articles?category=database

The client should not reuse it for:

GET /articles?category=backend

Because the cursor boundary belongs to a different result set.

Why Cursors Should Be Opaque

Clients should not need to understand the cursor structure.

Instead of exposing:

createdAt=...
id=...
direction=...

we can return something like:

eyJ2ZXJzaW9uIjoxLCJkaXJlY3Rpb24iOiJhZnRlciJ9...

The client treats it as:

opaque string

It only sends it back to the server.

This gives the server freedom to change the internal cursor representation later.

Base64 Is Not Security

Many systems encode cursor JSON with Base64.

For example:

JSON
↓
Base64URL
↓
cursor

But Base64 does not protect against tampering.

A client can decode it, modify the values, and encode it again.

So if we care about integrity, we can sign the cursor.

Signing Cursors With HMAC

Node provides:

createHmac;

We can create:

encoded payload
+
signature

Conceptually:

cursor =
payload.signature

Example:

import { createHmac, timingSafeEqual } from "node:crypto";

Encoding:

function base64UrlEncode(value: string | Buffer): string {
  return Buffer.from(value).toString("base64url");
}

Create cursor:

function encodeArticleCursor(payload: ArticleCursorV1, secret: Buffer): string {
  const encodedPayload = base64UrlEncode(JSON.stringify(payload));
 
  const signature = createHmac("sha256", secret)
    .update(encodedPayload)
    .digest("base64url");
 
  return `${encodedPayload}.${signature}`;
}

Now if the client changes the payload, the signature no longer matches.

Cursor Signing Does Not Encrypt the Data

This is important.

Signing means:

Client cannot modify cursor
without detection.

It does not mean:

Client cannot read cursor.

The Base64 payload can still be decoded.

So never put secrets in it.

Bad cursor contents:

password
access token
private user details
secret database values

Keep cursor state minimal.

Validate Decoded Cursors

Never do this:

const cursor =
  JSON.parse(decoded)
    as ArticleCursorV1;

because:

as ArticleCursorV1

does not perform runtime validation.

A malicious client could send:

{
  "version": "hello",
  "direction": 500,
  "boundary": null
}

The server must validate:

cursor version
direction
timestamp format
ID format
string lengths
required fields

Treat cursor contents exactly like any other untrusted client input.

Limit Cursor Size

Do not allow:

10 MB cursor

For example:

const MAXIMUM_CURSOR_LENGTH = 2048;

Then reject anything larger.

This limits unnecessary:

memory use
decoding work
signature work
JSON parsing

Bind the Cursor to Filters

Suppose the first request is:

GET /articles?category=database

The cursor represents a position in:

database articles

Now the client sends:

GET /articles?category=backend&after=CURSOR

If we silently accept that cursor, the pagination boundary no longer belongs to the same dataset.

A safer approach is to hash the filters.

Build a Filter Hash

Example:

function articleFilterHash(input: {
  tenantId: string;
  category: string | null;
}) {
  const canonical = JSON.stringify({
    tenantId: input.tenantId,
 
    category: input.category,
 
    sort: "created_at:desc,id:desc",
  });
 
  return createHash("sha256").update(canonical).digest("base64url");
}

The cursor stores:

filterHash

On the next request:

recalculate filter hash
↓
compare with cursor

If different:

reject cursor

Bind the Cursor to the Tenant

For a multi-tenant system, this is especially important.

Imagine:

Tenant A cursor

being used in:

Tenant B request

The server must never trust tenant data inside the cursor.

Tenant ID should come from:

authenticated server state

For example:

subject.tenantId;

not:

request.query.tenantId;

or a client-controlled cursor.

The cursor can contain a hash based on the tenant so the server detects misuse.

Forward Pagination

Suppose ordering is:

ORDER BY created_at DESC, id DESC

To load the next / older page:

AND (created_at, id) < ($3, $4)
ORDER BY created_at DESC, id DESC
LIMIT $5;

Conceptually:

Newest
 
A
B
C  ← cursor
 
D
E
F  ← next page

Backward Pagination

Now suppose the user wants to go back toward newer rows.

We need:

AND (created_at, id) > ($3, $4)

But there is a subtle problem.

We want the rows closest to the boundary.

So the SQL usually reverses the ordering:

ORDER BY created_at ASC, id ASC

Example:

SELECT ...
FROM articles
WHERE ...
  AND (created_at, id) > ($3, $4)
ORDER BY created_at ASC, id ASC
LIMIT 21;

Then application code reverses the result before returning it.

Why Reverse the SQL?

Suppose normal order is:

Newest
 
A
B
C
D
E
F
 
Oldest

We are currently around:

To get the closest newer rows, we need:

C
B
A

Using ascending database order lets PostgreSQL seek near D and collect the closest rows efficiently.

Then we reverse:

A
B
C

before sending them to the client.

This keeps the API response consistently newest-first.

Do You Need Backward Pagination?

Not always.

For infinite scroll:

Load more
Load more
Load more

you may only need:

nextCursor

Supporting both directions adds complexity.

So:

Do not implement backward pagination unless the product actually needs it.

Complete Request Flow

The handler might accept:

limit
category
after
before

Example:

GET /articles?limit=20&category=database&after=CURSOR

The application should reject:

?after=CURSOR_A&before=CURSOR_B

because the direction is ambiguous.

Example TypeScript Handler

type ListArticlesInput = {
  tenantId: string;
  category: string | null;
  limit: number;
  cursor: ArticleCursorV1 | null;
};

Handler:

export async function getArticles(request: Request): Promise<Response> {
  const requestId = request.headers.get("x-request-id") ?? crypto.randomUUID();
 
  try {
    const subject = await authenticate(request);
 
    authorization.require(subject, "article:list");
 
    const url = new URL(request.url);
 
    const limit = parseBoundedInteger(url.searchParams.get("limit"), {
      defaultValue: 20,
      minimum: 1,
      maximum: 100,
    });
 
    const category = normalizeCategory(url.searchParams.get("category"));
 
    const after = url.searchParams.get("after");
 
    const before = url.searchParams.get("before");
 
    if (after && before) {
      throw new InvalidPaginationError("Use either after or before, not both");
    }
 
    const encodedCursor = after ?? before;
 
    const cursor = encodedCursor
      ? decodeArticleCursor(encodedCursor, cursorSigningKey)
      : null;
 
    const expectedDirection = after ? "after" : before ? "before" : null;
 
    if (cursor && cursor.direction !== expectedDirection) {
      throw new InvalidCursorError();
    }
 
    const filterHash = articleFilterHash({
      tenantId: subject.tenantId,
 
      category,
    });
 
    if (cursor && cursor.filterHash !== filterHash) {
      throw new InvalidCursorError();
    }
 
    const page = await articleRepository.list({
      tenantId: subject.tenantId,
 
      category,
      limit,
      cursor,
    });
 
    return Response.json(
      {
        items: page.items,
 
        pageInfo: {
          nextCursor: page.nextCursor,
 
          previousCursor: page.previousCursor,
 
          hasNextPage: page.hasNextPage,
 
          hasPreviousPage: page.hasPreviousPage,
        },
      },
 
      {
        headers: {
          "Cache-Control": "private, no-store",
 
          "X-Request-Id": requestId,
        },
      },
    );
  } catch (error) {
    const problem = mapPaginationError(error, requestId);
 
    return Response.json(problem, {
      status: problem.status,
 
      headers: {
        "Content-Type": "application/problem+json",
 
        "X-Request-Id": requestId,
      },
    });
  }
}

The HTTP layer handles:

authentication
authorization
query validation
cursor validation
error responses

while the repository handles:

SQL
indexes
seek predicates
limit + 1
cursor boundaries

Stable Invalid Cursor Errors

Suppose a cursor fails because:

signature is invalid
tenant changed
filter changed
version unsupported
payload malformed

Do not reveal which validation failed.

Return one public error:

{
  "type": "https://api.example.com/problems/invalid-cursor",
  "title": "The pagination cursor is invalid",
  "status": 400,
  "code": "invalid_cursor",
  "requestId": "req-01K5"
}

From the client's perspective:

cursor invalid

is enough.

Detailed internal reasons can be logged securely if needed.

How to Build pageInfo

Suppose forward pagination fetches:

limit + 1

For:

limit = 20

we fetch:

If 21 rows return:

hasNextPage = true

We return only 20.

The next cursor comes from:

last returned item

Forward Page Info

For an after request:

hasNextPage
=
database returned more than limit
 
hasPreviousPage
=
an after cursor was provided
 
nextCursor
=
cursor after the final returned row
 
previousCursor
=
cursor before the first returned row

Backward Page Info

For a before request:

hasPreviousPage
=
database returned more than limit
 
hasNextPage
=
a before cursor was provided

After reversing results into normal API order:

previousCursor
=
before first result
 
nextCursor
=
after last result

Cursor semantics should be tested carefully.

Small bugs here can cause:

duplicate pages
loops
missing items
wrong direction

What If the Cursor Row Was Deleted?

Suppose page 1 returned:

A B C

and the cursor represents:

Then C gets deleted.

Can the next query still work?

Yes, because keyset pagination normally does not need to find the actual cursor row.

It asks:

(created_at, id) <
(cursor_created_at, cursor_id)

The cursor represents a boundary value.

The row itself does not have to still exist.

Empty Pages Can Be Valid

Because rows can be deleted or filters can change, a cursor may sometimes lead to:

0 rows

That does not automatically mean the cursor is invalid.

Return:

{
  "items": [],
  "pageInfo": {
    "hasNextPage": false
  }
}

according to your contract.

Do not treat every empty result as malformed pagination.

What Happens When New Rows Are Inserted?

Suppose the original feed is:

A
B
C
D
E

The client reads:

A
B
C

Now a new article appears:

X
A
B
C
D
E

The cursor says:

continue after C

So the next page still returns:

D
E

The new article X does not shift the boundary.

This is one of the big advantages over offset pagination.

The user can see X when they refresh the feed from the beginning.

Cursor Pagination Does Not Freeze the Dataset

This is a very important distinction.

A cursor says:

Continue from this position.

It does not say:

Show me the exact database
as it looked five minutes ago.

Rows may still:

be deleted
change category
change permissions
change ordering values

Cursor pagination improves traversal stability.

It is not a database snapshot.

What If an Ordering Value Changes?

Suppose we paginate by:

updated_at

An article initially appears here:

Page 3

Then someone edits the article.

Its updated_at changes.

Now it moves near the top:

Page 1

During the same traversal it could:

appear twice
or
be missed

This is why stable or immutable ordering columns are better.

Common choice:

created_at
+
id

rather than:

updated_at

Filters Can Also Change

Suppose we are traversing:

GET /articles?category=backend

An article is currently:

category = backend

Then someone changes it to:

category = database

It leaves the result set.

Cursor pagination cannot prevent that.

Likewise:

draft
→ published

may cause a row to enter the result set.

The API should enforce current state and permissions on every request.

Correct authorization is more important than preserving a perfectly frozen pagination experience.

Live Feed vs Snapshot Traversal

Different products want different behavior.

For a social/news feed:

new content appears
when the user refreshes

That is desirable.

For a large export:

Export every article
that existed when export started

we may want a more stable boundary.

This is where a snapshot upper bound helps.

Snapshot-Bounded Cursor

Suppose the newest row at the start is:

created_at = 12:00
id         = article-A

Store it in the cursor:

snapshot: {
  createdAt:
    "2026-09-22T12:00:00Z",
 
  id:
    "article-A",
}

Then every page includes:

AND (created_at, id)
    <= ($snapshotCreatedAt, $snapshotId)

Now new rows created after the traversal began are excluded.

Why Store the Full Snapshot Tuple?

Do not store only:

created_at = 12:00

because several rows may share the same timestamp.

Store:

created_at
+
id

This represents an exact upper boundary.

Snapshot-Bounded Pagination Still Is Not a True Snapshot

Even with:

(created_at, id) <= snapshot

rows can still:

be deleted
change values
change permissions

So this gives us:

bounded new inserts

not:

perfect historical consistency

For a true historical export, you may need:

database snapshot
temporal data
export table
materialized dataset
background export job

Do Not Keep One Database Transaction Open Across HTTP Pages

A tempting idea is:

Start transaction
↓
page 1
↓
wait for user
↓
page 2
↓
wait 30 seconds
↓
page 3
↓
commit

Do not do this.

HTTP clients may take minutes between pages.

Long-running transactions can cause:

resource consumption
vacuum issues
locks
connection exhaustion
long snapshots

Pagination should normally work with independent requests.

For repeatable exports, use a dedicated export architecture instead.

Read Replicas Add Another Consistency Problem

Suppose page 1 goes to replica A.

Replica A has received database changes up to:

position 500

Page 2 goes to replica B.

Replica B is behind:

position 470

Now page 2 may not know about some rows that page 1 already saw.

This can create confusing traversal behavior even with a correct cursor.

Options for Replica Consistency

If stronger traversal consistency matters, consider:

replica affinity

Meaning:

same user/session
→ same replica

or:

replication position token

Meaning the next replica must be at least as caught up as the previous read.

Other options:

route traversal to primary
wait for replica catch-up
document eventual consistency

The correct solution depends on how strong the product's consistency requirement is.

Exact Counts Can Be Expensive

Offset pagination often comes with:

Page 1 of 12,537

To calculate that, the server may run:

SELECT COUNT(*)
FROM articles
WHERE tenant_id = $1
  AND category = $2;

For large filtered tables, an exact count may be expensive.

Ask whether the product actually needs it.

Alternatives to Exact Counts

For infinite scroll:

Load more

you may only need:

hasNextPage

Other options include:

cached count
estimated count
precomputed aggregate
separate count endpoint
background export count

Do not automatically run an expensive:

COUNT(*)

on every page if nobody needs the result.

Pagination Is Also a Security Boundary

Every client-controlled value should be bounded.

Examples:

limit
cursor length
offset
filters
search terms
sort modes

Bad request:

GET /articles?limit=10000000

Another:

GET /articles?sort=random-database-expression

Do not allow arbitrary SQL ordering.

Whitelist Sort Modes

Instead of:

ORDER BY ${request.query.sort}

define supported sort modes:

newest
oldest
title

Then map them server-side.

Example:

const sortModes = {
  newest: "created_at DESC, id DESC",
 
  oldest: "created_at ASC, id ASC",
};

Only use known safe options.

This helps prevent:

SQL injection
unexpected slow queries
unindexed sort patterns

Caching Multi-Tenant Lists

Be careful caching:

GET /articles

if the response depends on:

authenticated tenant
user permissions
private filters

A shared cache must never accidentally return:

Tenant A data

to:

Tenant B

Unless caching is designed very carefully, authenticated paginated endpoints are often safer with:

Cache-Control: private, no-store

Testing Pagination Properly

Pagination should be tested with more than:

"response has 20 items"

That is not enough.

You need to test traversal behavior.

Important Test Cases

Test:

1. Empty result
 
2. One row
 
3. Exactly one page
 
4. Page size + 1 rows
 
5. Duplicate timestamps
 
6. Invalid page size
 
7. Oversized cursor
 
8. Malformed Base64
 
9. Invalid cursor signature
 
10. Unsupported cursor version
 
11. Cursor from another tenant
 
12. Cursor used with different filters
 
13. Forward traversal
 
14. Forward then backward traversal
 
15. New insert before cursor
 
16. Row deletion
 
17. Ordering-key update
 
18. Snapshot boundary
 
19. Maximum page size
 
20. Query plan with realistic data

Test IDs Across the Entire Traversal

Suppose the dataset contains:

Page size:

Do not only test:

every response has 3 items

Collect all IDs:

Page 1 → 1 2 3
Page 2 → 4 5 6
Page 3 → 7 8 9

Then verify:

no unexpected duplicates
no unexpected missing IDs
correct ordering

This catches bugs that individual page assertions miss.

Use Real PostgreSQL for Query Tests

Mocking a repository cannot verify:

index behavior
tuple comparison
query plan
sort behavior
PostgreSQL ordering semantics

For pagination SQL, use integration tests with a real PostgreSQL instance.

Unit tests are useful for:

cursor encoding
cursor validation
filter hash
pageInfo logic

Database integration tests should verify:

real keyset query behavior

Observability

Pagination should be observable in production.

Useful metrics include:

offset request count
cursor request count
page size
deepest offset
query duration
invalid cursor rate
empty page rate
count query latency
buffer reads
sort spills
replica fallback

This lets us answer questions like:

Are people requesting page 10,000?
 
Are cursor queries still fast?
 
Are clients frequently sending invalid cursors?
 
Is COUNT(*) becoming expensive?

Avoid High-Cardinality Metric Labels

Good labels:

route
strategy
status
page-size bucket

Bad labels:

cursor
userId
tenantId
raw URL
search text

Why?

Because values like:

userId
cursor

may have millions of unique combinations.

That can severely increase metrics storage and memory usage.

Use logs or analytics when exact identity is needed.

Offset vs Cursor Comparison

Requirement	Offset	Cursor
Easy to implement	Excellent	More complex
Page numbers	Excellent	Poor
Jump directly to page 500	Easy	Difficult
Deep-page performance	Gets worse	Usually stable
Frequently changing feed	Can duplicate/skip	More stable
Infinite scroll	Fine at small scale	Excellent
Backward navigation	Easy	More complex
Arbitrary sorting	Easier	Requires matching cursor/index
Exact page counts	Natural	Separate concern
Large sequential export	Weak	Better
Concurrent inserts	Positional movement	Better boundary stability

When to Use Offset Pagination

Offset is a good choice when:

dataset is modest

and:

users need page numbers

and:

deep pagination is uncommon

Example:

Admin users table
 
Previous
1 2 3 4 5
Next

Offset is simple and appropriate here.

When to Use Cursor Pagination

Cursor pagination is a strong choice when:

dataset is large
data changes frequently
users traverse sequentially
deep pagination happens

Examples:

social feed
notification feed
activity timeline
chat history
large article feed
transaction history
event stream

Especially when the UI looks like:

Load more

or:

Infinite scroll

Do Not Use Cursor Pagination Just Because It Sounds Advanced

Cursor pagination introduces extra complexity:

cursor format
signing
validation
indexes
backward traversal
filter binding
snapshot semantics

If your admin screen contains:

1,500 rows

and users need:

Page 1
Page 2
Page 15

offset pagination may be the better design.

Choose based on requirements, not architecture fashion.

A Practical Decision Process

When building pagination, ask:

1. How large can this dataset become?

Then:

2. Do users need direct page numbers?

Then:

3. Does data change frequently?

Then:

4. Will users navigate very deep?

Then:

5. Do we need exact total pages?

Then:

6. Is navigation sequential?

If the answer looks like:

small dataset
+
page numbers
+
rare changes

use:

offset

If the answer looks like:

large dataset
+
frequent writes
+
load more
+
deep traversal

use:

cursor/keyset

A Good Production Cursor Flow

A mature cursor pagination flow looks like this:

Request
 
GET /articles
?limit=20
&category=database
&after=CURSOR
 
        ↓
 
Authenticate user
 
        ↓
 
Determine tenant
 
        ↓
 
Validate limit
 
        ↓
 
Normalize filters
 
        ↓
 
Decode cursor
 
        ↓
 
Verify signature
 
        ↓
 
Validate cursor schema
 
        ↓
 
Verify direction
 
        ↓
 
Verify filter + tenant hash
 
        ↓
 
Run keyset query
 
        ↓
 
Fetch limit + 1
 
        ↓
 
Build pageInfo
 
        ↓
 
Generate signed cursors
 
        ↓
 
Return response

The important part is not the Base64 cursor.

The important part is that the cursor always represents a valid position in the exact query the client is continuing.

Production Checklist

Before shipping cursor pagination:

□ Define exact ordering.
 
□ Add a unique tie-breaker.
 
□ Prefer stable ordering columns.
 
□ Make ordering columns non-null.
 
□ Match the cursor predicate
  with the sort direction.
 
□ Create indexes matching
  filters + ordering.
 
□ Test with EXPLAIN ANALYZE.
 
□ Fetch limit + 1.
 
□ Avoid COUNT(*) unless needed.
 
□ Keep cursor opaque.
 
□ Version cursor format.
 
□ Sign cursors when integrity matters.
 
□ Validate decoded cursor data.
 
□ Limit cursor size.
 
□ Bind cursor to tenant.
 
□ Bind cursor to filters.
 
□ Bind cursor to sort mode.
 
□ Reject wrong direction.
 
□ Use authenticated tenant identity.
 
□ Support backward pagination
  only if needed.
 
□ Test duplicate timestamps.
 
□ Test concurrent inserts.
 
□ Test deletes.
 
□ Test ordering-key changes.
 
□ Define live vs snapshot behavior.
 
□ Consider replica consistency.
 
□ Bound limit and offset.
 
□ Whitelist sort modes.
 
□ Protect tenant-specific caching.
 
□ Test with real PostgreSQL.
 
□ Monitor deep offsets.
 
□ Monitor query latency.
 
□ Monitor invalid cursors.

A Simple Way to Remember the Difference

Offset pagination asks:

Where is row number N right now?

Cursor pagination asks:

What comes after this exact row in this ordering?

That is the core difference.

Offset is based on:

position

Cursor pagination is based on:

ordering boundary

Conclusion

Offset pagination is simple and useful.

For modest datasets, admin tables, and interfaces that need page numbers, it is often the correct solution.

Its main limitation is that deeper pages require more work:

OFFSET 100
OFFSET 10,000
OFFSET 100,000

and concurrent inserts or deletes can move row positions.

Cursor pagination uses a different idea.

Instead of:

skip 100,000 rows

it says:

continue after this exact row

With a deterministic ordering such as:

ORDER BY created_at DESC, id DESC

and a matching index:

(
  tenant_id,
  category,
  created_at DESC,
  id DESC
)

the database can seek directly from the previous boundary.

But production cursor pagination requires more than simply Base64-encoding an ID.

You need to think about:

deterministic ordering
tie-breakers
indexes
cursor validation
cursor signing
tenant isolation
filter binding
backward navigation
concurrent writes
snapshot behavior
replica consistency
exact counts
testing
observability

The final rule is simple:

Need page numbers
+ moderate dataset
        ↓
Offset pagination
 
Need scalable sequential traversal
+ large/changing dataset
        ↓
Cursor / keyset pagination

Choose the simplest pagination strategy that satisfies the consistency, navigation, and performance requirements of the product.

Pagination is one part of the wider API contract. The production-ready REST API guide shows how it fits with validation, caching, concurrency control, and safe evolution.

Why Page 5000 Becomes Slow#

How Offset Pagination Works#

The Real Problem: Results Move#

Deletions Can Cause Missing Rows#

Offset Pagination Is Not Bad#

A Safe Offset Pagination Parser#

Cursor Pagination#

Offset vs Cursor Mental Model#

First Cursor Page#

The Next Page#

Why Cursor Pagination Scales Better#

Cursor Pagination Needs Stable Ordering#

Add a Unique Tie-Breaker#

Does the ID Need to Be Sequential?#

Good Cursor Ordering Columns#

Ordering and Cursor Predicate Must Match#

Indexes Are Critical#

Filtering Changes the Index#

One Index Does Not Always Handle Every Query#

Verify With EXPLAIN ANALYZE#

What Is a Cursor?#

boundary#

direction#

version#

snapshot#

filterHash#

Why Cursors Should Be Opaque#

Base64 Is Not Security#

Signing Cursors With HMAC#

Cursor Signing Does Not Encrypt the Data#

Validate Decoded Cursors#

Limit Cursor Size#

Bind the Cursor to Filters#

Build a Filter Hash#

Bind the Cursor to the Tenant#

Forward Pagination#

Backward Pagination#

Why Reverse the SQL?#

Do You Need Backward Pagination?#

Complete Request Flow#

Example TypeScript Handler#

Stable Invalid Cursor Errors#

How to Build pageInfo#

Forward Page Info#

Backward Page Info#

What If the Cursor Row Was Deleted?#

Empty Pages Can Be Valid#

What Happens When New Rows Are Inserted?#

Cursor Pagination Does Not Freeze the Dataset#

What If an Ordering Value Changes?#

Filters Can Also Change#

Live Feed vs Snapshot Traversal#

Snapshot-Bounded Cursor#

Why Store the Full Snapshot Tuple?#

Snapshot-Bounded Pagination Still Is Not a True Snapshot#

Do Not Keep One Database Transaction Open Across HTTP Pages#

Read Replicas Add Another Consistency Problem#

Options for Replica Consistency#

Exact Counts Can Be Expensive#

Alternatives to Exact Counts#

Pagination Is Also a Security Boundary#

Whitelist Sort Modes#

Caching Multi-Tenant Lists#

Testing Pagination Properly#

Important Test Cases#

Test IDs Across the Entire Traversal#

Use Real PostgreSQL for Query Tests#

Observability#

Avoid High-Cardinality Metric Labels#

Offset vs Cursor Comparison#

When to Use Offset Pagination#

When to Use Cursor Pagination#

Do Not Use Cursor Pagination Just Because It Sounds Advanced#

A Practical Decision Process#

A Good Production Cursor Flow#

Production Checklist#

A Simple Way to Remember the Difference#

Conclusion#

References#

Related

Why Page 5000 Becomes Slow

How Offset Pagination Works

The Real Problem: Results Move

Deletions Can Cause Missing Rows

Offset Pagination Is Not Bad

A Safe Offset Pagination Parser

Cursor Pagination

Offset vs Cursor Mental Model

First Cursor Page

The Next Page

Why Cursor Pagination Scales Better

Cursor Pagination Needs Stable Ordering

Add a Unique Tie-Breaker

Does the ID Need to Be Sequential?

Good Cursor Ordering Columns

Ordering and Cursor Predicate Must Match

Indexes Are Critical

Filtering Changes the Index

One Index Does Not Always Handle Every Query

Verify With EXPLAIN ANALYZE

What Is a Cursor?

boundary

direction

version

snapshot

filterHash

Why Cursors Should Be Opaque

Base64 Is Not Security

Signing Cursors With HMAC

Cursor Signing Does Not Encrypt the Data

Validate Decoded Cursors

Limit Cursor Size

Bind the Cursor to Filters

Build a Filter Hash

Bind the Cursor to the Tenant

Forward Pagination

Backward Pagination

Why Reverse the SQL?

Do You Need Backward Pagination?

Complete Request Flow

Example TypeScript Handler

Stable Invalid Cursor Errors

How to Build pageInfo

Forward Page Info

Backward Page Info

What If the Cursor Row Was Deleted?

Empty Pages Can Be Valid

What Happens When New Rows Are Inserted?

Cursor Pagination Does Not Freeze the Dataset

What If an Ordering Value Changes?

Filters Can Also Change

Live Feed vs Snapshot Traversal

Snapshot-Bounded Cursor

Why Store the Full Snapshot Tuple?

Snapshot-Bounded Pagination Still Is Not a True Snapshot

Do Not Keep One Database Transaction Open Across HTTP Pages

Read Replicas Add Another Consistency Problem

Options for Replica Consistency

Exact Counts Can Be Expensive

Alternatives to Exact Counts

Pagination Is Also a Security Boundary

Whitelist Sort Modes

Caching Multi-Tenant Lists

Testing Pagination Properly

Important Test Cases

Test IDs Across the Entire Traversal

Use Real PostgreSQL for Query Tests

Observability

Avoid High-Cardinality Metric Labels

Offset vs Cursor Comparison

When to Use Offset Pagination

When to Use Cursor Pagination

Do Not Use Cursor Pagination Just Because It Sounds Advanced

A Practical Decision Process

A Good Production Cursor Flow

Production Checklist

A Simple Way to Remember the Difference

Conclusion

References