Thanks for the reference.
I was asked the same question on reddit so i’ll post my answer here as well
Problems with paging arise in very large tables. Large tables (such as orders, transactions, log etc.) are usually being inserted into and updated very often. For tables that update very often maintaining a cached count is not an easy task. you will have to constantly invalidate and refresh the value. Due to high rate of records coming in you will rarely get to utilize the cached value.
Another issue that’s worth mentioning is that you need to maintain a cache per predicate — meaning, it’s not enough to maintain a cache of just the
select count(*), you would have to cache counts for different predicates.
Having said that, if you have a very large tables that is mostly static (or updated not very often such as DW fact tables or MV’s) you might be able to work up some solution using a cache.