Paginate Without OFFSET

LIMIT 10 OFFSET 40 is how most people write page 5. It works, until the table is large. To return page 5000 the database still reads and throws away the first 49,990 rows, so each page is slower than the last. It also repeats or skips rows when someone inserts a row while a person is paging. Keyset pagination fixes both: instead of "skip 40 rows", you say "give me the rows after the last one I showed".

Step 1: a table to page through

An invented table of 50,000 rows with repeated dates, so the sort needs a tie-breaker.

CREATE TABLE posts AS
SELECT g AS post_id, DATE '2026-01-01' + (g % 200) AS posted_on
FROM generate_series(1, 50000) AS g;
ALTER TABLE posts ADD PRIMARY KEY (post_id);
CREATE INDEX posts_posted_on_id_idx ON posts (posted_on, post_id);
VACUUM ANALYZE posts;
SET max_parallel_workers_per_gather = 0;

SELECT 50000
ALTER TABLE
CREATE INDEX
VACUUM
SET

VACUUM ANALYZE refreshes the statistics and marks the pages as all visible, which keeps the plans below the same on every run.

Step 2: the OFFSET way, and its cost

Page 1 and a late page both return five rows. Look at how many rows the late page reads.

SELECT post_id, posted_on FROM posts ORDER BY posted_on, post_id LIMIT 5 OFFSET 0;

 post_id | posted_on
---------+------------
     200 | 2026-01-01
     400 | 2026-01-01
     600 | 2026-01-01
     800 | 2026-01-01
    1000 | 2026-01-01
(5 rows)

EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT post_id, posted_on FROM posts ORDER BY posted_on, post_id LIMIT 5 OFFSET 40000;

                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Limit (actual rows=5.00 loops=1)
   ->  Index Only Scan using posts_posted_on_id_idx on posts (actual rows=40005.00 loops=1)
         Heap Fetches: 0
         Index Searches: 1
(4 rows)

The query returns five rows, and the scan underneath it produced 40,005. The Limit line shows the rows it kept. The index scan line shows how many it had to walk past. Make the offset bigger and the work grows with it.

Step 3: keyset pagination

Remember the last row of the page you showed: posted_on and post_id. Ask for the rows that sort after it. A row comparison, (a, b) > (x, y), does this with both columns at once.

SELECT post_id, posted_on
FROM posts
WHERE (posted_on, post_id) > (DATE '2026-01-01', 1000)
ORDER BY posted_on, post_id
LIMIT 5;

 post_id | posted_on
---------+------------
    1200 | 2026-01-01
    1400 | 2026-01-01
    1600 | 2026-01-01
    1800 | 2026-01-01
    2000 | 2026-01-01
(5 rows)

Page 1 above ended at (2026-01-01, 1000), and these are the five rows after it. To get the next page, use the last row of this one. There is no page number. You carry the last values from page to page, in a link or a cursor token.

Now look at the plan for a deep page.

EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT post_id, posted_on
FROM posts
WHERE (posted_on, post_id) > (DATE '2026-04-01', 40000)
ORDER BY posted_on, post_id
LIMIT 5;

                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Limit (actual rows=5.00 loops=1)
   ->  Index Only Scan using posts_posted_on_id_idx on posts (actual rows=5.00 loops=1)
         Index Cond: (ROW(posted_on, post_id) > ROW('2026-04-01'::date, 40000))
         Heap Fetches: 0
         Index Searches: 1
(5 rows)

The index scan starts at the right place and stops after five rows. It does not matter whether this is the first page or the five-thousandth.

Why the tie-breaker is not optional

Many posts share a date. If you page on the date alone, you lose rows.

SELECT count(*) AS posts_on_that_day FROM posts WHERE posted_on = DATE '2026-01-02';
SELECT count(*) AS rows_after_it FROM posts WHERE posted_on > DATE '2026-01-02';
SELECT count(*) AS rows_after_it_with_tiebreak FROM posts WHERE (posted_on, post_id) > (DATE '2026-01-02', 1);

 posts_on_that_day
-------------------
               250
(1 row)

 rows_after_it
---------------
         49500
(1 row)

 rows_after_it_with_tiebreak
-----------------------------
                       49749
(1 row)

Paging with posted_on > last_date skips every other post of the same day. The pair (posted_on, post_id) is unique, so each row has exactly one place in the order.

What you give up

• No jump to page N. Keyset gives "next" and "previous", not a page number.
• Going backwards means reversing the comparison and the order, then reversing the page.
• The sort columns must be indexed, in the same order, for the speed.
• Descending order needs < instead of >, and the same direction on every column.

Related course: SQL Intermediate.