Query performanceintermediateFree in full

Replace deep OFFSET paging with keyset pagination

Page 10,000 of an OFFSET query re-reads everything before it. Switch to keyset (seek) pagination and the query reads only the rows it returns, 200,020 rows scanned drops to 20.

Problem

What you're actually looking at

The symptom as it shows up on a real server.

OFFSET N LIMIT K makes PostgreSQL generate and throw away the first N rows on every page. Deep pages get linearly slower, and infinite-scroll APIs quietly become the slowest endpoint you own.

A shipment_events feed is paged 20 rows at a time. Jumping to a deep page with OFFSET 200000 forces the database to walk 200,020 index entries just to return the last 20.

Simple terms

OFFSET 200000 LIMIT 20 does not skip ahead for free. PostgreSQL still walks past all 200,000 rows one by one, throws them away, and only then hands you the next 20, so "page 10,000" is far slower than "page 1". Keyset (or "seek") paging fixes this by remembering the last row you saw and asking for "the next 20 after this key". The database jumps straight there, so the cost of a page no longer depends on how deep it is.

Before you start

  • • A unique, indexed ordering key (a primary key works).
  • • An API contract that can carry a cursor (the last key) instead of a page number.

How to identify it

  • ›EXPLAIN ANALYZE shows the inner node producing rows equal to OFFSET + LIMIT while the query returns only LIMIT.
  • ›Response time grows with the page number, the first pages are fast, deep pages crawl.
  • ›The endpoint powers infinite scroll or an admin table where users actually reach deep pages.
  • ›The ORDER BY is on a unique, indexed key (or can be made so), which is what keyset pagination requires.

Pitfalls to avoid

  • ✕Do not keyset-paginate on a non-unique sort key without a tiebreaker, rows can be skipped or repeated at page boundaries.
  • ✕Do not expose raw OFFSET to clients for deep lists; it is a denial-of-service vector on large tables.
  • ✕Do not forget that keyset pagination cannot jump to an arbitrary page number, it is next/previous, by design.
  • ✕Do not drop the ORDER BY when you add the WHERE seek clause; the index only helps if the sort matches it.

Trace it

Run these against the affected server. To build a copy of this scenario instead, see “Reproduce it in a lab” below.

  1. 01

    Watch OFFSET read everything it skips

    The Index Scan produces 200,020 rows and the Limit discards all but the final 20. That is the deep-page tax: PostgreSQL walks past 200,000 ids just to return one screen of data.

    SQL
    EXPLAIN (ANALYZE, BUFFERS)
    SELECT * FROM shipment_events ORDER BY id OFFSET 200000 LIMIT 20;
    Lab-captured output
                                                                              QUERY PLAN
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------
     Limit  (cost=6726.92..6727.60 rows=20 width=40) (actual time=22.707..22.711 rows=20.00 loops=1)
       Buffers: shared hit=2076
       ->  Index Scan using shipment_events_id_idx on shipment_events  (cost=0.42..8410.00 rows=250000 width=40) (actual time=0.011..17.541 rows=200020.00 loops=1)
             Index Searches: 1
             Buffers: shared hit=2076
     Planning:
       Buffers: shared hit=42
     Planning Time: 0.196 ms
     Execution Time: 22.735 ms
    (9 rows)

Resolution approach

  1. 1.Remember the last row's key instead of an offset, and fetch WHERE key > :last ORDER BY key LIMIT K.
  2. 2.Back the seek with an index on the exact ORDER BY columns so the WHERE clause is a range scan.
  3. 3.For composite sorts, use a row-value comparison, WHERE (sort_col, id) > (:last_sort, :last_id).
  4. 4.Keep OFFSET only for small, bounded lists where users never reach deep pages.

Stop it recurring

  1. 01

    Seek by the last key instead of counting

    The keyset query starts at id > 200000 and stops after 20 rows. It reads a handful of buffers instead of walking 200,020 index entries, the cost no longer depends on how deep the page is.

    SQL
    EXPLAIN (ANALYZE, BUFFERS)
    SELECT * FROM shipment_events WHERE id > 200000 ORDER BY id LIMIT 20;
    Lab-captured output
                                                                           QUERY PLAN
    ---------------------------------------------------------------------------------------------------------------------------------------------------------
     Limit  (cost=0.42..1.15 rows=20 width=40) (actual time=0.027..0.029 rows=20.00 loops=1)
       Buffers: shared hit=7
       ->  Index Scan using shipment_events_id_idx on shipment_events  (cost=0.42..1682.00 rows=50000 width=40) (actual time=0.026..0.027 rows=20.00 loops=1)
             Index Cond: (id > 200000)
             Index Searches: 1
             Buffers: shared hit=7
     Planning:
       Buffers: shared hit=44
     Planning Time: 0.202 ms
     Execution Time: 0.043 ms
    (10 rows)

Reproduce it in a lab

Builds the scenario above on a throwaway database so you can practise the fix. Skip this if you are working a live incident.

  1. 01

    Lab setup (run this first)

    Builds `shipment_events` with 250,000 rows and a named btree on id so OFFSET 200000 is a real deep page inside the table, not past the end.

    SQL
    DROP TABLE IF EXISTS shipment_events CASCADE;
    CREATE TABLE shipment_events AS
    SELECT g AS id,
           jsonb_build_object('service', CASE WHEN g % 5 = 0 THEN 'express' ELSE 'standard' END) AS payload,
           1.0::numeric AS rate,
           g AS zone_id
    FROM generate_series(1, 250000) g;
    CREATE INDEX shipment_events_id_idx ON shipment_events (id);
    ANALYZE shipment_events;

Verify you're done

EXPLAIN ANALYZE SELECT * FROM shipment_events WHERE id > 200000 ORDER BY id LIMIT 20;
Related errors2201X57014

Related errors

SQLSTATEs this runbook resolves

The error pages that send an on-call engineer here.

More in this category

Other Query performance runbooks

Neighbouring incidents that share the same diagnostic surface.

All Query performance runbooks →

Connected

How this connects to the rest of the library

A live view of this page's real cross-references, what explains it, what fixes it, what to tune, and where to go next. Every link is an authored relationship, not a guess.

Open in the interactive map →
ShareLinkedInX

Need the full procedure?

Pro runbooks finish the incident path

Free runbooks teach the shape. Pro opens the full step transcript, edge cases, and prevention depth.

Unlock runbooks with ProSign in
FollowSubstackLinkedInnew errors · lab notes · hiring loops