No commit activity in last 3 years
No release in over 3 years
Generic, flexible seek pagination implementation for Sequel and PostgreSQL
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
 Dependencies

Development

~> 1.6
>= 0
>= 0

Runtime

~> 5.0
 Project Readme

Sequel::SeekPagination

This gem provides support for seek pagination (aka keyset pagination) to Sequel and PostgreSQL. In seek pagination, you pass the pagination function the last data you saw, and it returns the next page in the set. Example:

# Activate the extension:
Sequel::Database.extension :seek_pagination
# or
DB.extension :seek_pagination
# or
ds = DB[:seek]
ds.extension(:seek_pagination)

# Get the first page of data:
DB[:seek].order(:id).limit(50) # SELECT * FROM "seek" ORDER BY "id" LIMIT 50

# Pass the last data to the you saw to get the second page.
# (suppose the id of the last row you got was 1456)
DB[:seek].order(:id).limit(50).seek(value: 1456) # SELECT * FROM "seek" WHERE ("id" > 1456) ORDER BY "id" LIMIT 50

# Also works when sorting by multiple columns.
DB[:seek].order(:col1, :col2).limit(50) # SELECT * FROM "seek" ORDER BY "col1", "col2" LIMIT 50
DB[:seek].order(:col1, :col2).limit(50).seek(value: [12, 56]) # SELECT * FROM "seek" WHERE (("col1", "col2") > (12, 56)) ORDER BY "col1", "col2" LIMIT 50

Why Seek Pagination?

Performance. The WHERE conditions generated above can use an index much more efficiently on deeper pages. For example, using traditional LIMIT/OFFSET pagination, retrieving the 9th page of 30 records requires that the database process at least 270 rows to get the ones you want. With seek pagination, getting the 100th page is just as efficient as getting the second.

Additionally, there's no slow count(*) of the entire table to get the number of pages that are available. This is especially valuable when you're querying on a complex join or the like.

Why Not Seek Pagination?

The total number of pages isn't available (unless you do a count(*) yourself), and you can't jump to a specific page in the table. This makes seek pagination ideal for infinitely scrolling pages.

Caveats

It's advisable for the column set you're sorting on to be unique, or else there's the risk that results will be duplicated if multiple rows have the same value across a page break. You may be able to get away with doing this on non-unique column sets if they have very high cardinality (for example, if one of the columns is a timestamp that will rarely be repeated). If you need to enforce a unique column set to get a stable sort, you can always add a unique column to the end of the ordering (the primary key, for example).

The gem is tested on PostgreSQL. It may or may not work for other databases.