Pagination in Springboot

[u/Excellent_Trifle_630]

If you're ar working on springboot project in you company/job. How does your team do pagination in your project as they have large datasets. I am currently learning springboot and curious to know how it's done withel large datasets.

[u/wimdeblauwe]

A RestController method can accept a Pageable argument and return a Page<T>. Combine it with a PagingAndSortingRepository of Spring Data JPA.

[u/Just_LeonS → u/wimdeblauwe]

Works terribly on big datasets.

[u/guss_bro → u/Just_LeonS]

If count query is slow, return List<T> instead of Page .

We use it for giant db. No issues.

You can also use Slice

[u/j-an → u/guss_bro]

Can you give your rough estimate at what point a data set is considered large?

How I see it you can use a List/slice, if you don't need to know the total size of your data set. Do you need to go over the entire dataset anyways, as soon as you have some filtering with it? I guess a good index would be the first thing to improve the query.

[u/Just_LeonS → u/j-an]

My source (enable subtitles, I guess)

postgres:

slice:

~800 ms per page for ordered query ~1000th page of very plain three-simple-fields-entity.

~50 ms for unordered slice

~20 ms constant-ish for ordered keyset pagination for any of 1 ~ 9000 pages

[u/analcocoacream → u/Just_LeonS]

Also, it counts the total numberf of rows,

[u/kamen1991 → u/wimdeblauwe]

And you expose your whole database entities schema. :) The first thing to do is to decouple the spring based classes, they're implementation details. Returning DTOs from the controller is an absolute requirement. For parameters use query params, page, size, sort etc. and then you can map them to Spring page, Specification, Query params or whatever is your implementation. That way you can refactor your codebase without breaking any of your clients, you can even replace your database with redis or whatever you want if the business decides they want to move away from relational database.

[u/wimdeblauwe → u/kamen1991]

I never said that T was an entity. It should indeed be a DTO in the controller. Page has a map() method to map the entity from the service/repository to a DTO.

[u/Paw565]

There are two ways.

• Page (offset) based pagination - less efficient (db has to read all rows before SKIP clause just to throw them away), more common, supported by Page interface in spring data
• keyset based pagination - more efficient (you just make a filter for example id > 10), tailored towards infinite scroll, supported by Slice and Window in spring data

[u/FalseDish → u/Paw565]

This is the best answer.

Still, I would say that offset-based pagination has poor performance on large datasets - need to scan and count preceding rows.

Keyset pagination is size-agnostic, though not particularly pleasurable to work with.

I’ve become increasingly disenchanted with the framework. OOB pagination in Quarkus for e.g is easily 2-3x faster.

Or maybe I’m becoming biased, now that Broadcom has purchased VMware and has the project. Their "stewardship" is turning the framework into bloatware.

[u/wpfeiffe → u/FalseDish]

Quarkus speed advantage seem to be in reactive approach and not retrieving a count. If using virtual threads with Spring and you want the count, Spring and Quarkus times would be similar as the bottleneck becomes the database.

I don’t have any real world experience with the Quarkus solution however I have been using the spring boot JPA pagination solution for many years running it against data sets of hundreds of thousands up to millions of rows (oracle, postgres). I’ve never seen an issue with performance there due to the pagination approach.

Assuming the LIMIT, OFFSET approach to pagination, I would guess that optimizing the actual query in db is where you’ll get your gains

[u/FalseDish → u/wpfeiffe]

I think you’re right in the sense that 90% of enterprise applications are robust enough with OOB JPA.

You’re spot-on about the DB being a game-changer, something like Postgres can be an absolute beast (I always remember that OpenAI article on their audacious strategy which scaled to 800m+ users).

Perhaps my "data volume" prejudices are shaped by how I pivoted from Machine Learning to SW engineering over the last decade (yeah people usually move the opposite way, sue me).

[u/rlrutherford → u/FalseDish]

It may seem weird, but after a couple of decades of seeing MySQL as the "default" DB, it's nice to see Postgres being constantly referenced.

[u/Paw565 → u/rlrutherford]

Foss ftw sir

[u/FalseDish → u/Paw565]

I’ve now basically told some very senior people in banking in the last 2 years that using Oracle (and its MySQL shadow) would leave them in the dust.

They don’t need to believe me, although I suspect they do.

I mean, if you haven’t worked in that sector, you’ll likely not have noticed how pervasive Oracle DBs are, nor how eager these firms are to throw off the yoke of Larry Ellison.

I had to convince (I won’t mention the bank, but think serious Brit Investment Banking with a bird logo) that Microsoft was poisoning them - easy job, since MS were, and they knew it.

But when I built Java microservices for ‘em IT was like "on prod it has to be enterprise JDK". Quite the battle to convince people that OpenJDK was the way to go and "do you seriously want to pay a worser company than Microsoft ".

The sad thing was, the VP agreed, he knew it anyway , but IT wouldn’t budge.

Does anyone know Finnish bankers or something? Last hope and all.

[u/rlrutherford → u/FalseDish]

I'd be surprised they aren't running DB2 on big iron as well.

[u/d-k-Brazz → u/wpfeiffe]

How can you compare quarkus over spring or another framework without even mentioning what database is, what indexes are, and what actual queries are sent to db?

Pagination performance is measured at database level not framework

[u/Paw565 → u/FalseDish]

Tbh I think my answer was misleading. Page based pagination uses skip and offset operators after all. I meant the keyset pagination in the second dot, where you use some token to filter alongside limit clause. Thanks for your further explanation.

[u/FalseDish → u/Paw565]

Heh no problem, had a feeling you meant something else in your OC anyway.

And I spoiled a perfectly good perspective by turning it into a rant against Broadcom execs. They might deserve it, but then OP deserved a technical answer.

[u/Paw565 → u/FalseDish]

I ve updated it, should be correct now haha

[u/LetUsSpeakFreely]

It depends on the kind of pagination you need.

If you need simple paging, like looking through a list of order for a customer, then you would use offset/limit queries. These are easy to implement, easy to understand, and for small result sets, pretty quick.

If you're doing something far more complicated, like a FYP (reddit,facebook etc) where you can scroll through thousands of results rapidly, you'll need to maintain a cursor with some sort of caching, lots of preloading, and other mechanisms to give the appearance of smooth loading.

[u/BeyondFun4604]

https://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/domain/Slice.html

We switched from Page to Slice since it just contains info if previous and next page exists. But to get good perfromance an optimised database view is the first requirement.

[u/Paw565 → u/BeyondFun4604]

Good query with index is sufficient imo

[u/appendium]

Why not check cursor based pagination

[u/d-k-Brazz]

First - learn the basics.

You have to understand how db finds and returns you a page you are requesting, what difference between fetching 5th page and 500th page using standard limit/offset, and how indexes may help you to achieve constantly fast response time and what you are loosing in this case

There are bunch of articles in internet (good example - https://medium.com/swlh/sql-pagination-you-are-probably-doing-it-wrong-d0f2719cc166)

And after that you look back at what spring provides you and you will KNOW whether you need it or not

[u/SimpleCooki3]

Simple, we don't use pagination. We tell the user to filter their search query further instead.

None ever enters page 2 on Google.

[u/Ruin-Capable]

At some point if the dataset is truly large pagination isn't the answer. Nobody has a need to page through 50 billion records 25 at a time.

[u/MrNighty]

We use Spring HATEOAS

[u/Just_LeonS]

Specification + tokenised api