You don't use OFFSET because the btree index just sorts the rows from smallest to largest. It can quickly get the first 30 rows, but it can't quickly figure out where the 30th or the nth row is. When pagination is crawled it will crawl the whole table, so it's important that the worst case performs well.

The fix to this is to paginate by saying "give me 30 rows after X" where X is an unique indexed value, e.g. the primary key of the row. The RDBMS can quickly find X and 30 rows after X in the sorted index.

This makes it hard to implement a "previous page" button but nowadays everything is a feed with just a "show more" button so it doesn't matter much.

Not necessarily "better" but cursor-based pagination, for example, has a different set of trade-offs. It can be more performant, but tends to be trickier to implement.

This article looks like a decent overview: https://medium.com/better-programming/understanding-the-offs...

The easiest alternative is using a where clause and filtering by an ID range. Eg: "WHERE id between 1000 and 1200". But this introduces a ton of limitations with how you can sort and filter, so the general advice of not using LIMIT and OFFSET has a ton of caveats.