[PostgreSQL] 테이블의 행(Row) 개수와 테이블 크기(Size) 구하기

PostgreSQL에서 테이블의 행(Row) 개수와 테이블 크기(Size) 구해보자


환경

  • PostgreSQL


행(Row) 개수

  • 쿼리에서 COUNT(*)를 이용
select count(*) from {table_name};
  • 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)

  • pg_total_relation_size() 함수를 이용
select pg_size_pretty(pg_total_relation_size('{table_name}'));


하나의 쿼리로 행(Row) 개수와 크기(Size) 구하기

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;


참고자료