Get total rowcount before paginating in JOOQ?

Question

I am interested to know if there is a more optimal way of getting the total rowcount from a JOOQ query before paginating with OFFSET and LIMIT

We tried using JOOQs synthetic SEEK clause however since our IDs are unordered UUIDs it does not work.

Our current implementation is to execute the query twice, the first time before we set an offset and limit to get the result/row count.

Then we get the results in the second query.

    SelectQuery<Record> select = context.selectQuery();
    select.addSelect(FOO_TABLE.fields());
    select.addFrom(FOO_TABLE);


    int totalElements = select.fetch().size();

    select.addOffset(20);
    select.addLimit(50));

    List<Foo> paginatedFoo = select.fetchInto(Foo.class);

Is this just something that has to be accepted with this implementation/design, or is there any JDBC driver magic that would eliminate the need for two queries to be executed on the DB?

Any input or suggestions welcomed!

Answer 1

We tried using JOOQs synthetic SEEK clause however since our IDs are unordered UUIDs it does not work.

When you paginate, you order by something meaningful to the user. So you will probably have some other column to order / seek first (e.g. some DATE column), and then use the UUID only to get unambiguous, stable results. I don't see why SEEK wouldn't work for you.

If using SEEK (keyset pagination) makes sense to your application, logically, it is much preferrable for performance reasons:

1. You don't have to count, because it doesn't matter
2. You don't have to use offset

Is this just something that has to be accepted with this implementation/design, or is there any JDBC driver magic that would eliminate the need for two queries to be executed on the DB?

It's a lot of extra work for a database. Especially if you do it like this:

// Don't do this!
int totalElements = select.fetch().size();

You're now transferring the entire data set! If you must count the number of rows in a separate query, at least run that query completely in the database:

// Do this instead (prior to adding the limit):
context.fetchCount(select);

But why not just use a window function? Add DSL.count().over() to your query to calculate the total row count that would be produced by your query if you weren't paginating, and you're all set.

The reason why you can use window functions for this is because they are calculated conveniently after all the other operations (WHERE, GROUP BY, HAVING, etc.), but before paginating (OFFSET, LIMIT). See this article about the logical order of operations.