How do you find the row count for all your tables in Postgres

I'm looking for a way to find the row count for all my tables in PostgreSQL. I know I can do this one table at a time with

SELECT COUNT(*) FROM table_name;

…but I'd like to see the row count for all the tables and then order by that to get an idea of how big all my tables are.

Solution

There's three ways to get this sort of count, each with their own tradeoffs.

If you want a true count, you have to execute the SELECT statement like the one you used against each table. This is because PostgreSQL keeps row visibility information in the row itself, not anywhere else, so any accurate count can only be relative to some transaction. You're getting a count of what that transaction sees at the point in time when it executes. You could automate this to run against every table in the database, but you probably don't need that level of accuracy or want to wait that long.

WITH    tbl AS (
 SELECT Table_Schema, Table_Name
 FROM   information_schema.Tables
 WHERE  Table_Name NOT LIKE 'pg_%'
        AND Table_Schema IN ('public')
)
SELECT  Table_Schema AS Schema_Name
,       Table_Name
,       (xpath('/row/c/text()', query_to_xml(format(
          'SELECT count(*) AS c FROM %I.%I', Table_Schema, Table_Name
        ), FALSE, TRUE, '')))[1]::text::int AS Records_Count
FROM    tbl
ORDER   BY Records_Count DESC;

The second approach notes that the statistics collector tracks roughly how many rows are "live" (not deleted or obsoleted by later updates) at any time. This value can be off by a bit under heavy activity, but is generally a good estimate:

SELECT schemaname,relname,n_live_tup 
  FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

That can also show you how many rows are dead, which is itself an interesting number to monitor.

The third way is to note that the system ANALYZE command, which is executed by the autovacuum process regularly as of PostgreSQL 8.3 to update table statistics, also computes a row estimate. You can grab that one like this:

SELECT  n.NspName AS Schema_Name, c.RelName AS Table_Name, c.RelTuples::int AS N
FROM    pg_class                AS c
        LEFT JOIN pg_namespace  AS n    ON n.oid = c.RelNameSpace
WHERE   NspName NOT IN ('pg_catalog', 'information_schema')
        AND RelKind='r'
ORDER   BY N DESC;

Which of these queries is better to use is hard to say. Normally I make that decision based on whether there's more useful information I also want to use inside of pg_class or inside of pg_stat_user_tables. For basic counting purposes just to see how big things are in general, either should be accurate enough.