Estimating Row Counts: Why COUNT(*) Isn't the Answer

In QoreDB's browser, every table in the sidebar shows an estimate of its row count. It's basic information, expected of any database client. It tells you whether you're opening a 200-row table or a 400-million-row one, which completely changes your querying strategy and what you can do with it from a dev machine.

The problem is that this number is expensive. The naive solution, a SELECT COUNT(*) FROM ma_table, works perfectly on a test table. It becomes a disaster the moment you run it on a table of tens of millions of rows in production, where it can take several seconds or even minutes. A database client that freezes its interface while counting the rows of every table at startup is simply unusable.

What Every Engine Already Knows

All relational databases maintain internal statistics used by their planner. These statistics almost always include an estimate of the row count per table, updated automatically by ANALYZE or its equivalent. These values are accessible in O(1), without scanning the table.

On PostgreSQL, the pg_class table exposes the reltuples column, a floating-point estimate maintained by autovacuum. The pg_stat_user_tables.n_live_tup view gives a fresher value, updated on every insertion or deletion. Both are readable instantly.

On MySQL and MariaDB, information_schema.TABLES.TABLE_ROWS returns an estimate from the storage engine. On InnoDB, this value can swing by 40 to 50 percent of error, which remains acceptable for an order of magnitude.

On SQL Server, the query SUM(p.rows) FROM sys.partitions filtered on heap and clustered indexes (index_id 0 and 1) gives a near-exact and instant count. It's the method Microsoft recommends to avoid a scan.

SQLite, by contrast, does not reliably expose this kind of per-table statistic. You have to count. This remains tolerable since SQLite files rarely exceed the size at which COUNT(*) becomes problematic.

The Hybrid Threshold on PostgreSQL

The approach chosen for PostgreSQL is the following: first read the estimate via pg_class and pg_stat_user_tables, then also read pg_total_relation_size to learn the physical size of the table.

Two thresholds then determine what is returned. If the estimate is below 100,000 rows, or if the total size stays under 64 MB, QoreDB triggers a real COUNT(*). On a table of that size, the operation stays fast (a few tens of milliseconds at most) and the user gets an exact value, useful for distinguishing a near-empty table from one that is starting to grow.

Beyond that, QoreDB stops at the estimate. The browser then displays that number as is. The decision is simple: on a 400-million-row table, exact precision brings no additional information to a developer. The difference between 400M and 401M doesn't change how they will approach the table.

This threshold is a deliberate compromise. It keeps things responsive on large tables while offering an exact number on small ones, where reltuples can be flat-out wrong (a table that has just been massively inserted into without a subsequent ANALYZE can display zero).

A Strategy per Driver

The DataEngine trait exposes a row_count_estimate: Option<u64> field in TableSchema. Each driver implements its own strategy behind this contract.

For CockroachDB, pg_stat_user_tables is not reliably populated. The driver settles for reltuples without the fallback on n_live_tup.

For MySQL, the value returned is directly TABLE_ROWS with no COUNT(*) threshold. This is a deliberate decision: on InnoDB, a COUNT(*) on a large table requires a full scan of the primary index, and the precision gained on small tables doesn't justify the extra logic.

For SQL Server, sys.partitions already gives a near-exact number, so there's no hybrid logic. For SQLite, a direct COUNT(*) is done, consistent with the local-file context. For MongoDB, the estimate uses estimatedDocumentCount or countDocuments depending on the presence of filters.

Since the field is Option<u64>, a driver is allowed to return None if it has no reliable source. The UI then simply shows nothing rather than a misleading number. This is the guiding thread of QoreDB's architecture: never invent data that the engine doesn't provide.

What This Looks Like in the Browser

In practice, opening a PostgreSQL database of 200 tables takes a fraction of a second. The statistics are read through a single query to pg_class per table, without ever launching a COUNT(*) on a large table in the background.

Dev or staging tables, often small, show their exact count. Production tables that exceed the threshold show the estimate. A user who wants the exact count can always run a SELECT COUNT(*) in the SQL editor. It's an explicit action, with a known cost, not something that runs silently when the browser loads.

Counting Just What's Needed

The story of the row count in QoreDB illustrates a simple rule: when the question has several good answers depending on the engine, the client must look for the best one without inventing one. pg_class.reltuples is not COUNT(*), but it's the best PostgreSQL can do in O(1). Using it means respecting the way the engine is designed.

The hybrid threshold covers the case where the estimate is too imprecise to be useful, without ever freezing the interface to display a decorative number. The result is a table browser that stays responsive on multi-terabyte databases and honest about the data it presents.