4 ms·
Can you elaborate on the issues with that? I've used that pattern and haven't found any major woes (yet), but also don't usually paginate complicated queries.
by vorpalhex 8y ago
Can you elaborate on the issues with that? I've used that pattern and haven't found any major woes (yet), but also don't usually paginate complicated queries.
- bpicolo 8y agoIn postgres and mysql, at least, the database has to re-scan data every time you run a limit/offset query query. It gets progressively slower as your offset increases. The efficient way to handle it is to set a lower limit on the pkid (or other atomically increasing row) of the last record fetched, and fetch in ascending order e.g. SELECT * from my_table where id > (last_row_id_seen) ORDER BY id asc limit 20; Then you have an indexed jump to the correct rows to return. It's pretty straightforward to abstract a batched database iterator with this pattern for use application-wide (usually can be done in ~20-50 loc), but no ORM that I'm aware of supports this pattern natively. Do note that, by extension, it's impossible to efficiently batch iterate over tables that don't have a unique, orderable key in postgres and mysql.
- kraftman 8y agoWhat if you need to order by something other than id?
- thermodynthrway 8y agoThen, generally, give the user a bunch of filtering options and limit page depth to less than ~1000 entries
- thermodynthrway 8y agoDB has to calculate every record from 0-> offset . A user can frequently crash a server by just going to page 100+ in a big list since the database has to load all that data. Easiest way to prevent this is add enough filtering options that it doesn't inconvenience users to limit depth to page 10 or something
- adamzochowski 8y agoGuys behinds PostgreSQL / CitusDB wrote awesome article on which methods of pagination work how well and where they fail. https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/ https://www.citusdata.com/blog/2016/03/30/five-ways-to-pagin... Highly recommended read.