I have seen a very large number of articles about pagination in SQL over the years, so I do hold it to a high standard. for that reason, I would have liked some discussion of other common approaches and why they’re less efficient.
I also feel like there’s an entire missing section of the article (or a missing second article) about the user experience tradeoffs and what state you put in the frontend to keep track of where the user is. storing a numerical offset of rows is fine and all, but what are the user’s expectations when the data changes between their clicks? what constraints are we accepting by using an offset like this, and are there ways to do better?
One extra trick can be used when NULL values are involved (as they throw a wrench in row-wise comparisons due to NULL != NULL) is to COALESCE them to boundary values to emulate NULLS LAST or NULLS FIRST.
For example, you’d use the expression (COALESCE(rating, 'inf'), id) to achieve indexed NULLS LAST strict total ordering on a nullable rating: float column. I suspect the same can be done for text column as well by coalescing to a value that sorts last in the column’s collation.
Lastly, Postgres will be able to use the b-tree index to perform a reverse index scan when swapping the order of the ordering and keyset offset (as long as all component of the index are reversed together). In the article’s example that would mean that
SELECT * FROM users
WHERE (name, id) < ('bob', 42)
ORDER BY name DESC, id DESC
LIMIT 10;
would also be able to use the users_name_id_idx index (Index Scan Backward) even if it’s defined in ascending order. This is useful property to implement previous and next buttons that are always relative to what is presented to the user while still allowing the order to be reversed.
it’s a nice fast explanation of a solid approach
I have seen a very large number of articles about pagination in SQL over the years, so I do hold it to a high standard. for that reason, I would have liked some discussion of other common approaches and why they’re less efficient.
I also feel like there’s an entire missing section of the article (or a missing second article) about the user experience tradeoffs and what state you put in the frontend to keep track of where the user is. storing a numerical offset of rows is fine and all, but what are the user’s expectations when the data changes between their clicks? what constraints are we accepting by using an offset like this, and are there ways to do better?
Great and concise explanation indeed!
One extra trick can be used when
NULLvalues are involved (as they throw a wrench in row-wise comparisons due toNULL != NULL) is toCOALESCEthem to boundary values to emulateNULLS LASTorNULLS FIRST.For example, you’d use the expression
(COALESCE(rating, 'inf'), id)to achieve indexedNULLS LASTstrict total ordering on a nullablerating: floatcolumn. I suspect the same can be done for text column as well by coalescing to a value that sorts last in the column’s collation.Lastly, Postgres will be able to use the b-tree index to perform a reverse index scan when swapping the order of the ordering and keyset offset (as long as all component of the index are reversed together). In the article’s example that would mean that
would also be able to use the
users_name_id_idxindex (Index Scan Backward) even if it’s defined in ascending order. This is useful property to implement previous and next buttons that are always relative to what is presented to the user while still allowing the order to be reversed.Funny, I just wrote a cursor-based paginator. In my case it makes sense because I’m iterating the entire table top-to-bottom.