Get table’s row count and size in PostgreSQL
Environment and Prerequisite
- PostgreSQL
Row Count
- Use COUNT(*) in query
select count(*) from {table_name};
- Get estimated value using pg_class
select relname, reltuples from pg_class where relname='{table_name}';
select n.nspname as table_schema, c.relname as table_name, c.reltuples as rows
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where c.relname='{table_name}' and c.relkind = 'r' and n.nspname not in ('information_schema','pg_catalog')
order by c.reltuples desc;
Size
- Use pg_total_relation_size() function
select pg_size_pretty(pg_total_relation_size('{table_name}'));
Get row count and size using one query
create or replace function count_rows_of_table(table_schema text, table_name text)
returns numeric
language plpgsql
as
$$
declare
count numeric;
begin
execute format('select count(*) from %s.%s', table_schema, table_name)
into count;
return count;
end;
$$;
select n.nspname as table_schema, c.relname as table_name, c.reltuples as estimated_row_count, count_rows_of_table(n.nspname, c.relname) as exact_row_count
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r' and n.nspname not in ('information_schema','pg_catalog')
order by c.reltuples desc;