6 ms·

it's kind of crazy that here in 2017 there are still discussions and debates on what exactly is the best way to paginate a large table in a database. I'm leani

it's kind of crazy that here in 2017 there are still discussions and debates on what exactly is the best way to paginate a large table in a database. I'm leaning with @jerf that the LIMIT/OFFSET approach can trap you on the 100M record scale, however in all honesty, most of us don't deal with databases at this size. As @alvil stated, his approach works fine for 1M records, which is way more then most personal and application databases will contain. Maybe the solution is for the database vendor to actually implement and document a definitive solution for this. Maybe re-engineering the LIMIT/OFFSET approach under the hood to take advantage of this new way. As for the article... I really have a personal problem when authors get lazy and take the easy way out by assuming everyone knows what they are talking about. Take this portion of the article ======================== Simplified algorithm: We get PAGE_SIZE number of records from the table. Starting offset value is 0. Use the max returned value for user_id in the batch as the offset for the next page. Get the next batch from the records which have user_id value higher than current offset. ======================== The author took the time to write an entire article debating and demonstrating their solution to the whole pagination problem and they couldn't take 5 minutes to show the code behind these steps in their solution? As I stated, it's just a personal thing. UPDATE: Down the internet rabbit hole I go. Here is a very nice article similarly demonstrating and debating the LIMIT/OFFSET approach in MSSQL between their OFFSET/FETCH and CTE. https://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch https://sqlperformance.com/2015/01/t-sql-queries/pagination-...

deleted 9y ago
[deleted]
privateprofile 9y ago
Exactly, the "correct solution" would be my first attempt as it uses the (clustered) index scan to count pages/records, it's likely to be the fastest option whereas the first method should not even be an option. This should be immediate for someone who understands how to develop in virtually any RDBMS. This is also, more broadly, something I've been thinking about recently. It feels (to me) like industry knowledge is getting lost somewhere, because you see people trying to reinvent the wheel in a lot of technical areas. One of them is data: there are proven approaches to most problems faced by the average organization, especially when it comes to designing and managing a data platform. Yet, you see people rolling their own approaches/designs/methodologies to basic things like ingestion, ETL and data modelling when the traditional approaches would suffice and would take half the time to implement and one third of the effort to maintain. It's great that people try to innovate, but what I see regularly is more like people trying to solve a solved problem without bothering to educate themselves on how it was solved so far.
tatersolid 9y ago
My day job at a smallish company has five different applications with tables more than 100M rows. This is not uncommon at all, especially for apps that need to retain audit history tables. Assuming you can’t toss history for business or compliance reasons, tables with 100M rows become commmon when an app has been in service for more than a few years. Even in SMBs.

deleted 9y ago

[deleted]

privateprofile 9y ago

Exactly, the "correct solution" would be my first attempt as it uses the (clustered) index scan to count pages/records, it's likely to be the fastest option whereas the first method should not even be an option. This should be immediate for someone who understands how to develop in virtually any RDBMS. This is also, more broadly, something I've been thinking about recently. It feels (to me) like industry knowledge is getting lost somewhere, because you see people trying to reinvent the wheel in a lot of technical areas. One of them is data: there are proven approaches to most problems faced by the average organization, especially when it comes to designing and managing a data platform. Yet, you see people rolling their own approaches/designs/methodologies to basic things like ingestion, ETL and data modelling when the traditional approaches would suffice and would take half the time to implement and one third of the effort to maintain. It's great that people try to innovate, but what I see regularly is more like people trying to solve a solved problem without bothering to educate themselves on how it was solved so far.

tatersolid 9y ago

My day job at a smallish company has five different applications with tables more than 100M rows. This is not uncommon at all, especially for apps that need to retain audit history tables. Assuming you can’t toss history for business or compliance reasons, tables with 100M rows become commmon when an app has been in service for more than a few years. Even in SMBs.