Paging in Postgres and Java

I am not a Postgres expert by any means. It has been a good 6 months of using what I think are the basics and so far one feature I found a breeze was server side paging.

It’s one of those problems we always need to solve when it comes to scaling and performance on larger datasets but never without a challenge.

For particular reasons that don’t need explaining here, I did not use an ORM in Java, so basic ResultSet was used to fetch and map the records out of the db.

So let’s say we have a users table. The SQL for paging would look something like this to get the first 10 results (LIMIT 10) and page 1 (OFFSET 0)

SELECT *, count(*) OVER() AS full_count FROM (
select * from users ...
where ... -- include any filtering conditions here
) as t
LIMIT 10
OFFSET 0

It’s really as simple as this! The results would return the user records along with the last column called full_count.

🙂

Now, reading that result set out in Java can be done pretty efficiently. Here’s a generic method I am using to read out the data:

      private ResultSetWithCount executeQuery(String query) {
        final ResultSetWithCount result = new ResultSetWithCount(mMapper);
        Connection connection = getConnection();
        try (PreparedStatement prepared = connection.prepareStatement(query)) {
            ...
            ResultSet rs = prepared.executeQuery();
            result.map(rs); // Map the paged results here...
        } catch (SQLException exception) {
            handleSqlException(exception);
        }
        return result;
    }

The method above is pretty simple. It accepts the raw SQL query (does not have to be prepared statement but that is a blog for another time), gets each record and lets the object ResultSetWithCount object do the mapping. The ResultSetWithCount class is the one with the trick to get the objects out as well as the total count column to be read only once.

This is what is looks like:

public class ResultSetWithCount {
    private long mTotalCount;
    private List mData;
    private final ResultSetMapper mMapper;

    public ResultSetWithCount(ResultSetMapper mapper) {
        mMapper = mapper;
        mData = new ArrayList();
    }

    public void map(ResultSet resultSet) throws SQLException {

        if (resultSet.next()) {
            mTotalCount = ResultSetHelper.tryReadLong(resultSet, JSONConstants.FULL_COUNT);
            mData.add(mMapper.mapFromResultSet(resultSet));
        }

        while (resultSet.next()) {
            mData.add(mMapper.mapFromResultSet(resultSet));
        }
    }

    public long getTotalCount() {
        return mTotalCount;
    }

    public List getData() {
        return mData;
    }
}

So this class takes in an interface of ResultSetMapper. You must implement this interface which will understand how to map your ResultSet to a User object! It should take a ResultSet (SQL row) and map each property to the user property.

The nice and efficient part of this class is that it will read the first Result Set and map it to the Total Count property only once. The rest of the records will map to your User object.

Also, it keeps the paging aspect out of your domain model. So your user class doesn’t need to have a “total count” property.

Once again, I’m sure there might be a nice and easier way to accomplish paging in Java and Postgres particularly using ORM tooling. Thought I’d share the approach we crafted ourselves.

Happy coding!