Offset Considered Harmful or: The Surprising Complexity of Pagination in SQL
For most applications, the job of a database system is to ingest all your data and give you access to the data you care about. Unfortunately, sometimes this is a large amount of data, and a typical application should only carefully take small sips from the fire hose of data. Generally, controlling the amount of output data is hard, but SQL offers the special limit syntax, which can guarantee that the result is bounded, and you never turn on the fire hose’s full blast. Unfortunately, the story does not end here, since there are many caveats.
For a limit, you need a sort order, and getting that to be consistent needs a bit of careful engineering. Additionally, while limit has wonderful use cases, its companion offset gives false hope that paging through results should be equally easy. However, using offset for pagination results in spurious duplicates and unexpectedly bad performance. In the following, we explore the details behind result pagination, and why you should replace offset with predicate-based pagination with a where condition.
In a typical application stack, the job of a database system is to store large amounts of data and provide access to relevant pieces of the data through queries. One catch is that even innocent and seemingly harmless database queries can return large results, and as a consequence, crash or degrade the whole system. Unfortunately, applications interfacing with the DB, thus, need to deal with large query results.
