3 ms·
What’s the better way to do pagination?
by coolThingsFirst 1y ago
What’s the better way to do pagination?
- duggan 1y agoNot 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-offset-and-cursor-pagination-8ddc54d10d98 https://medium.com/better-programming/understanding-the-offs...
- tmstieff 1y agoThe 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.
- AlienRobot 1y agoYou 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.