4 ms·

I really don't like this whole "OFFSET" kills performance thing that you see everywhere because it's only relevant for huge sets, think millions of rows. And t

by Eikon 5y ago

I really don't like this whole "OFFSET" kills performance thing that you see everywhere because it's only relevant for huge sets, think millions of rows. And then, there's a reason why people are using offsets, cursors are usually not a good alternative and the after_id pattern is not flexible and don't allow to go to a random page number... But still, some people are preaching for alternatives that are not actually solving the same problem as offsets.

simonw 5y ago
Millions of rows isn't a lot of data though - I've seen plenty of systems that implemented pagination with OFFSET and later regretted it.
- Eikon 5y ago
  By what measure "millions" is not big? We are talking about pagination there. And even then you just scale up your database server to have performant disks and implement caching. You'll be just fine going through even tens of millions of rows.
  - simonw 5y ago
    The problem is that when I implement pagination it's often because I want automated scripts to be able to export ALL of the data, which means they need to be able to paginate to the end of the results. Example from one of my projects: https://covid-19.datasettes.com/covid/ny_times_us_counties https://covid-19.datasettes.com/covid/ny_times_us_counties - it uses keyset (cursor) pagination so it's possible to efficiently export all 1,611,908 rows of data. "SELECT * from table limit 100 offset 10000" requires the database to skip 10000 rows in order to get to the rows it needs to start returning - and then on the next page it has to do it again, using offset 10100. If you're showing an interface to users you can implement an easy workaround: don't allow them to click past page 10, in which case OFFSET pagination only ever needs to loop through up to about 1,000 rows (depending on your page size).
  - nicwolff 5y ago
    Seconding simonw: If you implement a REST API with a "page" parameter that renders as OFFSET in its DB queries, I promise you someone will come along and page through your entire "not big" table. For reporting, or to update XML sitemaps, or something else vital to their business. Every 15 minutes. Now put that API behind a public-facing Web app handling, say, a thousand requests a second. Now deploy a migration that queues for an exclusive lock on a production table to add a column. Now write a "post-mortem" report explaining to your boss' boss' bosses why their revenue-generating sites were all down for 15 minutes.

by Eikon 5y ago

simonw 5y ago

Millions of rows isn't a lot of data though - I've seen plenty of systems that implemented pagination with OFFSET and later regretted it.

Eikon 5y ago
By what measure "millions" is not big? We are talking about pagination there. And even then you just scale up your database server to have performant disks and implement caching. You'll be just fine going through even tens of millions of rows.
- simonw 5y ago
  The problem is that when I implement pagination it's often because I want automated scripts to be able to export ALL of the data, which means they need to be able to paginate to the end of the results. Example from one of my projects: https://covid-19.datasettes.com/covid/ny_times_us_counties https://covid-19.datasettes.com/covid/ny_times_us_counties - it uses keyset (cursor) pagination so it's possible to efficiently export all 1,611,908 rows of data. "SELECT * from table limit 100 offset 10000" requires the database to skip 10000 rows in order to get to the rows it needs to start returning - and then on the next page it has to do it again, using offset 10100. If you're showing an interface to users you can implement an easy workaround: don't allow them to click past page 10, in which case OFFSET pagination only ever needs to loop through up to about 1,000 rows (depending on your page size).
- nicwolff 5y ago
  Seconding simonw: If you implement a REST API with a "page" parameter that renders as OFFSET in its DB queries, I promise you someone will come along and page through your entire "not big" table. For reporting, or to update XML sitemaps, or something else vital to their business. Every 15 minutes. Now put that API behind a public-facing Web app handling, say, a thousand requests a second. Now deploy a migration that queues for an exclusive lock on a production table to add a column. Now write a "post-mortem" report explaining to your boss' boss' bosses why their revenue-generating sites were all down for 15 minutes.