https://dev.to/scion01/optimizing-pagination-in-postgresql-offsetlimit-vs-keyset-21dp

Discusses changing pagination to use keyset vs offset/limit (aka page number/page size).

The idea is to replace:

  SELECT * FROM table ORDER BY id ASC LIMIT 100 OFFSET 100;

with:

  SELECT * from table where id > x ORDER BY id ASC LIMIT 100

where X was the max id in the prior set of 100 items.

For larger offsets, the database has to retrieve and order all rows up to OFFSET
and then discard them. In the second case, the index prevents retrieving
rows that would be in the OFFSET region. Only rows with an id larger than X
would be processed. This may come in play only for large offsets (high page number)
but is worth investigating as it could improve response times for X.index.html pages.