Dev.to WebDev 🛠 Dev 👁 0 📖 7 min read

Stop Using OFFSET for Large PostgreSQL Tables: A Practical Guide to Cursor Pagination

Pagination looks like a solved problem until your API starts slowing down on page 500. The first version usually looks harmless: SELECT id, created_at, status FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 1000

Pagination looks like a solved problem until your API starts slowing down on page 500.

The first version usually looks harmless:

SELECT id, created_at, status
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

It returns 20 rows. The SQL is simple. The frontend knows how to request page 501.

But there's a hidden cost: PostgreSQL still needs to process the rows it skips. As the offset grows, the database does more work to return the same number of results.

There's also a correctness problem. If new records arrive while a user moves between pages, records can appear twice or get skipped.

For activity feeds, transaction histories, audit logs, and large API result sets, cursor-based pagination (also called keyset pagination) is often a better choice.

Here's how to implement it correctly, where it helps, and where traditional pagination is still useful.

1. Why OFFSET Becomes Expensive

Consider a hypothetical orders table with millions of records.

An API endpoint returns 20 orders at a time:

SELECT id, created_at, status
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 0;

The first page is straightforward.

Now imagine a customer navigating much further into the order history:

SELECT id, created_at, status
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

Even with a suitable index, PostgreSQL generally needs to traverse the skipped index entries before returning the requested rows.

A large offset doesn't mean the database can jump directly to the desired record.

PostgreSQL's documentation explicitly notes that skipped rows still have to be computed and that large offsets can be inefficient.

Reference: PostgreSQL LIMIT and OFFSET documentation

The other problem is consistency.

Imagine the first page contains these records:

Page 1: 100, 99, 98, 97, 96

Before the next request, a new record with ID 101 is inserted.

The next query uses OFFSET 5, producing:

Page 2: 96, 95, 94, 93, 92

Record 96 appears twice.

Deletes or changes to sort values can create similar pagination problems, including missed records.

2. How Cursor Pagination Works

Instead of telling PostgreSQL how many rows to skip, tell it where to continue.

Let's start with a simple example using IDs:

SELECT id, created_at, status
FROM orders
WHERE id < 96
ORDER BY id DESC
LIMIT 5;

Here, 96 is the cursor, representing the last record returned by the previous page.

The database can use an index to find records below that value rather than counting through everything before it.

The API response might look like this:

{
  "data": [
    {"id": 95},
    {"id": 94},
    {"id": 93},
    {"id": 92},
    {"id": 91}
  ],
  "next_cursor": "91",
  "has_more": true
}

The client passes next_cursor to request the following page.

This approach keeps the amount of pagination-related work more predictable as users move deeper into the dataset.

However, using IDs as cursors only makes sense when the ordering is based on IDs.

Most applications need something more flexible.

3. Use a Composite Cursor for Stable Ordering

Suppose the API displays orders by creation time:

ORDER BY created_at DESC

Timestamps aren't necessarily unique.

Two orders can share the same created_at value. If the cursor uses only the timestamp, the API risks skipping records that have the same timestamp as the last item on the previous page.

The solution is to add a unique tie-breaker.

ORDER BY created_at DESC, id DESC

Now every record has a deterministic position.

The next-page query becomes:

SELECT id, created_at, status
FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 21;

The parameters represent the timestamp and ID of the last returned record.

PostgreSQL supports this row-comparison syntax. The comparison evaluates columns from left to right, using the next column when earlier values are equal.

This example assumes both cursor columns are non-null.

Reference: PostgreSQL Row Constructor Comparisons

Why LIMIT 21 instead of 20?

Because requesting one extra row provides an inexpensive way to determine whether another page exists.

If 21 rows are returned, send the first 20 and set has_more to true.

The cursor should come from the 20th returned record, not the extra record.

That small detail prevents accidentally skipping an item.

4. Build the Right Index

Cursor pagination isn't automatically fast.

The index must support the filtering and ordering strategy.

For the query above:

CREATE INDEX idx_orders_created_id
ON orders (created_at DESC, id DESC);

A B-tree index in this order can support efficient traversal of the results.

For an application where users only retrieve their own orders, the query might instead be:

SELECT id, created_at, status
FROM orders
WHERE user_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 21;

A more appropriate index would be:

CREATE INDEX idx_orders_user_created_id
ON orders (user_id, created_at DESC, id DESC);

The leading user_id column helps PostgreSQL narrow the search to one user's records before traversing them in the requested order.

This follows an important principle: design indexes around real query patterns, not individual columns in isolation.

The trade-off is write overhead. Every additional index consumes storage and requires maintenance during inserts, updates, and deletes.

Don't add several pagination indexes without understanding which queries need them.

5. Design the API Contract Carefully

A clean cursor-pagination endpoint might look like:

GET /api/orders?limit=20&cursor=eyJ...

The response:

{
  "data": [],
  "pagination": {
    "next_cursor": "eyJ...",
    "has_more": false
  }
}

The cursor can contain the timestamp and ID:

{
  "created_at": "2026-11-19T10:30:00Z",
  "id": 8421
}

Encode the cursor into a URL-safe token so callers don't need to understand its internals.

Base64 encoding is convenient, but it isn't encryption or tamper protection.

For public APIs, consider signing cursor payloads or validating decoded values carefully.

Also, keep these rules in mind:

  • Enforce a maximum page size to protect database resources.
  • Use parameterized queries instead of concatenating cursor values into SQL.
  • Return a clear client error for malformed cursors.
  • Ensure the cursor matches the filters and sort order used for the request.
  • Treat cursors as navigation markers, not proof of authorization.

A cursor should never allow a client to bypass tenant or user-level access controls.

6. Understand the Consistency Trade-Off

Cursor pagination solves some problems with changing datasets, but it doesn't create a permanent snapshot.

Consider an order that was originally on page three.

If its sorting timestamp changes before the user reaches that page, the record might move across the cursor boundary.

It could be missed or encountered again.

For feeds ordered by immutable creation timestamps, this is usually manageable. For datasets sorted by frequently changing fields, the behavior requires more thought.

There are several alternatives:

Use immutable ordering fields. This is often the simplest choice for event streams, audit logs, and creation histories.

Introduce a snapshot boundary. Capture a fixed upper boundary when pagination begins and apply it to later requests. This can exclude newer records, although it doesn't freeze existing records against updates or deletions.

Use snapshot-based reads or materialized results. For exports and compliance workflows that require an exact point-in-time dataset, stronger consistency mechanisms may be necessary.

These choices have different costs in storage, database resources, and implementation complexity.

The correct decision depends on whether the product needs a convenient browsing experience or an exact historical result set.

7. Measure Before and After

I wouldn't replace every OFFSET query just because cursor pagination sounds more efficient.

First, inspect the actual workload.

PostgreSQL provides EXPLAIN ANALYZE to evaluate execution plans:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, status
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

Compare that with a cursor query using a boundary that represents a similar depth in the dataset.

Look for:

  • Actual execution time across shallow and deep pages.
  • Rows processed and index access patterns.
  • Shared buffer hits and reads.
  • Sort operations and sequential scans.
  • API latency at p50, p95, and p99 under representative traffic.

No benchmark numbers are provided here because results depend on table size, indexes, data distribution, cache state, and concurrent activity.

Measure the behavior in your own environment before claiming a performance improvement.

8. When OFFSET Is Still the Better Choice

Cursor pagination adds complexity.

It makes jumping directly to page 50 harder because navigation depends on a known cursor boundary.

OFFSET can still be appropriate for small administrative tables, relatively static datasets, and interfaces that require numbered pages or random page navigation.

Here's the practical comparison:

Requirement OFFSET Cursor
Simple implementation Easier More involved
Direct page-number navigation Yes Not naturally
Deep pagination efficiency Often worse Often better
Behavior during inserts Can duplicate or skip More stable with immutable keys
Index design Important Important
Infinite scrolling Supported Usually a better fit

If a dataset contains a few hundred rows and queries are already fast, cursor pagination may provide little benefit.

Complexity is a cost too.

Final Thoughts

Pagination isn't just a frontend concern. It affects database load, API reliability, and whether users see consistent results.

Three lessons are worth remembering:

  1. OFFSET is simple, but deep pagination can become expensive. Measure where that cost starts to matter.
  2. A reliable cursor needs deterministic ordering. Use a unique tie-breaker and an index that matches your query.
  3. Cursor pagination isn't a snapshot. Understand how inserts, deletes, and updates affect the user experience.

For large append-heavy datasets, cursor pagination is often a strong default. For small tables and page-number navigation, OFFSET may still be the simpler engineering decision.

How do you handle pagination in APIs where records are frequently inserted or updated?

📰 Read the original article on Dev.to WebDev

Originally published by Dev.to WebDev. Aggregated on AIWithGhost for educational purposes — full credit and traffic to the original publisher.