The number of rows in a table depends on whether rows have been inserted or deleted in another transaction. Conceptually it’s the same reason the database can’t

The number of rows in a table depends on whether rows have been inserted or deleted in another transaction. Conceptually it’s the same reason the database can’t tell you the sum of all the rows without a table scan. Even if it could do this, it wouldn’t help pagination except in the rare case you’re trying to display all the rows in the table. Normally you need to count the results of a query, not the whole table, so there’s no good reason to optimize that case. In an MVCC database, rows are present until the global transaction ID moves far enough to make them “old”, and the only way to count them for a given transaction is to look at them all and check their max transaction IDs. I guess you’d have to keep a set of row counts for each outstanding transaction ID to be able to instantly know the row count. (This is really the implementation-dependent version of the first paragraph above.)