Unique sort order for postgres pagination

Question

When trying to implement pagination on the server side in postgres, I came across a point that, when using the restriction and offset keywords, should provide an ORDER BY clause in a unique column, possibly a primary key.

In my case, I use UUID generation for Pkeys, so I cannot rely on the sequential order of increasing keys. ORDER BY pkey DESC - may not cause new lines to appear on top. Therefore, I resorted to using the "Date Created" column - a timestamp column that should be unique.

But my question arises if the UI client wants to sort some other column? in case it may not always be a unique column, I turn to ORDER BY user_column, created_dt DESC to maintain predictable results for pagination on postgres pages.

Is this the right approach? I'm not sure if I will return correctly. please inform.

Answer 1

I talked about this exact issue on an old blog (in the context of using ORM):

The last note on using sorting and swap in a connection. A query that implements paging may have odd results if the ORDER BY clause does not include a field that is an empirical sequence in the data; the sort order is not guaranteed beyond what is explicitly stated in the ORDER BY clause in most (possibly all) database systems. For example: if you have 100 orders, all of them occurred on the same date, and you request the first page of this data, sorted by this date, then ask the second page of data to sort the same way, it is quite possible that you will get some data, duplicated in both pages. Therefore, depending on the query and the distribution of the data, this is “sortable”, it may be good practice to always include a unique field (such as a primary key) as the final field in the sort clause if you are paging.

http://psandler.wordpress.com/2009/11/20/dynamic-search-objects-part-5sorting/

Answer 2

A strategy for using a column that uniquely identifies a record as pkey or insertion_date may not be possible in some cases.

I have an application in which the user configures his own grid query, then he can just put any column from several tables, and maybe none of them is a unique identifier.

In a case that may be useful, you use rownum. You simply select rownum and use its sort in function. It will be something like:

select col1, col2, col3, row_number() over(order by col3) from tableX order by col3 

It is important that above (order by *) matches the order by *. This way, your paging will have consistent results every time.