OFFSET Pagination: The Query That Gets Slower the Deeper You Scroll

There is a default in a lot of pagination code that is technically correct, works perfectly in testing, and degrades with a curve that eventually takes a site down. The default is OFFSET.

The problem is not that OFFSET is slow. The problem is that it gets slower in proportion to how far the user has scrolled, and users scroll.

The engine is doing exactly what it was asked, and the wasted work is a function of the tuples or index entries discarded rather than the rows returned. It is not necessarily one heap read per skipped row: a covering index plus sufficiently visible heap pages can produce an index-only scan. That is why covering indexes and index-only scans change the constant but not the linear shape. The fix has a requirement people discover too late: the cursor must encode a total, stable order.

What OFFSET actually does

SELECT id, created_at, headline
FROM events
WHERE created_at > now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

OFFSET 5000 does not mean “start at row 5000”. It means “produce rows one through 5000, then discard them, then return the next 50.”

  rows the database must handle

    rows 1      .. 5000   produced, ordered, discarded
    rows 5001   .. 5050   returned

    work = offset + limit

The cost is therefore a function of the page number.

  page 1     offset      0    -> 50 rows handled
  page 20    offset    950    -> 1000 rows handled
  page 100   offset  4950    -> 5000 rows handled
  page 1000  offset 49950   -> 50,000 rows handled
  page 10000 offset 499950  -> 500,450 rows handled

At the bottom of a long feed, the query is doing ten thousand times the work of the first page, for the same fifty rows out.

Why a perfect index does not rescue it

The natural objection is that with an index on (created_at DESC) the database should be able to seek to the offset position instead of counting through it. Postgres does have an index on the sort column in this case, and the plan still degrades.

The reason is that skipping an index entry is not something the executor does. It walks the index in order, produces a row for each entry it visits, applies the filter, applies the limit and offset, and discards the ones before the offset. Each index entry visited is work, whether or not the row is returned.

  index scan backwards, OFFSET 5000

    visit entry 1      -> offset counter 4999   discard
    visit entry 2      -> offset counter 4998   discard
    ...
    visit entry 5000   -> offset counter 0      discard
    visit entry 5001   -> keep
    ...
    visit entry 5050   -> keep, limit reached

    5050 index entries visited for 50 rows returned

The entries it visits cheaply when the index covers the query, and expensively when they are not. The difference between the two cases is the heap.

  index-only scan, selected columns covered and heap pages all-visible
    5000 index entries, few or no heap fetches
    still linear work, with a smaller constant

  index scan, headline not in the index
    5000 index entries
    up to 5000 heap lookups, depending on filters and visibility
    cache state and physical layout decide how expensive they are

That is the version that produces a timeout. The plan is not pathological and the index is being used, so nothing looks wrong in a slow query log. The heap accesses are simply scattered across the table in whatever order the index entries happened to be in.

Adding the missing column to a covering index helps a great deal, and it is worth doing. It does not change the shape of the problem, which is a cost that grows with the offset.

The correctness problem underneath the performance one

OFFSET is also wrong, independent of how fast it is, and this is the failure that gets reported as a bug rather than as a slow endpoint.

  a reader is on page 4, offset 150

    t=0   page 4 requested: rows 151-200
    t=1   12 new events are inserted
    t=2   page 5 requested: offset 200
          -> rows 201-250 OF THE NEW ORDERING
          -> 12 of them are the rows that were on page 4
          -> the reader sees them twice and never sees 12 others

Every insert at the head of the ordering shifts every subsequent row by one. Anything a reader fetches after the shift is now wrong, and a reader who has scrolled once before a single insert has already been given twelve rows they will see again.

This affects real features in ways that are easy to miss. Infinite scroll produces visible duplicates. A background job that walks pages to export data silently skips and repeats rows, so the export is wrong rather than slow. A resumable upload or sync that stores an offset as its progress marker loses track of the position on the first concurrent write.

Keyset pagination

The fix is to stop asking for a position and start asking a question: give me the rows that come after this particular row.

-- first page
SELECT id, created_at, headline
FROM events
WHERE created_at > now() - interval '30 days'
ORDER BY created_at DESC, id DESC
LIMIT 51;

-- next page, given (created_at, id) = ('2026-09-04 10:12:31+01', 918274)
SELECT id, created_at, headline
FROM events
WHERE created_at > now() - interval '30 days'
  AND (created_at, id) < ('2026-09-04 10:12:31+01', 918274)
ORDER BY created_at DESC, id DESC
LIMIT 51;

The row-value comparison is the mechanism that makes this an index seek rather than a filter.

  OFFSET 5000             keyset with (created_at, id) < (c, i)

  "give me 50 rows        "give me rows strictly before this
   from position 5000"      sort position, in index order"
   -> 5000 entries walked  -> 1 index descent, then 50 entries
   cost grows with page   cost is the same on page 1 or page 10000

The B-tree has to support the requested order. PostgreSQL can scan an index backward, so (created_at ASC, id ASC) can satisfy the complete reverse order. Mixed directions such as created_at DESC, id ASC require matching per-column options. Verify with EXPLAIN; do not infer eligibility from the index text alone.

CREATE INDEX CONCURRENTLY idx_events_created_id
    ON events (created_at DESC, id DESC);

The LIMIT 51 rather than 50 is a technique worth keeping: it tells you whether there is another page without a second query, and you simply ignore the extra row.

public readonly record struct EventCursor(DateTimeOffset CreatedAt, long Id)
{
    public string Encode() =>
        Convert.ToBase64String(JsonSerializer.SerializeToUtf8Bytes(this));

    public static EventCursor Decode(string value) =>
        JsonSerializer.Deserialize<EventCursor>(Convert.FromBase64String(value));
}

public async Task<Page> GetPageAsync(string? cursorToken, int pageSize, CancellationToken ct)
{
    await using var cmd = _conn.CreateCommand();
    cmd.CommandText = """
        SELECT id, created_at, headline
        FROM events
        WHERE created_at > now() - interval '30 days'
          AND (
                @cursor_created IS NULL
             OR (created_at, id) < (@cursor_created, @cursor_id)
          )
        ORDER BY created_at DESC, id DESC
        LIMIT @take
        """;

    var cursor = cursorToken is null ? null : EventCursor.Decode(cursorToken);

    cmd.Parameters.Add(new NpgsqlParameter("cursor_created",
        NpgsqlDbType.TimestampTz) { Value = (object?)cursor?.CreatedAt ?? DBNull.Value });
    cmd.Parameters.Add(new NpgsqlParameter("cursor_id",
        NpgsqlDbType.Bigint) { Value = (object?)cursor?.Id ?? DBNull.Value });
    cmd.Parameters.Add(new NpgsqlParameter("take", NpgsqlDbType.Integer) { Value = pageSize + 1 });

    var rows = new List<Event>(pageSize);
    await using var reader = await cmd.ExecuteReaderAsync(ct);

    while (await reader.ReadAsync(ct))
        rows.Add(new Event(reader.GetInt64(0), reader.GetFieldValue<DateTimeOffset>(1), reader.GetString(2)));

    var hasMore = rows.Count > pageSize;
    if (hasMore) rows.RemoveAt(rows.Count - 1);

    // the next cursor comes from the last row returned, not from a row count
    var nextCursor = hasMore
        ? new EventCursor(rows[^1].CreatedAt, rows[^1].Id).Encode()
        : null;

    return new Page(rows, nextCursor);
}

A cursor is opaque to the client and should be treated as such. Decode failures need to become a client error rather than a 500. In production I also version and authenticate the token, and include a hash of the filter and sort, so clients cannot alter a boundary or reuse it against another query.

The tiebreaker is not optional

The id in ORDER BY created_at DESC, id DESC is doing real work, and the version without it is a bug that only shows up under concurrency.

Two events can share a created_at to the microsecond. Without a unique final column, the database is free to order those two rows in either direction, and it may choose differently on each execution.

  two rows, identical created_at

    page 1 returns  [A] and stops just before B
    page 2 asks for rows before A's timestamp
      -> B is still in range
      -> B is returned again

    or, on a different execution, the ordering flips
      -> B is skipped entirely

The fix is to make the sort total. Every row has a distinct position in the ordering, so “the rows after this row” is a well-defined set, and no row can be returned twice or missed.

-- total order, safe for a seek
ORDER BY created_at DESC, id DESC
WHERE  (created_at, id) < (@c, @id)

-- partial order, unsafe for a seek
ORDER BY created_at DESC
WHERE  created_at < @c

The same rule applies to every paginated surface. A text sort needs the id appended. A computed score needs the id appended. A sort by a nullable column needs the nulls handling to be explicit, because NULLS FIRST and NULLS LAST change which rows the comparison excludes.

A useful check: if you cannot state the sort as a list of columns ending in a unique one, the pagination is not correct.

Counting is the other expensive query

OFFSET is usually blamed for a slow feed, and the count is frequently the actual cost.

SELECT count(*) FROM events WHERE created_at > now() - interval '30 days';

PostgreSQL count(*) is exact, not a catalog estimate. It must process the qualifying rows through a sequential or index(-only) plan; the visibility map may let an index-only scan avoid heap visits, but it does not turn the aggregate into metadata lookup. A selective filter and a suitable index can make the count cheap. A broad filter still touches a broad part of the relation or index.

  what a "load more" endpoint actually costs

    SELECT count(*) ...              -- often 200-800 ms
    SELECT ... LIMIT 50 OFFSET 5000  -- 5-20 ms on a covering index

    the count is 30x the page

Four options, in the order they tend to be right.

Drop the exact total. Show “1-50” or nothing. Almost every interface that shows “1,247 results” is showing a number that does not change anybody’s behaviour.

Estimate it. EXPLAIN gives a row estimate that is close enough for “about 1,200”, and the planner already has the statistics.

// Explain the filtered row source, not count(*): the aggregate itself estimates one output row.
var planJson = await conn.QuerySingleAsync<string>(
    "EXPLAIN (FORMAT JSON) SELECT 1 FROM events WHERE created_at > now() - interval '30 days'");

// Parse Plan[0].Plan[\"Plan Rows\"] from the JSON and label it as an estimate.

Cache it. Counts for a filter that changes slowly can be computed on a schedule and served from memory, with a short TTL and an explicit staleness.

Maintain it. If the number is genuinely load-bearing, keep it rather than deriving it. A counter table updated in the same transaction as the write is exact and cheap to read, at the cost of a write path that now has a row to update.

When you genuinely need page numbers

Keyset pagination cannot jump to page fifty, and a requirement for numbered pages with random access is a real requirement in a reporting interface. The honest options are all compromises.

Precompute the boundaries. Walk the keyset once, record the cursor at each page boundary, and look the boundary up on request. Page numbers become constant time, and the maintenance job is cheap.

CREATE TABLE feed_pages (
    feed_key    text        NOT NULL,
    page_no     int         NOT NULL,
    last_created_at timestamptz NOT NULL,
    last_id     bigint      NOT NULL,
    recorded_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (feed_key, page_no)
);

The cost is staleness. The boundaries are a snapshot, and if the feed changes the numbering is slightly wrong, which for a page number in a UI is usually acceptable and for a page number in a financial report is not.

Cap the depth. If the interface is an infinite scroll, cap it at something like a thousand pages and say so. Nobody scrolls past a thousand pages, and a cap turns an unbounded query into a bounded one.

Cap the offset instead. Keep OFFSET for small pages and refuse large ones, which at least keeps the failure visible rather than gradual.

if (offset + pageSize > MaxPageDepth) throw new PageDepthExceeded(pageSize);

The judgement to avoid is shipping OFFSET with no cap at all and treating it as an implementation detail. Its cost is a function of an input the user controls, which makes it a capacity problem whether or not anyone is looking at it.

A production-ready architecture

  a list endpoint
        |
        v
  does the UI need to jump to an arbitrary page?
        |
   +----+---------------------------------------+
   | no, infinite scroll                    | yes, numbered pages
   v                                       v
  keyset / seek pagination                precompute boundaries into
  cursor = last row's sort key            a page table, or cap the depth
  ORDER BY created_at DESC, id DESC            |
  (created_at, id) < (@c, @id)                  v
  index matches sort, exactly      +---------------------------+
        |                          | keyset, with a boundary    |
        v                          | lookup and a staleness     |
  +----------------------------------------+  caveat             |
  | total count: drop it, estimate it,     +----------------------+
  | cache it, or maintain it                           |
  | never derive it on the page request                |
  +---------------------------------------------------+

A delivery checklist:

1. Replace OFFSET with a keyset comparison on any list a user can scroll through.
2. Make the sort total by ending it with a unique column. id is usually already there.
3. Create an index that can provide the sort (including mixed directions), and verify the seek and row count with EXPLAIN (ANALYZE, BUFFERS).
4. Put the covering columns in the index so the skipped rows cost an index entry rather than a heap read.
5. Return LIMIT n + 1 and derive hasMore from the extra row instead of running a count.
6. Delete the exact count, estimate it, cache it, or maintain it, in that order of preference.
7. Treat the cursor as opaque, and encode the query identity and sort into it so a mismatched cursor is rejected.
8. Where numbered pages are required, store the keyset boundary per page and accept that it is a snapshot.
9. Put a hard cap on the depth of any OFFSET endpoint that survives, and return an error rather than a slow response.
10. Export the page depth in metrics, because a client asking for offset four million is worth knowing about.

Failure stories worth testing

Query the same table at offset 0, 10k, 100k, and 1M and time all four

The curve is the whole argument. A linear relationship between offset and time, on an indexed table, is not subtle once you have seen it plotted.

Reproduce the duplicate on a page boundary with a concurrent insert

Load page two, insert a row at the head, load page three, and look for the repeats. It works with OFFSET on any feed that receives writes while someone is scrolling it.

Drop the tiebreaker from a keyset query and run it concurrently

Two requests with the same created_at at the boundary, several executions, look for a row that appears twice or not at all. The id is what makes this impossible and removing it is the fastest way to see why.

Time a filtered count(*) against the page query

On most real feeds the count is the bigger cost, and finding that out changes which one you optimise.

Run EXPLAIN (ANALYZE, BUFFERS) on a deep offset and look at the buffer counts

The heap reads scattered across the plan are the part that turns a slow query into a timeout, and they are visible in the buffers section.

Common mistakes

Mistake	What actually happens	Better decision
Treating OFFSET as a starting position	Rows are produced and discarded up to the offset	Keyset comparison against the last row
Assuming a sort index makes it cheap	Every index entry up to the offset is still visited	Seek on a row-value comparison
Omitting the tiebreaker from the sort	Equal keys order arbitrarily, causing gaps and repeats	End the sort with a unique column
Letting the user control the offset with no cap	Cost is a function of user input	Cap the depth or move to a cursor
Running count(*) per page	Often the slowest query in the endpoint	Drop, estimate, cache, or maintain it
Returning the total in the same request	A full scan on a table with a filter	Report a range or a cached estimate
Assuming index direction by inspection	A full reverse scan works; mixed directions are different	Verify the requested order with EXPLAIN
Not including selected columns in the index	Every skipped row costs a random heap read	Make the index cover the query
Treating a cursor as a page number	Clients start constructing offsets again	Opaque token, no ordering exposed
Using a cursor across a different sort	Silently wrong rows rather than an error	Encode the query identity in the token
Numbering pages with a live OFFSET query	The numbering is wrong as soon as a row is inserted	Precomputed boundaries, or do not number

The complete story in one minute

OFFSET is not a starting position, it is a count of rows to produce and then throw away, which makes the cost of a page proportional to its depth rather than constant. A page at offset five thousand handles five thousand and fifty rows to return fifty, and at a million rows the heap accesses along the way are scattered enough to turn it into a timeout. Adding the missing columns to a covering index helps a great deal and does not change the shape of the problem, because the executor still walks every index entry up to the offset rather than seeking past them.

Underneath the performance issue there is a correctness one, and it is the more damaging of the two. Every insert at the head of the ordering shifts every row after it, so a reader who fetched page four and then requests page five is now looking at a list that has changed under them, and they see rows twice and miss others. That affects infinite scroll visibly and it affects background exports silently, which is worse.

The fix is to ask a question rather than request a position. Instead of “give me rows from offset 5000”, ask for “rows before this sort position”, using a row-value comparison of (created_at, id) < (@created, @id) against an index that can provide that order. That is an index descent followed by a bounded page scan, so page depth leaves the cost equation. It is stable against inserts before the cursor; deletes and updates to sort keys still change what a live traversal can observe, so exports that require a fixed dataset need a snapshot or immutable cutoff. The id is not optional: without a unique final sort key the boundary is ambiguous. The other query in that endpoint is often an exact total, and count(*) must still process every qualifying row. Drop it, estimate it honestly, cache it, or maintain it. Where numbered pages are genuinely required, precompute boundaries with an explicit snapshot/staleness contract and cap anything that still uses OFFSET.

Technical references

• PostgreSQL: LIMIT and OFFSET
• PostgreSQL: B-tree ordering and backward scans
• PostgreSQL: index-only scans and the visibility map
• EF Core: pagination and keyset pagination