How to Get Row Count For All Tables in PostgreSQL

Generally, we calculate row count for a single table with query like ‘select count(*) from table_name’ but sometimes you may need to get row counts of all tables in your database, just to get an indication of how big your tables are. In this article, we will learn how to get row count for all tables in PostgreSQL.

How to Get Row Count For All Tables in PostgreSQL

There are multiple ways to calculate row count for all tables in PostgreSQL, each with its pros and cons. We will look at 3 such methods one by one.

1. Using Count() function

Typically, we use the following query to get row count for each table.

select count(*) from table_name;

Now if you want to get row count of all tables in your database, you will need to run the above query for each table in your database. Here is a query for that purpose.

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,
       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 rows_n
FROM tbl
ORDER BY rows_n DESC;

We use a SELECT query in WITH statement to get a list of all table names in our public database schema. You can replace with your own database name. Then for each table_name and schema we calculate its row count. The above query will output table_schema name, table_name along with row count for each table.

This is fairly accurate depending on the current transactions going on in your database, but it will take time.

2. Using Statistics Collector

PostgreSQL Statistics collector tracks how many rows are live in any table, at any time. It stores all the information in pg_stat_user_tables table. It is not exactly accurate but gives a pretty good estimate, and is much faster than the above method. Here is a simple query for that purpose.

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

3. Using ANALYZE

ANALYZE command is regularly executed by PostgreSQL to update table statistics, including row counts. You can use this information to get row counts of all tables in your database.

SELECT 
  nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r' 
ORDER BY reltuples DESC;

In this article, we have learnt 3 ways to get row count of all tables in database in PostgreSQL.

How to Get Row Count For All Tables in PostgreSQL