Posted 3 months ago. Visible to the public.

Postgresql: Paginate and count in one query using window functions

When paginating records, we usually need to know the number of total records in order to render pagination links. Popular pagination libraries like will_paginate Archive or Kaminari Archive do this for us by simply issuing an extra query, like this:

Copy
SELECT post.* FROM posts LIMIT 20 OFFSET 100; SELECT COUNT(*) FROM posts;

This is fine most of the time. But rarely, you might have very complicated WHERE conditions or a subquery that takes time to run. In these cases, doing two queries is slower than it needs to be.

Copy
SELECT posts.* FROM (/* some complicated_subquery */) posts LIMIT 20 OFFSET 100; SELECT COUNT(*) FROM (/* some complicated subquery */) posts;

Here the subquery had to run twice. (Although there is a chance PostgreSQL managed to optimize it for the COUNT query.)

Luckily, we can use PostgreSQL's window functions to speed things up. You can read more about window functions here Archive , but for our purposes, know that the following SQL does what we need:

Copy
SELECT posts.*, COUNT(*) OVER() AS full_count FROM (/* some complicated subquery */) posts LIMIT 20 OFFSET 100;

In Rails with will_paginate you might use it like this:

Copy
posts = scope_complicated_scope.select('posts.*, COUNT(*) OVER() AS full_count').paginate(page: page, total_entries: 0) if (first_post = posts.first) posts.total_entries = first_post.full_count end

There is one additional edge-case: When the OFFSET is so high that you get no results (i.e. a user managed to get to a pagination page that does not exist), the database will not return a record and so you will not get the count. To work around this, I use

Copy
scope = scope_complicated_scope.select('posts.*, COUNT(*) OVER() AS full_count') posts = scope.paginate(page: page, total_entries: 0) if (first_post = posts.first) posts.total_entries = first_post.full_count elsif page && page > 1 if (any_post = scope.limit(1).first) posts.total_entries = any_post.full_count end end

Your development team has a full backlog of feature requests, chores and refactoring coupled with deadlines? We are familiar with that. With our "DevOps as a Service" offering, we support developer teams with infrastructure and operations expertise.

Owner of this card:

Avatar
Tobias Kraze
Last edit:
3 months ago
by Tobias Kraze
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Tobias Kraze to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more