This feels familiar. I once designed an API for retrieving a large number of items (hundreds of thousands to millions) from a Postgres database. I quickly learned that using OFFSET for such queries is an anti-pattern because it forces Postgres to iterate through all items before the specified offset just to retrieve the few items you requested. So allowing unlimited offsets is a great way to grind your database to a halt.

I learned that a better way to do this is to use an index-based query instead, which Postgres can deal with much better. For example, you could give your items an auto-incrementing sequence number and tell the API consumer the sequence number of the last item in the result set. For the next request the consumer can use this number to start off where it left. While that restricts the way you can order items it will greatly speed up queries and allow efficient retrieval of a very large number of items without running into the slowdown effect caused by offset-based pagination.