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 Show archive.org snapshot or Kaminari Show archive.org snapshot do this for us by simply issuing an extra query, like this:

SELECT post.* FROM posts LIMIT 20 OFFSET 100;

SELECT COUNT(*) FROM posts;   

Do you need DevOps-experts?

Your development team has a full backlog? No time for infrastructure architecture? Our DevOps team is ready to support you!

• We build reliable cloud solutions with Infrastructure as code
• We are experts in security, Linux and databases
• We support your dev team to perform

Read more Show archive.org snapshot

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.

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 Show archive.org snapshot , but for our purposes, know that the following SQL does what we need:

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:

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

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