[PostgreSQL] Table Size 확인

PostgreSQL에서 Table Size 확인 시 아래 쿼리를 통해 조회가 가능합니다.

 

1) 특정 Table Size 확인 (index 포함)

  # select pg_size_pretty(pg_total_relation_size('Table명'));

postgres=# select pg_size_pretty(pg_total_relation_size('pg_class'));

 pg_size_pretty
----------------
 224 kB
(1개 행)

 

2) 특정 Table Size 확인 (index 미포함)

  # select pg_size_pretty(pg_relation_size('Table명'));

postgres=# select pg_size_pretty(pg_relation_size('pg_class'));
 
 pg_size_pretty
----------------
 88 kB
(1개 행)

 

3) Size 큰 Table 조회

  # select A.relname as Tablename,
    A.oid as OID,
    A.relfilenode as Filename,
    B.tablespace as Tablespace,
    pg_relation_size(A.oid)/1024 as "FILE_SIZE(KB)"
from pg_class A, pg_tables B
   where A.relname = B.tablename
    order by 5 desc

postgres=# select A.relname as Tablename,
postgres-#     A.oid as OID,
postgres-#     A.relfilenode as Filename,
postgres-#     B.tablespace as Tablespace,
postgres-#     pg_relation_size(A.oid)/1024 as "FILE_SIZE(KB)"
postgres-# from pg_class A, pg_tables B
postgres-#    where A.relname = B.tablename
postgres-#     order by 5 desc
postgres-#     limit 10;

   tablename    | oid  | filename | tablespace | FILE_SIZE(KB)
----------------+------+----------+------------+---------------
 pg_shdepend    | 1214 |        0 | pg_global  |          1080
 pg_proc        | 1255 |        0 |            |           576
 pg_depend      | 2608 |     2608 |            |           432
 pg_attribute   | 1249 |        0 |            |           360
 pg_description | 2609 |     2609 |            |           272
 pg_statistic   | 2619 |     2619 |            |           184
 pg_operator    | 2617 |     2617 |            |           120
 pg_rewrite     | 2618 |     2618 |            |            96
 pg_class       | 1259 |        0 |            |            88
 pg_type        | 1247 |        0 |            |            72
(10개 행)