SQL Seek support for infinite scrolling
Joos
PostgreSQL (and to a limited extend other databases) support the SQL Seek method. This can be used for infinite scrolling or in a more complex implementation with next/previous pagination. But it's not possible to jump to a specific page.
The issue with current implementation using LIMIT and OFFSET is performance especially for higher page numbers. Each page takes a little bit longer to load. this isn't an issue for small databases or simple queries but for large ones or complex queries it is. See http://leopard.in.ua/2014/10/11/postgresql-paginattion/.
AFAIK the seek method is not available in querydsl.
Is there any way to manually implement this seek method using predicates/expressions? Or can I append literal SQL to a jpa query?
timowest
Joos
Hi Timo,
see also
http://use-the-index-luke.com/no-offset
The hall of fame of frameworks that do support keyset pagination is rather short:
Ruby order_query
blaze-persistence — a rich Criteria API for JPA providers (started in July 2014)
This is where I need your help. If you are maintaining a framework that is somehow involved with pagination, I ask you, I urge you, I beg you, to build in native support for keyset pagination too. If you have any questions about the details, I’m happy to help (forum, contact form, Twitter)!
I agree with him that this would be extremely helpful to have support for because after some investigation I do not see how I can build such a query manually with conditions:
Especially the partSELECT * FROM news WHERE category_id = 1234 AND (date, id) < (prev_date, prev_id) ORDER BY date DESC, id DESC LIMIT 10;
AND (date, id) < (prev_date, prev_id)Is IMHO impossible to build with querydsl or can you provide a hint on how to achieve this?
timowest
(date, id) < (prev_date, prev_id)
the same as
date < prev_date and id < prev_id
The idea is that offset is replaced with a condition that describes the last row of the previous page.
Br,TimoJoos
no, AFAIK there is actually a difference:
http://use-the-index-luke.com/sql/partial-results/fetch-next-page#sb-row-values
SQL Row Values
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: <row value constructor>]. 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.2Where 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.
Or in other words the order matters and must be the same as in ORDER BY clause.
date < prev_date and id < prev_id(date, id) < (prev_date, prev_id)
Joos
But thinking about this
date <= prev_date and id < prev_idshould do the trick. Using <= for any non-unique value and < for unique.