Getting Pagination Wrong

Lukas Eder discusses common pagination issues:

If your data source is a SQL database, you might have implemented pagination by using LIMIT .. OFFSET, or OFFSET .. FETCH or some ROWNUM / ROW_NUMBER() filtering (see the jOOQ manual for some syntax comparisons across RDBMS). OFFSET is the right tool to jump to page 317, but remember, no one really wants to jump to that page, and besides, OFFSET just skips a fixed number of rows. If there are new rows in the system between the time page number 316 is displayed to a user and when the user skips to page number 317, the rows will shift, because the offsets will shift. No one wants that either, when they click on “next”.

Instead, you should be using what we refer to as “keyset pagination” (as opposed to “offset pagination”).

He also has a good explanation of the seek method.

I will throw in one jab at Oracle (because hey, it’s been a while since I’ve lobbed a bomb at Oracle on this blog):  it’d really suck to have a system where I legally wasn’t allowed to distribute relevant performance comparison benchmarks.  Fortunately, I tend to work on better data stacks.

Related Posts

Basics Of Elasticsearch In .NET

Ivan Cesar gives us a brief tutorial of the Elasticsearch .NET API: To be able to search something, we must store some data into ES. The term used is “indexing.” The term “mapping” is used for mapping our data in the database to objects which will be serialized and stored in Elasticsearch. We will be […]

Read More

Working With CosmosDB

Derik Hammer has an introductory article showing how to work with CosmosDB to store and use document-style data: Querying Cosmos DB is more powerful and versatile. The CreateDocumentQuery method is used to create an IQueryable<T> object, a member of System.Linq, which can output the query results. The ToList() method will output a List<T> object from the System.Collections.Generic namespace. Derik also shows how to import […]

Read More


August 2016
« Jul Sep »