Improved Pagination Support

Is there anything on the roadmap that involves improved support for pagination?

The SKIP/LIMIT approach is obviously not viable for large data sets (as it’s currently implemented) and there is no ability to use the “greater than the last record” approach on multiple fields (described as the “RID/LIMIT” approach, which also works with any single field). See topic 440.

Paging is such a common issue, and is frankly not something that any database really handles well, but I could envision a specialized aggregate index (maybe set through a meta parameter) that would handle this quite well. And having this in ODB would just add another “unique value proposition” to the product.

PS - This is generally referred to as “keyset pagination”, but to make it truly useful, it has to be able to support a “key” that is made up of multiple fields, without the restriction that all but the last field must be a fixed value for all records in the set.

PPS - In my research on this topic, I found an interesting reference to a feature of PostgreSQL, which is exactly what I’m proposing here. The following syntax can be used in a SQL query:
(VAL1, VAL2, VAL3, ...) >= (FIELD1, FIELD2, FIELD3, ...)

Apparently, this is called “Row Value Constructors” and is part of ANSI SQL (see below). Any plans or possibility of supporting this?

Besides regular scalar values, the SQL standard also defines the so-called row value constructors . They “Specify an ordered set of values to be constructed into a row or partial row” [SQL:92, §7.1: ]. Syntactically, row values are lists in brackets. This syntax is best known for its use in the insert statement.

Using row value constructors in the where clause is, however, less well-known but still perfectly valid. The SQL standard actually defines all comparison operators for row value constructors. The definition for the less than operations is, for example, as follows:

> "Rx < Ry" is true if and only if RXi = RYi for all i < n and RXn < RYn for some n.
>
> — SQL:92, §8.2.7.2

Where i and n reflect positional indexes in the lists. That means a row value RX is less than RY if any value RXn is smaller than the corresponding RYn and all preceding value pairs are equal ( RXi = RYi; for i<n ).

This definition makes the expression RX < RY synonymous to “RX sorts before RY” which is exactly the logic we need for the seek method.