Skip to content
Advertisement

Cursor based pagination for table without a unique and sequential id column?

I’m investigating using cursor-based pagination. I have the following (postgres) table:

and had begun to sketch out the pagination query (not tested):

I’ve been reading these posts:

https://slack.engineering/evolving-api-pagination-at-slack-1c1f644f8e12

https://engineering.mixmax.com/blog/api-paging-built-the-right-way/

https://coderwall.com/p/lkcaag/pagination-you-re-probably-doing-it-wrong

which both state that the cursor should be a unique, sequential column to paginate on.

However, my table does not have an auto-incrementing sequential primary key.

Instead it’s primary key is a composite key of these four fields, which together are unique, but not sequential:

Will I be able to implement a cursor-based pagination approach?

And also maybe allow users to specify a sort column, such as the sorting on the ‘value’ column?

Update: Based on @Lesiak’s comment, I believe the following queries may be close to what I want, keeping in mind that the tenantId, tagFieldName and tagId parts of the primary key will be known (the client provides this), but the key field of the primary key will not be known, and assuming the client has asked to sort on the value field.

Unrolling the row value expressions for the second (next page) query results in:

Which would translate to a JPA query something like (untested):

My questions/concerns regarding this are:

  • The complexity that will result when the user specifies multiple sort fields, with some maybe in asc/desc order etc?

  • The user will also be able to specify filters for this request, for example ‘label exists’, ‘label key contains’ , ‘label key starts with’ etc, and how this will again add to the complexity of constructing the criteria objects?

Advertisement

Answer

Yes, you can implement keyset pagination with a composite key. But JPA is not ideal tool for that – you’ll have to construct your predicates yourself.

Details:

Let’s modify an example from a framework that natively supports keyset pagination: https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/select-statement/seek-clause/

This is the kind of query you want to generate with your implementation. Notice it first orders by a user-selected field, and then by all parts of composite id.

The problem is that JPA does not support row value expression predicates (and only some databases support them). You have to unroll them yourself:

becomes:

As your PK has 4 fields, the resulting predicate will be quite long.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement