Unique sort order for postgres pagination

Question

While trying to implement pagination from server side in postgres, i came across a point that while using limit and offset keywords you have to provide an ORDER BY clause on a unique column probably the primary key.

In my case i am using the UUID generation for Pkeys so I can't rely on a sequential order of increasing keys. ORDER BY pkey DESC - might not result in newer rows on top always. So i resorted to using Created Date column - timestamp column which should be unique.

But my question comes what if the UI client wants to sort by some other column? in the event that it might not always be a unique column i resort to ORDER BY user_column, created_dt DESC so as to maintain predictable results for postgres pagination.

is this the right approach? i am not sure if i am going the right way. please advise.

Answer 1

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

One last note about using sorting and paging in conjunction. A query that implements paging can have odd results if the ORDER BY clause does not include a field that represents an empirical sequence in the data; sort order is not guaranteed beyond what is explicitly specified in the ORDER BY clause in most (maybe all) database engines. An example: if you have 100 orders that all occurred on the exact same date, and you ask for the first page of this data sorted by this date, then ask for the second page of data sorted the same way, it is entirely possible that you will get some of the data duplicated across both pages. So depending on the query and the distribution of data that is “sortable,” it can be a good practice to always include a unique field (like a primary key) as the final field in a sort clause if you are implementing paging.

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

Answer 2

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

I have an application where the user sets up his own grid query then it can simply put any column from multiple tables and perhaps none is a unique identifier.

In a case that can be useful you use rownum. You simply select the rownum and use his sort in over function. It would be something like:

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

It's important that over(order by *) match with order by *. Thus your paging will have consistent results every time.