High Performance PostgreSQL for Rails

Improved Performance with Keyset Pagination

Keyset pagination has a very technical name but is a relatively straightforward concept. With keyset pagination, an indexed column is used both for filtering with a WHERE clause and for sorting with an ORDER BY clause.

Let’s walk through retrieving a page of results. A query sets an order on the same column being filtered on and sets a max amount of records with a LIMIT clause. To fetch additional pages of results, new queries are sent that still filter and order on the same column but start from rows with a value that’s one greater than the previous result’s max value.

Because the column is indexed, both the filtering and ordering operations use an index scan, meaning their performance stays consistent ...