Because for each entered/deleted row you would have to update that counter, which would need to be done synchronously and would add overhead. In typical db usage you don't really need to access that number that often.

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.)

> why are relational databases not able to tell you how many rows are in a table without a full table scan

This is unrelated to database being relational or not. Any mutable database will have hard time with that challenge.

Can you remember how long you have lived in seconds? In milliseconds? Why are you not keeping track of such obviously useful information?

Of course, it is theoretically possible to create a database, that can count rows very quickly — under very specific conditions. Are week-old results acceptable? What about year-old results? A nanosecond-old results?

Locking is hard. Your computer has multiple CPUs, which constantly execute out-of-order instructions, — such as other transactions, mutating the same table. In order to count results of read operation those CPUs will have to take a stop (no matter how insignificant) and agree on linearity of events. Some of CPUs may have to perform pending work (such as sending recent transaction contents over PCIe bus) before they declare themselves ready to sync. In the worst case you may have to wait for some preempted threads to be brought back to life by OS scheduler. And you have to do that during every read — otherwise your results will automatically be outdated!

If you can accept outdated or outright invalid results (duplicates, remains of incomplete transactions), you can always use weaker DB transaction isolation level ("READ UNCOMMITTED" etc.) or simply cache results in Redis/Memcached. But for obvious reasons that isn't a default.