The Hidden Cost of Pagination
As developers, we love pagination. It is a familiar UX pattern, it is simple to implement, and we have libraries that make it easy. Give users a few filters, show them how many pages of results there are, and you have an interface flexible enough for almost everyone.
So why am I talking about pagination in a post about performance?
Most implementations of pagination will issue an unbounded COUNT(*) query to determine how many pages of results to show, and as the table grows, that count query can utterly destroy your database performance.
Ironically, databases are not especially good at counting large result sets, and PostgreSQL is particularly bad at it. Finding the matching records is not the hard part. To produce an exact count, PostgreSQL must scan the matching rows or index entries and determine which versions are visible to the current query. That work consumes relatively high levels CPU similar to, but more expensive than, sequential scans.
And trust me™: you want your database focused on correctness and fast data retrieval, not calculating an exact count for every possible query.
So how do you keep paginated results without destroying the performance of your service?
Well, there are some technical solutions and some product solutions.
Counter Caches
A counter cache is just a running count kept somewhere else. As records are added or removed, you update that number and when needed, pass its current value to your pagination library.
There’s native support in Rails, or you can implement counters yourself, but I suggest leveraging counter_culture. It is very well thought out – supports conditional counters, dynamic counter columns, and counters through multiple levels of relationships.
Counter caches work best for a small(er) set predictable filters. They are less useful for arbitrary combinations of filters, since maintaining a separate exact counter for every possible query quickly becomes impractical.
Estimate the Count
If the complexity of counter_culture is putting you off, consider using FastCount.
FastCount uses PostgreSQL’s internal estimates instead of issuing a COUNT(*) query for an exact result. Since it uses estimates, it’s more useful for human-facing pages, where exactness isn’t required. It’s not recommended for APIs, where clients are likely to treat total_count as exact and build behaviour around it.
Remove the Count
Whenever possible, avoid showing the count entirely. Check your analytics: if users rarely navigate beyond the first few pages, an exact total may not be providing much value. In some interfaces, an approximate total, or simply whether another page exists, is almost as useful as the exact number.
Many everyday apps use pagination patterns that omit the total entirely. Ultimately, this is a product-level decision and not always available to developers.
Conclusion
Unbounded COUNT(*) queries will make your database sad, but pagination does not always require an exact count. Depending on the use case, you can cache the count, estimate it, or remove it entirely.
Further Thoughts
The goal is not to eliminate every COUNT(*) query. Focus on the common, expensive cases. Allow the occasional exact count against a large dataset. And as with most performance work, optimize where the cost is measurable.