> 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 no

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