Offset vs Where performance for pagination with index Suppose that you have a list of millions of items, each one with a timestamp (created_at). 

You also have an index on created_at DESC.

Sometimes you need to navigate through all items, chronologically, and obviously you will use pagination on created_at.

Now I have a question about pagination on big data: considering that there's an index, and no other filter conditions on items, is there any performance difference in using OFFSET or using WHERE created_at &gt; last_item_timestamp?

I mean, is there any difference in the way that the btree index will be accessed? Is there any difference in the amount of data that will be loaded from disk?

I ask this because I would say that there's no difference in the traversal of the btree, using OFFSET or WHERE, but from some quick tests that I have made it seems to be a performance difference. In particular I am testing this with PostgreSQL 11.

Update: after more reasoning I think that there's actually a difference in the number of tree nodes that have to be read. In case of OFFSET you need to read all the N nodes before the item, while with WHERE you only need to read log(N) nodes (because you don't need to count them).
Yes, there is a performance difference, especially with big OFFSETs. With OFFSET you ORDER BY your indexed column, and then limit the result set by removing the first x rows indicated by OFFSET x. All the rows still have to be read from disk.

With WHERE you ORDER BY your indexed column, but then only read the range of rows specified by your indexed WHERE condition from disk.

For more details, see: We need tool support for keyset pagination (On the Use The Index, Luke blog)