DEV Community

Cover image for SQL Performance - pagination scalability
Rafal Hofman
Rafal Hofman

Posted on • Originally published at brightinventions.pl

1

SQL Performance - pagination scalability

Recently I have been reading SQL Performance Explained by Markus Winand and I wanted to share with you what I have learned regarding SQL pagination scalability.

Probably if you would be given the task to implement pagination, you would do it with LIMIT and OFFSET in a query. This would be completely fine, but it is good to know the limitations of it.

The more you browse back in history (increase LIMIT and OFFSET) the more response time increases. This is due to the fact that DB has to count all rows until it reaches the requested page.

An answer for that would be to include a WHERE statement in a query with FETCH FIRST X ROWS ONLY, which does not select previous results. Each "page" is limited with a different WHERE statement. It also has its own limitations (harder to implement pagination, harder to browser backward, fetch arbitrary pages) but at a cost of simplicity, you get a performance increase.

Markus Winand SQL Performance Explained pagination scalability

Which option we should choose? As always in computer science - it depends :)

Let me know how do you use pagination and if you ever had any performance issues related to that.

P.S Remember that pagination needs deterministic order and do include ORDER BY in your queries when needed ;)

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay