Keyset pagination in Postgres: stop using OFFSET on big tables

The problem with OFFSET

Most paginated lists start like this:

select id, created_at, title
from events
order by created_at desc, id desc
limit 20 offset 150000;

It looks like the database jumps to row 150,000. It doesn't. It reads all 150,020 rows and throws away the first 150,000, so every page is slower than the one before. Rows added while someone is browsing also shift the pages, which causes duplicates and gaps.

Keyset pagination fixes both problems. Instead of saying "skip this many rows", you say "continue after this row".

1. Create an index that matches the sort

The index has to use the same columns in the same direction as ORDER BY. The id is there to break ties, because many rows can share a timestamp.

create index events_created_at_id_idx
  on events (created_at desc, id desc);

2. Fetch the first page

The first page needs no cursor:

select id, created_at, title
from events
order by created_at desc, id desc
limit 20;

Keep the created_at and id of the last row. Together they are your cursor.

3. Fetch the next page

Postgres can compare two columns as a single value, and it can use the index to do that:

select id, created_at, title
from events
where (created_at, id) < ($1, $2)
order by created_at desc, id desc
limit 20;

4. Compare the two

I ran both queries on a table of 200,000 rows with EXPLAIN (ANALYZE, BUFFERS):

The keyset query does the same small amount of work whichever page you are on.

When to keep OFFSET

Keyset pagination can't jump straight to page 37, and it can't easily show a total page count. It suits feeds, infinite scroll, API lists and batch jobs. For a small admin table with numbered pages, OFFSET is fine.

Next steps

• Encode the cursor as an opaque base64 string in your API, so clients don't depend on its contents.
• Fetch limit + 1 rows to find out whether there's another page without a count query.