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 likeor do this for us by simply issuing an extra query, like this:
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.
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
Luckily, we can use PostgreSQL's window functions to speed things up. You can, 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
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.